[dba-SQLServer] Scalar UDFs -- NOT!

The problem, insofar as my investigations have identified it (serious
qualifer there), is that calling a UDF in a WHERE clause causes it to
execute for every potential row, emphasis on Potential, i.e. every actual
row investigated prior to the WHERE clause's cutting down the qualifying
rows. And I think (just guessing) this is where the performance penalty
kicks in. So let me revise my previous diatribe and say instead "don't use
scalar UDFs in a WHERE clause".

The UDFs in question did things such as return the PK corresponding to a
description, i.e. BankAccountType('Savings') might return 3, say. The
original consultant thought that these UDFs would be good because if
anything changed, it would only have to change in one place, and to that
extent he is certainly correct. But in practice it turns out that calling a
bunch of these (similar) functions costs us dearly. 23 seconds to zero
seconds is meaningful, especially given that the sproc in question is called
frequently.

New topic:
Suppose that a web app calls sproc A, passing parm 123, and its execution
takes 3 seconds.
Suppose that another user logs on and at approximately the same time
executes sproc A, passing parm 234.
Suppose that another user logs on and at approximately the same time
executes sproc A, passing parm 345.

What happens to the execution time? Does each additional user executing the
same sproc with different parms cause the time to multiply by the number of
users? Or perhaps it's better engineered than that and some optimization
occurs under the covers. I don't know enough about the belly of the beast to
even guess about this. Does somebody on this list?

TIA.
Arthur


On Mon, Jun 23, 2008 at 6:01 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> Hi Arthur:
>
> Does the poor performance persist even when the UDF is called a numbers
> time? It should load into memory and optimize when called a number of
> times?
>
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
> Fuller
> Sent: Monday, June 23, 2008 12:36 PM
> To: Discussion concerning MS SQL Server
> Subject: [dba-SQLServer] Scalar UDFs -- NOT!
>
> When scalar UDFs first appeared, I was a big fan. No longer. In my current
> project, we had a bunch of them and in some sprocs they were called
> frequently. In one sproc various UDFs were called 57 times (they were
> emebedded in CASE WHEN blocks and such, mostly, but some were called from
> WHERE clauses. The first sproc I investigate was taking 28 seconds to
> execute. I copied it and replaced all the UDFs with hard-coded calls. The
> execution time went down to 3 seconds. I then tackled another
> time-consuming
> sproc (23 seconds) and did the same thing. The results were even more
> spectacular. Execution time as reported in Query Analyzer was zero seconds.
>
> I'm big enough to eat my own words. I've written various articles about how
> cool scalar UDFs are. I take it all back.
>
> Arthur
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>