SQL Server Performance

Alternate for sql cursors

Discussion in 'T-SQL Performance Tuning for Developers' started by abhishek.goel123, Oct 23, 2008.

  1. abhishek.goel123 New Member

    Hi Guys,
    I am working on a performance tuning project but was not able to rewrite the below given procedure with out using cursors could any body advise , bcoz this is urgent.
    Thanks in advance

    CREATE PROCEDURE [dbo].S_LPM_SUPPORT_DATA
    AS
    BEGIN
    DECLARE @promotion_id INT
    DECLARE @STATUS VARCHAR(32)
    DECLARE @upcadded INT
    DECLARE @upcdeleted AS INT
    DECLARE @totalupcs as int
    DECLARE @TODAY AS SMALLDATETIME
    DECLARE @EXPORT_DATE AS SMALLDATETIME
    SELECT @TODAY = CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), GETDATE()), 103)
    DELETE T_LPM_SUPPORT_UPC_LIST WHERE PROCESSDATE= @TODAY
    DELETE T_LPM_support_upc_count WHERE STATUS = 'EXPIRED YESTERDAY'
    declare c1 cursor for
    select promotion_id, date_to_export
    from t_lpm_promotion (nolock)
    WHERE dbo.F_PROMOTION_STATUS(promotion_id, @TODAY) IN ('LIVE', 'PENDING_START')
    order by promotion_id
    open c1
    fetch next from c1
    into @promotion_id, @EXPORT_DATE
    while (@@fetch_status= 0)
    begin
    select @upcadded = count(*)
    from T_LPM_support_upc_count (NOLOCK)
    where promotion_id = @promotion_id
    and processdate = @TODAY
    and run_status = 2
    and upc not in
    (select upc from T_LPM_support_upc_count (NOLOCK)
    where promotion_id = @promotion_id
    and run_status = 1)
    and processdate = @TODAY
    select @upcdeleted = count(*)
    from T_LPM_support_upc_count (NOLOCK)
    where promotion_id = @promotion_id
    and processdate = @TODAY
    and run_status = 1
    and upc not in
    (select upc from T_LPM_support_upc_count (NOLOCK)
    where promotion_id = @promotion_id
    and run_status = 2)
    and processdate = @TODAY
    select @totalupcs = COUNT(UPC)
    from T_LPM_support_upc_count (NOLOCK)
    where promotion_id = @promotion_id
    and processdate = @TODAY
    and run_status = 2
    select @STATUS = STATUS
    from T_LPM_support_upc_count (NOLOCK)
    where promotion_id = @promotion_id
    and processdate = @TODAY
    and run_status = 2
    IF @UPCADDED > 0
    SELECT @EXPORT_DATE = @TODAY
    insert into T_LPM_SUPPORT_UPC_LIST
    values (@promotion_id, @upcadded, @upcdeleted, @TODAY, @STATUS, @EXPORT_DATE, @totalupcs)
    fetch next from c1
    into @promotion_id, @EXPORT_DATE
    end
    close c1
    deallocate c1
    end
    GO
  2. Adriaan New Member

    You could turn the four queries inside the loop into four aggregate queries (adding the "cursor table" to each FROM clause, with a JOIN) storing the results of each into separate temp tables (SQL 7.0) or table variables (SQL 2000). This way you should have totals GROUPed BY promotion_id and run_status.
    You should look at the logic from the F_PROMOTION_STATUS udf, and try to implement that logic in the main query statements, not by calling the udf.
    Next, you can join those temp tables and insert the combined results into the final table, using the NOT IN subqueries to suppress the ones you don't want.
    Finally (SQL 7.0) you should explicitly drop the temp tables.
  3. abhishek.goel123 New Member

    Hi Adriaan,
    Thanks for the quick reply. It is good option to do that way but will the table variables or the temp tables degrade the performance ...
    I am using sql server 2000.
  4. Adriaan New Member

    There's only one way to find out - create another sproc that uses the temp tables/table variables, and test them both.
    If the number of iterations for the cursor is low, the solution with the cursor might be quicker than the set-based version. Difficult to say at what point the set-based version would be quicker.
    So you need to test with large amounts of data - but realistic for what you expect to be processing in production.
  5. abhishek.goel123 New Member

    Hi Adriaan ,
    I will try to test the data with both the options . I have one more issue Do you have any idea regarding splitting the database files so that the application can use multithreadinig capability of the server . My sql procces now is taking around 8 hr over night batch and is a squential process.
    The sql server is a 4 processor machine and it is utilising only one cpu.
  6. Adriaan New Member

    Since your cursor-based proc is doing everything one step at a time, there is no point for SQL to use more than one cpu ...

Share This Page