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, ChrisRwhat 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
>sql

performance question

I've got 3 functions. one that calculates a value, and two other ones that
return a value based on the value returned by the first.
I use these in a view
example:
select
dbo.calcvalue(t.[id]) as value,
dbo.calc1(calcvalue(t.[id]) )) as val1,
dbo.calc2(calcvalue(t.[id]) )) as val2
from
tabletest t
will calcvalue() get called 3 times? or would it be more efficient to use
two views.
example:
view 1:
select
dbo.calcvalue(t.[id]) as value
from
tabletest t
view 2:
select
dbo.calc1(value) as val1,
dbo.calc2(value) as val2
from
view1user defined functions must be deterministic - give the same output for the
same input. I think one reason this restriction is there is so that sql can
eliminate performing second and third calls to your function. That said, I
don't know if it will call it more than once. You can insure it won't by
using a derived table.
select t.id, value, dbo.calc1(value) as val1, dbo.calc2(value) as val2
from
(
select id, calcvalue(id) as value
from tabletest
) as t
Jeremy wrote:
>I've got 3 functions. one that calculates a value, and two other ones that
>return a value based on the value returned by the first.
>I use these in a view
>example:
>select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
>from
> tabletest t
>will calcvalue() get called 3 times? or would it be more efficient to use
>two views.
>example:
>view 1:
>select
> dbo.calcvalue(t.[id]) as value
>from
> tabletest t
>view 2:
>select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
>from
> view1
--
Message posted via http://www.sqlmonster.com|||Jeremy,
The rules for scalar UDFs allow for many types of optimization,
including substitution. However, a simple test will show that both SQL
Server 2000 and SQL Server 2005 will not reuse the result of the UDF for
the same row.
So if you have an expensive dbo.calcvalue, and a very cheap dbo.calc1,
then the query
select dbo.calcvalue(id), dbo.calc1(dbo.calcvalue(id)) from t
will need almost twice as long to finish when compared to
select dbo.calcvalue(id), dbo.calc1(id) from t
--
Gert-Jan
Jeremy wrote:
> I've got 3 functions. one that calculates a value, and two other ones that
> return a value based on the value returned by the first.
> I use these in a view
> example:
> select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
> from
> tabletest t
> will calcvalue() get called 3 times? or would it be more efficient to use
> two views.
> example:
> view 1:
> select
> dbo.calcvalue(t.[id]) as value
> from
> tabletest t
> view 2:
> select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
> from
> view1