July 27, 2007 at 2:27 am
Comparing the client statistics the inner join with criteria in the join seemed to be faster by about 1 sec on +-10000 records on my machine over an average of 10 runs
July 27, 2007 at 2:28 am
Correct Approach
SELECT [ClientNam] FROM dbo.Client C
INNER JOIN dbo.ContactInfo CI ON CI.[ClientID] = C.[ClientID] AND CI.[IsValid] = 0
WHERE
C.[IsNew] = 0
July 27, 2007 at 2:32 am
The execution plan is exactly the same
July 27, 2007 at 2:46 am
As you already found out, the execution plan is exactly the same. The reason for this is that the Query Optimizer realises that both queries request the same data and then optimizes the way how to look up the data.
If you look closely on the details of the execution plan you probaly see when the filter CI.[IsValid] = 0 is executed. My guess would be that it happens during the join.
Markus
[font="Verdana"]Markus Bohse[/font]
July 27, 2007 at 5:08 am
It actually would vary if the last table is used to join with a seperate 3rd table. The results will vary. It depends on the business logic that is expected. And most of the times, the IsVAlid in the joining clause will give less results as compared to where clause, if data for such a condition exists.
regards,
P
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply