Jim Lawrence
accessd at shaw.ca
Mon Jun 23 16:01:08 CDT 2008
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