logical reads count when specifying filter in join vs where clause

Question

logical reads count when specifying filter in join vs where clause

Sridhar-137443

Hall of Fame

Points: 3762
More actions
August 15, 2011 at 7:58 am

#241769

Hi,
I have the following query
declare @indate date = '8/15/2011',
@build int = 1,
@recordtype char(1) = 'P';
select pc.CoCode, pc.PositionNo
from dbo.PositionControl pc
left join dbo.PositionControlAssignment pca
on pca.PositionNo = pc.PositionNo
and pca.CoCode = pc.CoCode
and pca.StartDate <= @InDate
and pca.StopDate >= @InDate
and pc.Build = @Build
and pc.RecordType = @RecordType
and pc.StartDate <= @InDate
and pc.StopDate >= @InDate
If I run the above query, I got the logical reads for positioncontrol table as 4 and logical reads for positioncontrolassignment as 6.
If I change the query as follows
select pc.CoCode, pc.PositionNo
from dbo.PositionControl pc
left join dbo.PositionControlAssignment pca
on pca.PositionNo = pc.PositionNo
and pca.CoCode = pc.CoCode
and pca.StartDate <= @InDate
and pca.StopDate >= @InDate
where pc.Build = @Build
and pc.RecordType = @RecordType
and pc.StartDate <= @InDate
and pc.StopDate >= @InDate
then the logical reads for positioncontrolassignment is 623 and scan count is 310.
I am not sure which one is better. Based on my understanding, the one with less logical reads performs better.
Please let me know
Thanks,
Sridhar.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Those two queries are not equivalent, under many circumstances they'll return different data. Hence it's not a question which is better, it's a question which one returns the data you need.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Determining-the-position-of-search-arguments-in-a-join.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 2

Thank you. It makes sense. But I don't understand why it will have so many logical reads. It is in fact way more than the number of pages used to store the indexes. I will have to look into it deeper.