Posted by Gert-Jan Strik on 12/06/05 23:11

serge, See inline

serge wrote:[snip]
> Summary:
>
> The first code is clean, 1 single SELECT statement but
> takes 4 long seconds to execute. The logical reads are
> very few compared to the second method.

That is because the logical reads used by the UDF are not included in
the statistics.

> The second code is less clean and uses a temp table but
> takes 0 second to execute. The logical reads are way
> too high compared to the first method.

No, the reads of the first method are too low.

> What am I supposed to conclude in this example?

That scalar UDF's can result in poor performance, and that its
performance can be even worse if the the GROUP BY clause also contains a
scalar UDF.

> Which method should I use over the other and why?

I wouldn't use either, see below

> Are both methods good depending on which I prefer?
> If I can wait four seconds, it's better to reduce the logical
> reads in order to provide less Blocking on the live tables
> in a heavily accessed database?
>
> Which method should I choose on my own database?
> Calling a function like dbo.GetEmployeeName gets
> processed per each returned row, correct?

Correct (assuming the SQL Server 2000 implementation). But since UDFs
are deterministic, the future implementation could easily change, and
results could (potentially) be cached and reused. In SQL Server 2000 the
UDF will be called at least one time per row. It might be more than one
time, and the UDF may also be called for rows that are discarded from
the result afterwards.

> That means
> If i had a scenario where 1000 records were to be returned
> would it be better to dump 1000 records to a temp table
> variable and then call a function to process each record
> one at a time?
>
> Or would the direct approach without using
> a temp table cause slower processing and more
> blocking/deadlocks because I am calling the function
> per each row as I am accessing directly from the tables?

In your example, there is no reason to use a temp table. Also, there is
no reason to use a UDF. You could simply write

SELECT EmployeeID
, MIN(FirstName + ' ' + LastName) AS Employee
, SUM(UnitPrice * Quantity) AS Amount
FROM Orders
INNER JOIN "Order Details"
ON Orders.OrderID = "Order Details".OrderID
INNER JOIN Employees
ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Orders.EmployeeID
HAVING SUM(UnitPrice * Quantity) BETWEEN 100000 AND 200000

Maybe your example does not represent your problem properly. If you post
the real problem, someone might have a smart solution for it.

Gert-Jan

[Back to original message]