SQL Server Performance Forum – Threads Archive
A question about joins.
I have a best practices question regarding table joins. I am looking at a query where the tables are joined similar to the example below: SELECTA.ColumnA, B.ColumnB FROM Customers C JOINAddresses A ON A. (C.CustomerID = A.CustomerID) AND (A.DefaultAddress = 1) WHERE(A.EffectiveDate < GETDATE())
Seems the default address flag should go in the where clause. Does it really matter? Are there performance advantages one way or another? Thanks, Mike The revolution is just a t-shirt away.
That depends whether you can agree the performance of query, have you checked the estimated execution plan in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Also, are you sure there is only one row in Addresses, for each CustomerId, where DefaultAddress=1?
I have not looked closely at the estimated execution plan, but the actual query is pretty slow. I dropped the elements into the where clause and that at first appeared to speed up the query. The revolution is just a t-shirt away.
How about indexes for the involved tables? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
–Also, are you sure there is only one row in Addresses, for each CustomerId, where DefaultAddress=1? There are some cases where a customer has more than one default address listed. I initally started looking at the query to see if I could eliminate the duplicates. Mike The revolution is just a t-shirt away.
I can almost guarantee you that, you will get the same execution plan(which actually depends on the indexes you have), regardless whether you put condition "A.DefaultAddress = 1". But as a best practice, for INNER JOINs, I’d recommend putting the JOIN condition(s) in the ON clause and the FILTER condition(s) in the where clause. Anyway the optimizer is going to rearrange them. With outer joins, its a totally different story. Roji. P. Thomas
http://toponewithties.blogspot.com
]]>