Re: Do UDFs slow down Stored Proc execution?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 11/17/04 

Date: Wed, 17 Nov 2004 17:11:32 -0500

"John Kotuby" <jkotuby@snet.net> wrote in message
news:udBljDPzEHA.4028@TK2MSFTNGP15.phx.gbl...
>
> I don't know if you actually want to see that proc. I don't think anyone
> has ever posted 1000 lines of code for comment. What I am beginning to
> suspect is that this large proc should be broken down into a hierarchy
> of smaller procs that SQL Server can digest more readily. How does one
> "tune" such a large procedure?
>

    People have posted sprocs larger than that... and I've ignored their
posts ;)

    Seriously, though, tuning procs that large can be a pain. Your best bet
is, as you suspect, breaking it down into smaller chunks. It sounds like
that stored procedure does many different types of tasks which should be
logically separate, so it sounds like a good candidate for factoring out
into smaller pieces. So start there. That will let you focus on it in
small parts rather than looking at the whole thing at once, which is going
to be overwhelming. Don't worry about duplicated code too much -- that's
how most of these monster stored procs begin life -- someone thinks they can
be clever. Clever is, generally, not as good as maintainable! Take it
piece by piece, learn to use Query Analyzer's Show Execution Plan option
(which is absolutely essential), and start by eliminating as many "scans" as
possible. You'll want to look at sites like www.sql-server-performance.com
. If the book you're referring to is the one by Ken England, good choice.
That's a good primer... If it's by someone else, I can't comment :) 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Relevant Pages

  • Re: MV community lawsuits
    ... Why can't verbs like DELETE, CLEARFILE always have a ... > confirmation unless being executed from a proc or basic? ... Would this clever thing resemble the ...
    (comp.databases.pick)
  • Re: Another trip down Memory Lane: SQUARES
    ... One I remember was called "TUP". ... It was a PROC, which I thought quite ... clever. ... I wouldn't mind getting that; it may have been just a little ...
    (comp.databases.pick)
  • Re: Another Concurrency Issue!!!!
    ... > allocating in blocks, using tables with identities, etc. ... > Columnist, SQL Server Professional ... I have a proc which returns a value after some DML operations on ... >> TIA ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005
    ... The SQL Server implementation is not just Stored Procs, ... it works very tightly in proc with the SQL Server Engine(most ... Now with all that I have said T-SQL is not going away and is still a needed ... >> the CLR is now supported in stored procedures and triggers. ...
    (borland.public.delphi.non-technical)
  • Re: Performance Problem Using ADO and Stored Procs
    ... >I have a stored proc that executes in < 15 seconds through Query Analyzer. ... If I execute this proc ... > of CPU activity on the server hosting SQL server for the SQL server ... > Private Function RunProc(vntDB As Variant, strProcName As String, ...
    (microsoft.public.data.ado)