Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: 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

  2. #2
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    Not in

    There 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •