Performance of a curosr

[bheemsen]

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.

-Bheemsen

 

[tlbroadbent]

Change the NOT IN to NOT EXISTS. Change the LEFT OUTER JOIN to an INNER JOIN. LEFT JOIN says give me all records from the LEFT table and matching rows from the RIGHT table. The LEFT table is ff_lu_wtn so the ID will always be found.

However, you added a redundant WHERE clause (a.living_unit_id=b.living_unit_id) which is identical to the ON clause. Thus you've converted the OUTER JOIN to an INNER JOIN. After these changes, the cursor declare should look like this.
[tt]
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 Not Exists
(SELECT *
FROM ff_lu_wtn a
INNER JOIN stage_lu_Address b
ON a.living_unit_id=b.living_unit_id
AND a.wire_cntr_id=b.wc_code
WHERE a.id=ff_lu_wtn.id
AND a.wire_cntr_id = @v_wire_center)
FOR READ ONLY[/tt]

I recommend the following additional Index.

CREATE NONCLUSTERED INDEX [FF_LU_LIVINGUNIT]
ON [dbo].[FF_LU_WTN]([LIVING_UNIT_ID], [WC_CODE])

Do you have a clustered index on either table? If not, consider the following in place of the nonclustered on FF_LU_WTN.

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

It may take a little experimentation to find the best indexes. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bheemsen]

Terry,

Thanx for the reply and the tip. I will give it a try and post the performance.

Thanx again.
-Bheemsen