Re: Variables make Stored Procedure SLOW

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Derrick Leggett (derrickleggett_at_yahoo.com)
Date: 10/23/04 

Date: Sat, 23 Oct 2004 11:54:07 -0500

Use a table variable instead of the temp table Chris. You're creating the
temp table after you declare and set the other parameters. This is probably
causing a recompile. In addition, if this is a totals table with less than
10k rows, the table variable would be faster.

"Chris White" <nothanks> wrote in message
news:#lQA76GuEHA.2948@TK2MSFTNGP15.phx.gbl...
> Why do variables make my stored procedure crawl? If I use hard coded
values
> the SP below runs in 7 seconds, add 3 variables and it's over a minute(67
> seconds). In fact, just Declaring and Setting the variables makes take 3
> times longer(21 seconds).
>
> DECLARE
> @sp int,
> @ep int,
> @yr int
>
> SET @sp = 6
> SET @ep = 9
> SET @yr = 2004
>
> CREATE TABLE #INVSALES (
> branch varchar(4),
> total1 decimal(18,2)
> )
>
> CREATE TABLE #COGS (
> branch varchar(4),
> total2 decimal(18,2)
> )
>
> INSERT INTO #INVSALES
> SELECT DISTINCT
> RIGHT(gl.account_number,4),
> SUM(gl.amount) * -1
>
> FROM
> gl(NOLOCK)
>
> WHERE
> gl.period >= 6
> AND gl.period <= 9
> AND gl.year_for_period = 2004
> AND left(gl.account_number,4) = '6010'
>
> GROUP BY RIGHT(gl.account_number,4)
> ORDER BY RIGHT(gl.account_number,4)
>
> INSERT INTO #COGS
> SELECT DISTINCT
> RIGHT(gl.account_number,4),
> SUM(gl.amount)
>
> FROM
> gl(NOLOCK)
>
> WHERE
> gl.period >= 6
> AND gl.period <= 9
> AND gl.year_for_period = 2004
> AND left(gl.account_number,4) = '7020'
>
> GROUP BY RIGHT(gl.account_number,4)
> ORDER BY RIGHT(gl.account_number,4)
>
> SELECT
> 'A' AS section,
> 'ACCOUNTS RECEIVABLE' AS description,
> ISNULL(#INVSALES.branch, #COGS.branch) AS branch,
> ISNULL(#INVSALES.total1, 0) AS total1,
> ISNULL(#COGS.total2, 0) AS total2
> FROM
> #INVSALES
> FULL OUTER JOIN #COGS
> ON #INVSALES.branch = #COGS.branch
>
> DROP TABLE #INVSALES
> DROP TABLE #COGS
>
>

Relevant Pages