Posted by EggsAckley on 05/28/05 00:57

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

> (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.

>>Please elaborate a little on what exactly you mean by
set-based solutions vs iterative.

>>Also what do you think about cursors?
>
>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

[Back to original message]