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 |
|
CactusJuice
Starting Member
46 Posts |
 Posted - 2002-12-22 : 14:58:16
|
| SELECT * FROM tblClients c INNER JOIN tblHouses h ON c.ClientID = h.ClientID AND (h.ForSale=1)WHERE c.CreatedYear > 2000------- OR ---------SELECT * FROM tblClients c INNER JOIN tblHouses h ON c.ClientID = h.ClientIDWHERE c.CreatedYear > 2000 AND (h.ForSale=1)THe above queries are oversimplified examples. I am wondering which method is faster. Is it better to specify the (h.ForSale=1) condition in the JOIN or down below in the WHERE...does it matter? Can anyone tell me how to test this on my own.Using MS SQL 2000thanks. |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-22 : 17:15:01
|
| The difference is the join is performed before the filter.As far as performance sql server will optimize both statements and you will get the same performance.You can use query analyzer and take a look at the estimated execution plan for both queries. You can also insert say 500,000 records in some tables thenrun both queries and then compare execution times. Make sure you run it more than once since sql server caches the execution plans and it might give you different performance on different runs.Checkouthttp://www.sqlservercentral.com/columnists/bkelley/qa2k_1.aspThere is also part II and IIIEdited by - ValterBorges on 12/22/2002 17:18:45 |
 |
|
|
|
|
|
Best place for a condition/operater