Grant Fritchey (4/2/2009)
Mainly based on the scan count. I'm still assuming, possibly incorrectly, that the second query was able to read from cache where as the first had to go to disk. I think if the second query had to go to disk as well, it'd be quite a bit longer.
Well they both had to read partially from disk, both have physical IOs.
Thing with the logical IOs is (if I'm remembering correctly), they're cumulative over all 'scans'. So a query that does 500 reads in one scan is doing the same number of IOs as a query that does 500 in 100 scans
(by the way, do you just memorize Connect bugs or something)
No. This came up in the private newsgroups a couple weeks back and the connect was referenced there.
Generally, I don't count on the logical IOs as a single measure.
Likewise, but in this case it's all we have so far.
Of course, in this case it's a moot point since the two queries that we're comparing aren't equivalent. One just has a join, the other a join and a filter.
Apples and pears.
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