Do UDFs slow down Stored Proc execution?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Kotuby (jkotuby_at_snet.net)
Date: 11/17/04 

Date: Wed, 17 Nov 2004 12:11:08 -0500

I have a client/server checkwriter (VB/SQL Server 2000) that seem to be
running slowly upon commiting of the data back to the database. Originally
it was written without UDFs (in SQL 7). But along came SQL 2000 and I
decided to go with numerous UDFs to replace small procs that return a single
value, like a Date as a string, or an Error constant.

Admittedly the data commit procedure is quite involved, updating 14 tables
with accounting balances, check logging, detail (invoice#) logging and a
host of other things. I noticed that in a DB containing only 60,000
invoices, it takes 75 seconds to commit a check run of only 5 checks and 38
detail (invoice) lines. This is on a 2.2gh single processor box with 1gb of
ram and not much else running on it other than SQL Server. I believe that I
have created enough indexes to accomodate the task.

I have read that UDFs can slow performance. Which leads me to wonder why
Microsoft included them in SQL 2000. Is this actually true? Do they mess up
an optimization plan?

I tried creating a Work Load table from the SQL Profiler during a "check
run" to feed Index Optimizer , but it appears that the presence of the UDFs
also don't allow the Work Load to be used by the Optimizer.

What is the take on UDFs? Should they be avoided for the sake of
performance?

Relevant Pages

  • Re: Opinions on approach, please...
    ... Code conversion is much more tricky. ... to update more than 32k records without a commit in-between. ... I advise you to do cursor definitions on working storage). ... I don't want to see ANY SQL code in them. ...
    (comp.lang.cobol)
  • Re: index block cleanout
    ... alter system flush buffer_cache; ... PL/SQL Release 10.2.0.4.0 - Production ... SQL> insert into test_empty_block ...
    (comp.databases.oracle.server)
  • Re: Straight SQL always put perform PL/SQL?
    ... If you cannot do it in a single SQL Statement, ... end loop; ... analyze table test1 compute statistics; ...
    (comp.databases.oracle.server)
  • Re: Can I Create Materialized View with REFRESH FAST ON COMMIT & UNION ?
    ... We have requirement to create MV with REFRESH FAST ON COMMIT & UNION. ... CREATE MATERIALIZED VIEW LOG ON TRANS1 ... SQL> create materialized view log on trans1 WITH ROWID; ...
    (comp.databases.oracle.server)
  • Re: Do UDFs slow down Stored Proc execution?
    ... UDFs can improve performance if used in the right places; ... of UDFs in SQL Server 2000 having to do with parallelism. ... I noticed that in a DB containing only 60,000> invoices, it takes 75 seconds to commit a check run of only 5 checks and 38 ... > also don't allow the Work Load to be used by the Optimizer. ...
    (microsoft.public.sqlserver.programming)