August 2, 2002 at 12:12 pm
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
August 2, 2002 at 1:48 pm
If you will create the index by adding in all the columns used in this query you will get some performance by using only the index versus then also having to go to the table pages.
Also, if you will use a 'not exists' instead of NOT IN you should have an increase in performance.
Good luck
Jody
August 2, 2002 at 1:57 pm
I would revise the sql like :
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
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
)
FOR READ ONLY
I hope this helps you
August 6, 2002 at 12:31 pm
Hi,
I tried with the following modified version of the query.
SELECT wire_cntr_name, living_unit_id, wtn, id
FROM ff_lu_wtn as flw
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.wire_cntr_id = @v_wire_center and a.id = flw.id)
But there was no improvement.
If needed, I can split the query into two queries, since it is cursor. So that makes a new cursor inside the first cursor. I don't know whether it would improve the performance.
Please provide your input and thoughts.
Thanx..
-Bheemsen
August 7, 2002 at 12:44 am
Hi, can you post your complete code of the stored procedure, maybe there is a way to avoid using a cursor.
Klaas-Jan
August 7, 2002 at 2:29 am
If possible at all, you should try to avoid the cursor loop. This is probably a greater cost than executing the query itself. You can test this, just by timing the query in Query Analyzer.
Anyway something I noticed in the 'not exists' clause.
You are using an INNER JOIN with the ff_lu_wtn table, the same table that is used in the general query clause. Maybe I'm missing some point, but I don't believe that is necessary.
Try the following :
SELECT wire_cntr_name, living_unit_id, wtn, id
FROM ff_lu_wtn as flw
Where wire_cntr_id = @v_wire_center and NOT exists
(SELECT *
FROM stage_lu_Address b where flw.living_unit_id=b.living_unit_id and flw.wire_cntr_id=b.wc_code)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply