By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,521 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Using muliple smaller SPs and Functions better than using 1 big SP?

P: n/a
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
Dec 6 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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)
Dec 9 '05 #2

P: n/a
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
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.


Sometimes you need to reuse a table expression in a query. In this case a
table variable or temp table may be a good idea to hold the intermediate
result, so that it is only computed once.

Notice that this also applies in SQL 2005 with regards to Common Table
Expressions. If you have:

WITH CTE (...) AS
SELECT ...
FROM CTE c1 ...
JOIN CTE c2 ...

SQL Server will computed the CTE twice, and thus only treat the CTE as a
macro.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 9 '05 #3

P: n/a
On Fri, 9 Dec 2005 20:22:23 +0000 (UTC), Erland Sommarskog wrote:
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
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.


Sometimes you need to reuse a table expression in a query. In this case a
table variable or temp table may be a good idea to hold the intermediate
result, so that it is only computed once.


Hi Erland,

Yes, you're right. I forgot to mention that. Thanks for the addition!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Dec 9 '05 #4

P: n/a
> Sometimes you need to reuse a table expression in a query. In this case a
table variable or temp table may be a good idea to hold the intermediate
result, so that it is only computed once.


Erland,

also sometimes we materialize intermediate results in a temporary
table, build some indexes on it, so that there are statisitcs on the
temp table. This way the optimizer must recompile, and it considers the
statistics on the temp table in the process. As a result, we may get a
much better plan and much faster execution.
Sometimes, not always, not often.

Dec 9 '05 #5

P: n/a
Everyone gets all excited about compile time. Compiling in my
experience has always been pretty insignificant compared to crunching
the data.

Like the man said, "it depends." I don't really like 1000 line SP's,
but have written and worked on a few. If you have a total of 5 10k
SP's, in a database, I'd argue that you could make things simpler by
breaking them into more reasonable logical pieces.
However, if you have 497 SP's, each 15 lines wrong, then I'd probably
argue that you could consolidate it somewhat for maintenance purposes.
All in all, if you are wading through a 1000 line Sproc, and reading
code to figure out what it is doing, then I'd argue strongly the
documentation sucks.

Dec 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.