Posted by Hugo Kornelis on 12/09/05 02:14

On Mon, 5 Dec 2005 19:20:39 -0500, serge wrote:

>Is it generally or almost always better to have multiple
>small SPs and functions to return a result set instead of
>using a single big 1000+ lines SP?

Hi Serge,

The only answer that's always correct for this type of question is "it
depends".


>I have one SP for example that is 1000+ lines and early
>analysis of the SP I see it first has 3 big blocks of code
>separated by IF statements.

Seperating these blocks might be a good idea, for two reasons:

1. If the proc often has to be recompiled, the length of the proc will
influence the length of the recompilation. Shorter procs mean shorter
recompilation (and often less recompilations as well).

2. If the logic uses variables that were passed in to the proc and the
proc is NOT often recompiled, you'll often be executing code block 2
with an execution plan that is ideal for the parameters that caused
execution of code block 3.


(snip)
>I am thinking to cut this big SP to many smaller SPs and/or functions
>and I also plan on using table variable(s) to hold temporary result
>while I continue processing the records from the table variable
>with other code logic.

Functions can do three things to your performance: inline table valued
functions will keep performance about equal, multi-statement table
valued functions will decrease performance and scalar functions will
usually decrease performance. I've never seen a performance increase as
a result of using functions.

Same for temp tables - except very rare cases where the optimizer goofs
up and creates a sub-optimal query plan, it's better to just write one
complex query and let SQL Server figure out the best way to process it.

Of course, temp tables and user-defined functions CAN make your code
easier to read and maintain. In many companies, that's worth more than
some performance gain or loss.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]