Posted by serge on 12/06/05 02:20

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?

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. Then within each IF block
of code I see 3-4 UNIONs. UNIONs that means
they are all returning the same columns so I am
guessing these are prime candidates for becoming
individual functions or SPs, maybe even dynamic SPs.

Obviously I am not showing you the code but am I
right to think this way? This same SP has about 15 JOINs
including some LEFT JOINs and one LEFT JOIN to a (SELECT
statement) and almost all the tables referenced by these
JOINs have thousands of records, very possibly hundreds of
thousands.

The SELECT statement is returning 30-40 columns from
a lot of the these tables plus I also see a lot of CASE ELSE
statements within the main SELECT statement. The code of
each CASE statement is calling a function. As an example
if the CASE is for EmployeeID then a function is being called
to get the EmployeeID's FirstName and LastName. If the CASE
is for CustomerID then another function is being called to get
the Customer Name.

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.

Also I want to leave as the last thing to do is to convert the
"machine result", i.e. EmployeeID or CustomerID to "human
readable result", i.e. Employee FirstName and LastName,
Customer Name.

I am trying to test this on the Northwind's Employees table,
but the Statistics IO, Time and the Execution Plan are
something I've only started to use. I am unable to make
conclusion which method is better. I'll work on posting another
post specifically with details to this test that I am currently doing.

My opinion is that by having 1 single SP with 15+ join cause
a lot more locking than if I would run smaller SPs and store the
result into temp table variables and continue processing the
remaining code logic.

I would like to know what you think and if I am right or wrong
on how I want to optimize this SP?


Thank you

[Back to original message]