Microsoft SQL Server forum - Re: Effenciency of stored procedures

Author

Re: Effenciency of stored procedures

Erland Sommarskog

2005-05-27, 8:23 pm

(EggsAckley@Last.com) writes:
> Can you (or anyone) suggest general guidelines for improving the
> performance and/or efficiency of stored procedures under SqlServer
> 2000? Again, I very much appreciate any guidance.

The question is a bit open-ended. But here are some general points
from the top of my head:

o Try as much as possible to avoid iterative solution, and use set-
based solutions.

o When using temp tables, create them as the first executable statement
in the procedure. (DECLARE @local is not an executable statement.) If
you create temp tables in the middle of it all, you will get a
recompile.

o Share your graces between temp tables and table variables. Sometimes
ons is right, and sometimes the other. My general suggestion is that
you start with a temp table, but if you find that you get performance
problems because of recompiles, switch to temp tables. (Keep in mind
that those recompiles can just as well be life-savers!)

o And while it's sometimes it's a good idea to keep a temp table/table
variable for storage of intermediate results, it can also sometimes
be more effecient with one big query from hell that does it all in
one statement.

o Don't do this:
CREATE PROCEDURE some_sp ... @startdate = NULL, .... AS
IF @startdate IS NULL
SELECT @startdate = convert(char(8), getdate(), 112)
since SQL Server sniffs the parameter value, it will build query
plans assuming that @startdate is NULL. It's better to copy to
a local variable, of which SQL Server makes no assumption at all
about the value. Even more effecient is to move processing to an
inner procedure once all defaults have been filled in.

o Microsoft recommends that you always use two-part notation, for
instance "SELECT ... FROM dbl.tbl" and not "SELECT ... FROM tbl",
and claims this is more effecient. I claim that if the procedure
is owned by dbo, there should be no difference, and if there is,
that's a bug. One of these days, I will have to benchmark it.
Anyway, since MS recommends it, I thought I should mention it.

If you have particular issues you want to dicsuss, you are welcome.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

EggsAckley@Last.com

2005-05-27, 8:23 pm

On Fri, 27 May 2005 20:46:09 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

> (EggsAckley@Last.com) writes:
>
>The question is a bit open-ended. But here are some general points
>from the top of my head:
>
>o Try as much as possible to avoid iterative solution, and use set-
> based solutions.

set-based solutions vs iterative.
[color=darkred]
>
>o When using temp tables, create them as the first executable statement
> in the procedure. (DECLARE @local is not an executable statement.) If
> you create temp tables in the middle of it all, you will get a
> recompile.
>
>o Share your graces between temp tables and table variables. Sometimes
> ons is right, and sometimes the other. My general suggestion is that
> you start with a temp table, but if you find that you get performance
> problems because of recompiles, switch to temp tables. (Keep in mind
> that those recompiles can just as well be life-savers!)
>
>o And while it's sometimes it's a good idea to keep a temp table/table
> variable for storage of intermediate results, it can also sometimes
> be more effecient with one big query from hell that does it all in
> one statement.
>
>o Don't do this:
> CREATE PROCEDURE some_sp ... @startdate = NULL, .... AS
> IF @startdate IS NULL
> SELECT @startdate = convert(char(8), getdate(), 112)
> since SQL Server sniffs the parameter value, it will build query
> plans assuming that @startdate is NULL. It's better to copy to
> a local variable, of which SQL Server makes no assumption at all
> about the value. Even more effecient is to move processing to an
> inner procedure once all defaults have been filled in.
>
>o Microsoft recommends that you always use two-part notation, for
> instance "SELECT ... FROM dbl.tbl" and not "SELECT ... FROM tbl",
> and claims this is more effecient. I claim that if the procedure
> is owned by dbo, there should be no difference, and if there is,
> that's a bug. One of these days, I will have to benchmark it.
> Anyway, since MS recommends it, I thought I should mention it.
>
>If you have particular issues you want to dicsuss, you are welcome.

Thanks very much.

EA

Erland Sommarskog

2005-05-28, 3:23 am

(EggsAckley@Last.com) writes:[color=darkre
d]
>
> set-based solutions vs iterative.
>

A cursor is an iterative solution. Some people hear that cursors are
evil, so they go home and replace the cursor with a WHILE loop where
they do SELECT MIN from a table or somesuch. That's typiclally even
worse.

In a set-based solution you work on all data in one statement. I steal an
example from another thread, where a poster had a trigger like this:

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT,
@COUNTER AS INT,
@P_ID AS INT

SELECT @TEMP_ID = CATEGORY_ID,
@COUNTER = 1,
@P_ID = PARENT_CATEGORY_ID
FROM INSERTED
IF @P_ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
SELECT @TEMP_ID = @P_ID,
@COUNTER = @COUNTER + 1
FROM INSERTED
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER

This is an example of an iterative solution. Here is my rewrite of this
into a set-based solution:

CREATE TRIGGER category_tri ON CATEGORY FOR INSERT AS
UPDATE c
SET DEPTH = coalesce(p.DEPTH, 0) + 1
FROM CATEGORY c
JOIN inserted i ON c.CATEGORY_ID = i.CATEGORY_ID
LEFT JOIN CATEGORY p ON i.PARENT_CATEGORY_ID = p.CATEGORY_ID

In this case, we cannot compare performance, as the iterative trigger
was incorrect, but it illustrates the two different approaches.

There are situations where iterative solutions are required, or at least
can be justified. But in many situations, there are magnitudes of
performance to gain by using a set-based solution.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links