Odd behavior when calling a user function within an aggregation function

While debugging a code on the sql server 2000 debugger, I have encountered a
strange behavior while calling a user function from whitin the sum
aggregation function.
I have reproduced this behavior with the following code:
CREATE FUNCTION Test1 (@.prm decimal(18,2))
RETURNS decimal(18,2)
AS
BEGIN
return @.prm
END
---
CREATE PROCEDURE Test2
AS
declare @.var decimal(18,2)
set @.var=0
select @.var=sum(dbo.test1(Col1))
from Tbl1
print @.var
---
Col1 is a decimal(18,2) column on table Tbl1.
Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
for each row of Tbl1, but the sum result is ok.
For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
the result will be 3 as it should be.
On my original code I am using a recursive function that is running on a
tree and not a simple one like in this example, so in my case it harms the
performance quite a lot (actually any node on the tree will be executed 2^h
when h is the height of the node).
I replaced the sum function with the variable that sums itself the values as
followed:
select @.var = @.var + dbo.test1(Col1)
But still i'm not sure if there's no a better idea to solve it.
What is the cause of this behavior? Is it only while debugging?
Any ideas and thoughts will be appreciated.
Thanks, apscalar function gets invoked once per row. this is by design, i'm afraid.
-oj
<ap> wrote in message news:%23blucZYRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> While debugging a code on the sql server 2000 debugger, I have encountered
> a strange behavior while calling a user function from whitin the sum
> aggregation function.
> I have reproduced this behavior with the following code:
> CREATE FUNCTION Test1 (@.prm decimal(18,2))
> RETURNS decimal(18,2)
> AS
> BEGIN
> return @.prm
> END
> ---
> CREATE PROCEDURE Test2
> AS
> declare @.var decimal(18,2)
> set @.var=0
> select @.var=sum(dbo.test1(Col1))
> from Tbl1
> print @.var
> ---
> Col1 is a decimal(18,2) column on table Tbl1.
> Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
> for each row of Tbl1, but the sum result is ok.
> For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
> enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
> the result will be 3 as it should be.
> On my original code I am using a recursive function that is running on a
> tree and not a simple one like in this example, so in my case it harms the
> performance quite a lot (actually any node on the tree will be executed
> 2^h when h is the height of the node).
> I replaced the sum function with the variable that sums itself the values
> as followed:
> select @.var = @.var + dbo.test1(Col1)
> But still i'm not sure if there's no a better idea to solve it.
> What is the cause of this behavior? Is it only while debugging?
> Any ideas and thoughts will be appreciated.
> Thanks, ap
>
>|||>> What is the cause of this behavior?
In the topic
http://msdn.microsoft.com/library/e...des_08_460j.asp
they say:
"The number of times that a function specified in a query is actually
executed can vary between execution plans built by the optimizer."
This behaviour can also be revealed by using the "Show Server Trace"
option in Query Analyzer or using Profiler, so it seems that it does
not happen only while debugging.
You can use this statement, instead:
SELECT @.var=SUM(X) FROM (
select top 100 percent dbo.test1(Col1) X
from Tbl1 order by X
) A
It seems that this way the function is called only once for each row.
Razvan

odbc index problem

Hi all,
Here is a brief description of a problem I encountered, and how I
found a work around after 3 long days.

I have a VB6 app that uses ADO and ODBC to get communicate with SQL
server 2000 (sp3, running in win2003).

Everything was running great for a few weeks, but one day an update
statement that used to work just stopped working. It was a simple
update of 1 field in a table (about 30 columns, about 20k records).
SQL server acutally hung while it waited for a response that never
came, and everyone else on the network was also locked out of sql
server. Everyone had to do a ctrl alt del to crash the programme.

Steps I took...
Rebuilt the database. Same.
Restored backup. Same.
Moved database to another server. Same.
Checked for viruses, that no updates had happened, memory checks and
so on.
Still no joy.
Someone then recommended tinkering with the indexes of the table in
question. Which I did.
I added indexes, removed them, and eventuall found a combination that
worked. And this is it.

I removed the primary key, and replaced it with a clustered index.
That's it. And now it all appears to be running OK.

WHY WHY WHY?
I just don't get it.
Have I found a bug in sql server or odbc or ado? Is it a known issue?
What is the impact of what I have done?

Thanks in advance for your comments, and I hope this solution saves
someone else 3 days of hell!

TimTim (thew@.ltons.freeserve.co.uk) writes:
> I have a VB6 app that uses ADO and ODBC to get communicate with SQL
> server 2000 (sp3, running in win2003).

You should be using the SQLOLEDB provider. The default provider, OLE DB
over ODBC may be good for data sources for which there is no targeted
OLE DB provider, but this is not the case for SQL Server. Not that this
has anything to do with the problem you are describing, but nevertheless
I like to point this out.

> Everything was running great for a few weeks, but one day an update
> statement that used to work just stopped working. It was a simple
> update of 1 field in a table (about 30 columns, about 20k records).
> SQL server acutally hung while it waited for a response that never
> came, and everyone else on the network was also locked out of sql
> server. Everyone had to do a ctrl alt del to crash the programme.
> Steps I took...
> Rebuilt the database. Same.
> Restored backup. Same.
> Moved database to another server. Same.
> Checked for viruses, that no updates had happened, memory checks and
> so on.
> Still no joy.
> Someone then recommended tinkering with the indexes of the table in
> question. Which I did.
> I added indexes, removed them, and eventuall found a combination that
> worked. And this is it.
> I removed the primary key, and replaced it with a clustered index.
> That's it. And now it all appears to be running OK.
> WHY WHY WHY?
> I just don't get it.
> Have I found a bug in sql server or odbc or ado? Is it a known issue?
> What is the impact of what I have done?

There is next to nothing of useful information to comment the actual
case, so I can only answer in general terms.

In general, one needs to understand there are few tools that are so
powerful to make things run really slow like a relational database engine.
All modern DBMS has a cost-based optimizer that seeks find the best
way to execute a query, and to make its decisions it uses some information
about the data. SQL Server maintains statistics about the data and how it
is distributed. Most queries can be executed in a number of ways, and
the optimizer tries to estimate the most effecient plan. Note that was
the best plan yesterday, may not be the best plan today, because data
has changed, for instance increased in size. While optimizers often do a
good job, they are estimates, and sometimes things can go seriously wrong.
So this could explain why your query worked fine one day and then was
out to lunch the next day.

Now, there is a whole lot of things you can do help the optimizer, and
the most important is to ensure that your tables properly indexed for
the queries you use. After all, if you are to access one single row in
a million-row tables without any index at all, there is no more effecient
plan than to scan all million rows.

So there is nothing magic going on here, and least of all of any bug.
And ODBC is completely innocent. If you submit your own UPDATE statement,
ADO is too. If you use the .Update method in ADO, which I recommend
against, ADO is the one that builds the UPDATE statement. But the major
area for concern is the database design. If one has no knowledge about
database design and no understanding about indexing, you are very likely
to run into performance problems sooner or later, as soon as you get any
volume in your database.

If you feel that you would like to learn something in this area,
attending a class on SQL Server performance may be a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp