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.

    -Bheemsen

  • 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

  • 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

  • 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

  • Hi, can you post your complete code of the stored procedure, maybe there is a way to avoid using a cursor.

    Klaas-Jan

  • 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