August 8, 2008 at 11:33 am
I have 2 queries below. Both before the exame same as far as performance and result. Is there a best or preferred method or does it depend on the one writing the query?
select cf.VCN
--,cf.deficientClaimForm, cf.reviewStatusID, d1.bgReceivedDate as [Event 10 Date], d8.bgReceivedDate as [Event 88 Date]
from tblClaimForms cf
inner join
(select foreignKeyID, bgReceivedDate
from tblDocuments
where documentTypeID = 10) d1 on d1.foreignKeyID = cf.VCN
inner join
(select foreignKeyID, bgReceivedDate
from tblDocuments
where documentTypeID = 88) d8 on d8.foreignKeyID = cf.VCN
where cf.deficientClaimForm = 1
and cf.reviewStatusID = 1
and ((d8.bgReceivedDate > d1.bgReceivedDate) or (d8.bgReceivedDate = d1.bgReceivedDate))
--------------------------
select cf.VCN
,cf.deficientClaimForm, cf.reviewStatusID, d1.bgReceivedDate as [Event 10 Date], d8.bgReceivedDate as [Event 88 Date]
from tblClaimForms cf
inner join tblDocuments d1 on d1.foreignKeyID = cf.VCN
inner join tblDocuments d8 on d8.foreignKeyID = cf.VCN
where d1.documentTypeID = 10
and d8.documentTypeID = 88
and cf.deficientClaimForm = 1
and cf.reviewStatusID = 1
and ((d8.bgReceivedDate > d1.bgReceivedDate) or (d8.bgReceivedDate = d1.bgReceivedDate))
August 8, 2008 at 12:25 pm
Check the query plans and if they are the same it really doesn't matter. I prefer the second because I find it easier to read.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2008 at 12:28 pm
I'd tend to go with the second one, all other things being equal.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 12:45 pm
Assuming the query plans are the same, I'd tend to go with the second one as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply