Re: Do UDFs slow down Stored Proc execution?

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

Date: Wed, 17 Nov 2004 14:00:43 -0800

Thanks David,

Tuning the queries of course would be the correct thing to do. The
checkwriter is just a small part of a much larger intricate system. In
fact tuning probably would have been advisable while we were in the
early stages of creating the large business app that we find ourselves
with now. What we have done is converted a DOS app (Clipper) that had
originated in 1983 and evolved over time. Believe it or not, the DOS app
is still handling systems with over 500,000 invoices. The SQL system
works beautifully from a business logic perspective. But now we find it
performing slower than we had originally envisioned.

I have been plowing through the Microsoft SQL Server 2000 Performance
Tuning Tech Reference trying to find a place to start (don't laugh too
loud). Our execution plan consisted of writing copious code logic,
creating indexes and expecting SQL Server to simply handle it better
than Clipper did. After all, that approach worked well in the "desktop"
RDBMS.

For example, one stored proc that handles the bulk of all business
transactions for the system consists of over 1000 lines of code. More
than 40 variables are declared. It handles the decision making for 22
different invoice and cash related transactions such as Posting, Cash
Entry, Distribution, Adjustment, PrePay, Reversals, etc. and makes calls
to other procs that make entries and balance adjustments in the
Accounting, Invoicing and Order tables.

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?

Deconstructing an intricate system that is currently handling business
logic correctly is a daunting task. But if that is the only remedy then
it is time to swallow the bitter pill.

John P. Kotuby
IT Manager
Statware, Inc.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • 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)
  • Re: Stored proc timeout issue
    ... The program Opens the proc and times out after 2 minutes. ... proc from sql server mgmt studio, ... I began diagnosing the problem by commenting out various lines of code in the stored proc ... One thing that remained changed was the command timeout value. ...
    (borland.public.delphi.database.ado)
  • Re: create/using type dynamically
    ... Now i am looping thru the keys in a proc .Now for a key, ... values of that key want to use dynamic SQL and FORALL to update that ... In engineering the hardest part ... Please state the business case or business problem you are trying ...
    (comp.databases.oracle.misc)