Performance of stored procedure varies

I have run into an issue that if I use the parameters passed into strored
procedure directly the performance is slower than when i declare local
variable and assign the values passed in the procedure to those local
variable and then use the local variables.
Why is this the case?
RahulRead this:
http://www.microsoft.com/technet/pr...comp.mspx#E6TAE
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:%23RXsRNbuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have run into an issue that if I use the parameters passed into strored
>procedure directly the performance is slower than when i declare local
>variable and assign the values passed in the procedure to those local
>variable and then use the local variables.
> Why is this the case?
> Rahul
>

Performance of stored procedure varies

I have run into an issue that if I use the parameters passed into strored
procedure directly the performance is slower than when i declare local
variable and assign the values passed in the procedure to those local
variable and then use the local variables.
Why is this the case?
Rahul
Read this:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#E6TAE

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:%23RXsRNbuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have run into an issue that if I use the parameters passed into strored
>procedure directly the performance is slower than when i declare local
>variable and assign the values passed in the procedure to those local
>variable and then use the local variables.
> Why is this the case?
> Rahul
>

Performance of stored procedure varies

I have run into an issue that if I use the parameters passed into strored
procedure directly the performance is slower than when i declare local
variable and assign the values passed in the procedure to those local
variable and then use the local variables.
Why is this the case?
RahulRead this:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx#E6TAE
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:%23RXsRNbuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have run into an issue that if I use the parameters passed into strored
>procedure directly the performance is slower than when i declare local
>variable and assign the values passed in the procedure to those local
>variable and then use the local variables.
> Why is this the case?
> Rahul
>

Performance of a cursor

Hi,

I have a following cursor coded in a stored procedure. This part of the procedure is taking long hours to complete.

DECLARE FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR CURSOR LOCAL FAST_FORWARD
FOR
SELECT wire_cntr_name, living_unit_id, wtn, id
FROM ff_lu_wtn
Where wire_cntr_id = @.v_wire_center and id NOT IN
(SELECT id
FROM ff_lu_wtn a left outer join stage_lu_Address b ON a.living_unit_id=b.living_unit_id and a.wire_cntr_id=b.wc_code
WHERE a.wire_cntr_id = @.v_wire_center and a.living_unit_id=b.living_unit_id)
FOR READ ONLY

Table ff_lu_wtn has around 100,000 rows and table stage_lu_address has around 90,000 rows. The number of rows keeps changing every time this procedure runs.

I tried to improve the performance by creating the following two indexes, but there was no improvement.

CREATE NONCLUSTERED INDEX [FF_LU_WTN0] ON [dbo].[FF_LU_WTN]([WIRE_CNTR_ID])

CREATE NONCLUSTERED INDEX [STAGE_LU_ADDRESS1] ON [dbo].[STAGE_LU_ADDRESS]([LIVING_UNIT_ID], [WC_CODE])

I appreciate if someone can help me find a way to improve the performance. It can be either by:

1. splitting the above cursor into two cursors.
2. getting rid of left outer join and making a select statement within the cursor.
3. any other way.

I need this urgently, please help.

-BheemsenThere could be so many reasons behind the slow performance of your cursor,but I would first try to remove the 'Not In' clause with either IN clause or by using a #Temptable:

run this query in a #temptable and then use the #temptable in the cursor:
SELECT id
INTO #temptable
FROM ff_lu_wtn a left outer join stage_lu_Address b ON a.living_unit_id=b.living_unit_id and a.wire_cntr_id=b.wc_code
WHERE a.wire_cntr_id = @.v_wire_center and a.living_unit_id=b.living_unit_id

Also add DISTINCT ID in the SELECT id query,this should help.

So it will be something like:

1 Select DISTINCT ID in the temptable
2 DECLARE cursor........ID not in (SELECT ID FROM TEMPTABLE).

By uaing the DISTINCT clause you are reducing the number of ID's