Thread: SQL Technique Question
i frequently join certain tables together in various tables. view the following link for an example: http://www.rafb.net/paste/results/mBvzn950.html is it a good practice to leave this included in the queries, as is, or should i factor it out somehow? if i should factor it, how do i do so? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, Jun 15, 2006 at 01:59:22PM -0700, operationsengineer1@yahoo.com wrote: > > is it a good practice to leave this included in the > queries, as is, or should i factor it out somehow? if > i should factor it, how do i do so? If what you're saying is that these additional criteria are redundant, then it's up to you: what do you want to optimise for? If you're protecting against future errors, then the additional criteria might help. If you're protecting against having to write your code to produce a more efficient query, you should weigh the cost and benefit (which benefit includes "easier to debug queries"). There is a probably non-zero cost to the extra joins. A -- Andrew Sullivan | ajs@crankycanuck.ca Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris
On Thu, 2006-06-15 at 13:59 -0700, operationsengineer1@yahoo.com wrote:
> i frequently join certain tables together in various
> tables.
> is it a good practice to leave this included in the
> queries, as is, or should i factor it out somehow? if
> i should factor it, how do i do so?
Future proofing selects queries is difficult because they never throw
errors. They just give different results. What you really want is an
ASSERTION to disallow bad entries from being created in the first place
but PostgreSQL doesn't do those yet. Yes, you can do it with triggers
but those are annoying to create by the hundreds for development only
purposes.
I would tend to add all of the columns to select be selected out and
have assertions in the code. This way you can detect incorrect values in
development and disable those safety's for production.
select t_inspect_result.inspect_result_pass , t_inspect.serial_number_id ,
t_serial_number.serial_number_id... assert(t_inspect.serial_number_id =
t_serial_number.serial_number_id);
--
On Jun 16, 2006, at 5:59 , <operationsengineer1@yahoo.com> wrote: > i frequently join certain tables together in various > tables. > > view the following link for an example: > > http://www.rafb.net/paste/results/mBvzn950.html > > is it a good practice to leave this included in the > queries, as is, or should i factor it out somehow? if > i should factor it, how do i do so? I'm not quite sure what you're getting at in your message (in particular, what is the "this" in "leave this included in the queries"?), and it looks like I have a completely different interpretation of what you're asking, looking at the responses you've already received from Andrew and Rod. I think you're asking about encapsulation and how to efficiently use code, rather than copying and pasting the basic query and then modifying a small portion of it. Based on that interpretation, here's what I'd do. (If I'm wrong, well, then, oh well.) I'd create a view that contains the common code. CREATE VIEW t_inspect_join_view AS SELECT t_inspect_result.inspect_result_pass , t_inspect_result.inspect_result_timestamp , t_product.product_number , t_inspect_result.inspect_result_id FROM t_inspect_result, t_inspect, t_inspect_area, t_serial_number, t_link_contract_number_job_number, t_job_number, t_product WHERE t_inspect_result.inspect_id = t_inspect.inspect_id AND t_inspect.serial_number_id = t_serial_number.serial_number_id AND t_serial_number.link_contract_number_job_number_id = t_link_contract_number_job_number.link_contract_number_job_number_id AND t_link_contract_number_job_number.job_number_id= t_job_number.job_number_id AND t_inspect.inspect_area_id = t_inspect_area.inspect_area_id AND t_product.product_id = t_job_number.product_id; or in a style I find a bit clearer: CREATE VIEW t_inspect_join_view AS SELECT t_inspect_result.inspect_result_pass , t_inspect_result.inspect_result_timestamp , t_product.product_number , t_inspect_result.inspect_result_id FROM t_inspect_result JOIN t_inspect USING (inspect_id) JOIN t_serial_number USING (serial_number_id) JOIN t_link_contract_number_job_number USING (link_contract_number_job_number_id) JOIN t_inspect_area USING (inspect_area_id) JOIN t_job_number USING (job_number_id) JOIN t_product USING (product_id); One of the reasons I like this style is that it makes it easy to see that all of the tables in the FROM clause have join conditions (which is usually what you want). For example, it looks like you probably want the AND t_inspect.inspect_area_id = t_inspect_area.inspect_area_id part of the WHERE clause in your "repeating code" section, so I've added it to the view. With the JOIN conditions (how tables are joined together) now part of the FROM clause, the WHERE clause can be used to list just restrictions restrictions (limiting what rows are returned). I find this much clearer to write and read, as I've got clear separation between these two things. While underneath it all the server might consider everything part of the WHERE clause, sytactically I find this style helpful. I've also added some more columns to the SELECT target list, as you'll want to have them exposed for the extra WHERE clause restrictions. Once part of the view, only columns listed in the SELECT target list will be accessible outside of the view. You may have other restrictions that you want to apply in different cases, so you may want to add more columns to the target list. So your original query, using this view, would look like: SELECT inspect_result_pass FROM t_inspect_join_view WHERE product_number = '7214118000' AND inspect_result_timestamp > '2006-01-01' AND inspect_result_timestamp < '2006-06-13' AND inspect_result_id IN ... Hope this helps. Michael Glaesemann grzm seespotcode net
> > On Jun 16, 2006, at 5:59 , > <operationsengineer1@yahoo.com> wrote: > > > i frequently join certain tables together in > various > > tables. > > > > view the following link for an example: > > > > http://www.rafb.net/paste/results/mBvzn950.html > > > > is it a good practice to leave this included in > the > > queries, as is, or should i factor it out somehow? > if > > i should factor it, how do i do so? > > I'm not quite sure what you're getting at in your > message (in > particular, what is the "this" in "leave this > included in the > queries"?), and it looks like I have a completely > different > interpretation of what you're asking, looking at the > responses you've > already received from Andrew and Rod. I think you're > asking about > encapsulation and how to efficiently use code, > rather than copying > and pasting the basic query and then modifying a > small portion of it. > Based on that interpretation, here's what I'd do. > (If I'm wrong, > well, then, oh well.) > > I'd create a view that contains the common code. > > CREATE VIEW t_inspect_join_view AS > > SELECT t_inspect_result.inspect_result_pass > , t_inspect_result.inspect_result_timestamp > , t_product.product_number > , t_inspect_result.inspect_result_id > FROM t_inspect_result, t_inspect, t_inspect_area, > t_serial_number, > t_link_contract_number_job_number, > t_job_number, t_product > WHERE t_inspect_result.inspect_id = > t_inspect.inspect_id > AND t_inspect.serial_number_id = > t_serial_number.serial_number_id > AND > t_serial_number.link_contract_number_job_number_id = > > t_link_contract_number_job_number.link_contract_number_job_number_id > AND > t_link_contract_number_job_number.job_number_id = > t_job_number.job_number_id > AND t_inspect.inspect_area_id = > t_inspect_area.inspect_area_id > AND t_product.product_id = > t_job_number.product_id; > > or in a style I find a bit clearer: > > CREATE VIEW t_inspect_join_view AS > > SELECT t_inspect_result.inspect_result_pass > , t_inspect_result.inspect_result_timestamp > , t_product.product_number > , t_inspect_result.inspect_result_id > FROM t_inspect_result > JOIN t_inspect USING (inspect_id) > JOIN t_serial_number USING (serial_number_id) > JOIN t_link_contract_number_job_number > USING (link_contract_number_job_number_id) > JOIN t_inspect_area USING (inspect_area_id) > JOIN t_job_number USING (job_number_id) > JOIN t_product USING (product_id); > > One of the reasons I like this style is that it > makes it easy to see > that all of the tables in the FROM clause have join > conditions (which > is usually what you want). For example, it looks > like you probably > want the > > AND t_inspect.inspect_area_id = > t_inspect_area.inspect_area_id > > part of the WHERE clause in your "repeating code" > section, so I've > added it to the view. With the JOIN conditions (how > tables are joined > together) now part of the FROM clause, the WHERE > clause can be used > to list just restrictions restrictions (limiting > what rows are > returned). I find this much clearer to write and > read, as I've got > clear separation between these two things. While > underneath it all > the server might consider everything part of the > WHERE clause, > sytactically I find this style helpful. > > I've also added some more columns to the SELECT > target list, as > you'll want to have them exposed for the extra WHERE > clause > restrictions. Once part of the view, only columns > listed in the > SELECT target list will be accessible outside of the > view. You may > have other restrictions that you want to apply in > different cases, so > you may want to add more columns to the target list. > > So your original query, using this view, would look > like: > > SELECT inspect_result_pass > FROM t_inspect_join_view > WHERE product_number = '7214118000' > AND inspect_result_timestamp > '2006-01-01' > AND inspect_result_timestamp < '2006-06-13' > AND inspect_result_id IN ... > > Hope this helps. > > Michael Glaesemann > grzm seespotcode net Michael, this is the answer to my question. i have to read up on the other answers to see if i can learn something to incorporate into my programming. i've just had a bear of a time trying to keep everything straight... okay, i want to know what inspects are associated with p/n 123, s/n 1... or what is the p/n associated with p/n 456? i had to troll through all my relations to get at results. i need to become mor familiar with views. if i use views, will i substantially impair performance? thanks for the answer... the end result sure sure looks clean. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> On Thu, Jun 15, 2006 at 01:59:22PM -0700, > operationsengineer1@yahoo.com wrote: > > > > is it a good practice to leave this included in > the > > queries, as is, or should i factor it out somehow? > if > > i should factor it, how do i do so? > > If what you're saying is that these additional > criteria are > redundant, then it's up to you: what do you want to > optimise for? If > you're protecting against future errors, then the > additional > criteria might help. If you're protecting against > having to write > your code to produce a more efficient query, you > should weigh the > cost and benefit (which benefit includes "easier to > debug queries"). > There is a probably non-zero cost to the extra > joins. Andrew and Rod, my apologies for not being more clear in my question. all the code is required to get from t_inspect_result data back to t_product information. however, many of the joins are used over and over and over - making for a complex query to view and try and to debug - not to mention forcing a long trail of chasing linked data to get from t_inspect_result_id back to the linked t_product data. Thanks for taking the time to address the question - and i will try and be more clear going forward. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[Please quote responsibly. There was no need to quote my entire message back to the list.] On Jun 16, 2006, at 7:15 , <operationsengineer1@yahoo.com> wrote: > i need to become mor familiar with views. if i use > views, will i substantially impair performance? Short answer: maybe, maybe not Long answer: benchmark and compare. EXPLAIN ANALYZE is your friend. For example, compare the EXPLAIN ANALYZE output using the view and using the whole, explicit query. You'll learn a lot that will only help you write better queries. Michael Glaesemann grzm seespotcode net