performance question

Why does Query1 below have a Scan with 21 Logical Reads for 31 records
returned (a small percentage of Logicalreads), when Query 2 has no Scan,
only returns 6 records, and has 14 Logical reads. (a higher percentage)
In other words, the more data, the fewer Logical reads but no Scan. The less
data, higher reads but a scan. What gives?
SQL2K sp3a
TIA, ChrisR
what query? what data types are the columns?
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ChrisR" <noemail@.bla.com> wrote in message
news:OLWXZnMiFHA.3672@.TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
> SQL2K sp3a
> TIA, ChrisR
>
|||The scan on a clustered or nonclustered index at the leaf level is pretty
simple since it goes from leaf page to leaf page in order. It can read
relatively few pages to get a lot of rows that way. A seek has to traverse
the B-Tree from root to leaf level for each row returned. Depending on the
size of the index tree it can read a minimum of 2 pages per row seeked.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OLWXZnMiFHA.3672@.TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
> SQL2K sp3a
> TIA, ChrisR
>