July 3, 2009 at 2:01 am
hey,
Normally we do write the queries using the joins (Ex.1), but we can write in the queires if different way w/o specifying the joins (we use "," instead & in where clause we put the join on clause). There are many ways.
I have gone through the execution plans also both give me the same results but there is time difference found when i executed using SET STATISTICS TIME ON
I would like to do some debate on this. please contribute your knowledge..
Ex.1
SELECTM.*, D.*
FROMMaster M
JOIN Detail D ON M.MasterId = D.MasterId AND M.MasterId = 1
Ex.2
SELECTM.*, D.*
FROM(SELECT * FROM Master WHERE MasterId = 1) M
JOIN (SELECT * FROM Detail) D ON M.MasterId = D.MasterId
Abhijit - http://abhijitmore.wordpress.com
July 3, 2009 at 6:41 am
Normally there should not be any difference in execution time and execution plan.
Be carefull to test with dbcc dropcleanbuffers and dbcc freeproccache, but don't do this on a production server.
Other reasons could explain the difference, maybe other queries running on the server,..
July 5, 2009 at 10:28 pm
Thankfully, the optimizer is pretty good at sorting out exactly what you mean regardless of how you structure your query. However, I would be wary of using the syntax you use in ex 2 because, when your virtual tables (the parts of the queries between the brackets) get a bit more complex, SQL Server may not be able to apply filters to this part of the query based on the rest of the query. Yes, this style of SQL (Ex 2)is particularly useful and should be used when appropriate but I would not use it as the norm.
Normally I would recommend using something like Ex 1 but using a where clause for the filtering conditions.
SELECT M.*, D.*
FROM dbo.Master M
INNER JOIN dbo.Detail D ON M.MasterId = D.MasterId
WHERE M.MasterId = 1
This syntax separates out the relationships between the table and the filtering conditions. This should make life easier for the person who needs to maintain your code.
Both will result in the same query plan so SQL Server will deliver the same level of performance.
I would also recommend that you use the table owner/schema for each table. This should reduce the number of re-compilations that SQL does.
And as a personal preference, I generally include the join type "INNER" in the query. This is the default so the query would not be any different. However, it may leave it ambiguous to other people as to whether you intended to use an inner or an outer join.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply