[dba-SQLServer] Scalar UDFs -- NOT!

Arthur, would it be any faster to insert the result (sans udf) into a
temp table (or table variable) then run the udf against the data in
temp table instead of every possible record?

David

On Tue, Jun 24, 2008 at 3:12 AM, Arthur Fuller <fuller.artful at gmail.com> wrote:
> 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
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>