Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nathans
Aged Yak Warrior
938 Posts |
 Posted - 2005-02-09 : 19:49:09
|
| Hey guys,Is there a difference in these two queries? I came across some legacy code that uses syntax from the first query. It looks like the execution plans are the same?SELECT a.nameFROM table_a a INNER JOIN table_b b ON a.id = b.id AND b.id = 3-- vs.SELECT a.nameFROM table_a a INNER JOIN table_b b ON a.id = b.id WHERE b.id = 3Thanks! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-09 : 22:44:39
|
| Sql Server does a lot to try and optimize developers statements so its not surprising that the execution plan for your 2 statements are the same. Sql will come up with a bunch of plans that will statisfy your request, compare the costs, and select the least expensive plan. But you should try and make things as easy as possible for the optimizer to do its thing. There's no rule that says you can't put "where" criteria in with the join criteria. Sql developers should be aware of how a statement is processed by Sql Server and work toward limiting the result set as early in the process as possible. Here is a brief description of the order that Sql Server processes a statement:a) A base set of records is decided on by the From Clause (List Inner joins before other join types)b) The Where Clause then filters out rows derived from the From Clause.c) Group by clause then groups the records accordingly (aggregations in Select list are performed now)d) Having clause filters the groups from the group by clausee) The column list is then pulled from the Select Clause (grouping columns if Group By was used)f) Select Distinct will then remove dupes.g) finaly the Order by is applied.Be One with the OptimizerTG |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-09 : 23:14:45
|
| Not quite but it's not a bad rule of thumb.The select list (and group by / order by clauses) will also be considered in deciding the query plan for the where clause.And a) and b) are not separated in the query plan (as shown by the question).For the types of queries you have given it shouldn't make any difference. It can make a diference if you use outer joins though.For inner joins only go for readability. In that situation I would probably go for the second but swap round the tables in the join - but that depends on the nature of the data - the first might be more logical.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-02-10 : 12:30:51
|
| Thanks guys. That helps. |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-10 : 12:33:24
|
quote:
Originally posted by TG
Sql Server does a lot to try and optimize developers statements so its not surprising that the execution plan for your 2 statements are the same. Sql will come up with a bunch of plans that will statisfy your request, compare the costs, and select the least expensive plan. But you should try and make things as easy as possible for the optimizer to do its thing. There's no rule that says you can't put "where" criteria in with the join criteria. Sql developers should be aware of how a statement is processed by Sql Server and work toward limiting the result set as early in the process as possible. Here is a brief description of the order that Sql Server processes a statement:a) A base set of records is decided on by the From Clause (List Inner joins before other join types)b) The Where Clause then filters out rows derived from the From Clause.c) Group by clause then groups the records accordingly (aggregations in Select list are performed now)d) Having clause filters the groups from the group by clausee) The column list is then pulled from the Select Clause (grouping columns if Group By was used)f) Select Distinct will then remove dupes.g) finaly the Order by is applied.Be One with the OptimizerTG
TG....where'd you get that listing for the order of processing on the SQL Server? And is this for 2005 or 2000?Semper fi, Xerxes, USMC(Ret.)----------------------------------------------Acclaimed Cursoring Yak Master"If cursors were nickels, I could park in town--all year!" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-10 : 12:49:34
|
he gets it from reading the execution plans.Go with the flow & have fun! Else fight the flow  |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-10 : 14:32:59
|
| And from years of trial and error (mostly eror) of doing query optimizations. But as Nigel also pointed out, its just a rule of thumb. There are exceptions, and according to Nigel inaccuacies, with that list. He's right that the optimizer will look at the select list to help devise a plan. Here's an example: If your select list columns are all contained in a "covered" index, the plan will (or should) use that index for the actual data rather than pulling it from the table's data pages.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Filter in JOIN vs. Where ?? Any difference?