| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2007-05-10 : 10:12:05
|
| What topics/books/tutorials should I look up to refresh myself with the foundations of sql.Should I just lookup set theory?e.g. to understand the differnce between these two queires:#1select *from table1 t1 inner join table2 t2 on (t1.id = t2.id AND td.id > 100)versus#1select *from table1 t1 inner join table2 t2 on (t1.id = t2.id)where t2.id > 100Personally I can't picture in my mind what the difference is!! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-10 : 10:22:14
|
Besides the typo?  I'm no set theory expert, but I don't see the difference either.Mark |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-10 : 10:31:57
|
quote:
Originally posted by mwjdavidson
Besides the typo? I'm no set theory expert, but I don't see the difference either.Mark
Read the link I postedMadhivananFailing to plan is Planning to fail |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-10 : 10:45:58
|
| Yeah, thanks madhivanan - I was just being flippant!I also recommend "Inside Microsoft SQL Server 2005: T-SQL Querying" by Ben-Can, Collar, and Sarka [url]http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_2/026-0298766-8405211?ie=UTF8&s=books&qid=1178808205&sr=8-2[/url] for some good coverage of logical and physical query processing in SS 2005. This will help you understand why it is different for an outer join and also why the two queries presented are logically different, but would be physically processed in the same way.Mark |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-10 : 10:51:54
|
| No difference in output, but I think, first version will be slightly faster and results in less-costly execution plan than second one.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-10 : 12:14:08
|
quote:
Originally posted by harsh_athalye
No difference in output, but I think, first version will be slightly faster and results in less-costly execution plan than second one.
I'd put money on the plans being identical. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-10 : 12:31:27
|
quote:
Originally posted by pootle_flump
quote:
Originally posted by harsh_athalye
No difference in output, but I think, first version will be slightly faster and results in less-costly execution plan than second one.
I'd put money on the plans being identical.
Depending on the data I'd take that bet. :)In my experience putting additionaly restrictions on the join can gain you some performance. But, usually only on larger data sets. By larger I mean mutiple joins (5+ tables) all containing 100+ million row. Also, having the right indexs and restricting data that has a high selectivity will also make a big difference.Cheers,-Ryan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 13:25:14
|
"In my experience putting additionaly restrictions on the join can gain you some performance"Well, I tend to code like that anyway, so I'll pat myself on my back!, but I always assumed that the optimiser was smart enough to "move" such conditions around. I mean ... it moves stuff from HAVING to WHERE so from WHERE to JOIN can't be hard can it?  Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-11 : 06:25:33
|
quote:
Originally posted by Lamprey
quote:
Originally posted by pootle_flump
quote:
Originally posted by harsh_athalye
No difference in output, but I think, first version will be slightly faster and results in less-costly execution plan than second one.
I'd put money on the plans being identical.
Depending on the data I'd take that bet. :)In my experience putting additionaly restrictions on the join can gain you some performance. But, usually only on larger data sets. By larger I mean mutiple joins (5+ tables) all containing 100+ million row. Also, having the right indexs and restricting data that has a high selectivity will also make a big difference.Cheers,-Ryan
Irrespective of the data volume & distribution you are on  . Start adding tables though and I'm out. The optimiser becomes less predictable the more tables there are invlolved. I would still expect the plans to be the same, just not prepared to put my money where my mouth is. If anyone can produce two different plans for the OPs queries though I will contribute to a charity of their choice.Damn - why did I start mentioning money.... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-11 : 07:43:05
|
| Is there a MillionPoundMinimumDonation charity? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-05-11 : 08:09:07
|
quote:
Originally posted by Kristen
Is there a MillionPoundMinimumDonation charity?
Eeek - I hope not  |
|
|
|
|
back to the fundamentals