Effect of Joins on the Speed

Hi..

I want to know that when I do something like..

Select Query

Left Join

Select Query 2

Left Join

Select Query 3

How does it work actually?

As in, whether Query 2 & Query 3 will work only on the records retrieved by Query 1 only.

Or, all the select statements retrieves all the records and then the condition is applied to filter out the results.

Also, does the order of the Select statements make any difference on the speed?

Thanks

Sql server has a component called the query optimizer. It will read the query and then devise what it thinks is the best plan to come up with the results. You can see this plan when you type a query in Management Studio (or Query Analyzer in 2000) and then press CTRL-L.

The order of the joins does not make a difference, unless you tell the optimizer to join the tables in a specific order by using the 'force order' query hint. I have had complex queries where using a hint reduced execution time dramatically, but usually you're better of letting SQL sort it out. What definitely will improve performance is coming up with the right indexing strategy.

The best book i know on this subject is 'Inside MS sql server' that i read for 2000. I'm not sure about the 2005 version, i haven't read it.

Regards,

Gert-Jan

|||

Thanks..

That helps a lot!!

|||

This is the book you want for 2005:

http://www.microsoft.com/MSPress/books/9615.aspx

It breaks down the querying process both physically and logically, and really looks at how to tune indexes and querys at a level I didn't previously realize was possible. Good read too (if you're into that sort of thing...)