SQL Server Performance

Long Running Cursor

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sijeffs, Feb 18, 2009.

  1. sijeffs New Member

    Hi Everyone,
    I'm new to SQL so apologies if my question is simple but I'm struggling with how best to complete the task I have.
    I have a record set for customer policies with a start and end date (they may last several years), I want to create a table with the policykey and a start and end date for each year between the start and end date of the policy.
    I have used the below code to create the table but it runs for over 2 hours. For info there are approx 360,000 records in the initial select so over a million should be generated in the final table.
    DECLARE @Date datetime
    DECLARE @ENDDATE datetime
    DECLARE @POLICYKEY varchar(50)

    DECLARE c1 CURSOR READ_ONLY
    FOR
    SELECT table1.policy_key,table1.POL_START_DATE ,table1.dateadd(yy,1,POL_END_DATE)
    FROM table1 INNER JOIN table2.FACT_POLICY_SUMMARY
    ON table1.POLICY_KEY = table2.POLICY_KEY
    OPEN c1
    FETCH NEXT FROM c1
    INTO @POLICYKEY, @DATE, @ENDDATE
    WHILE @@FETCH_STATUS = 0
    BEGIN
    exec SP_POLICY_YEARS @POLICYKEY, @DATE, @ENDDATE
    -- PRINT @POLICYKEY + CAST(@ENDDATE AS VARCHAR(50))-- + @DATE
    FETCH NEXT FROM c1
    INTO @POLICYKEY,@DATE,@ENDDATE
    END
    CLOSE c1
    DEALLOCATE c1
    The SP just inserts the record into the new table.
    Any help is greatly appreciated.
    Kind Regards.
    Simon
  2. Madhivanan Moderator

    Post the code used in the procedure
  3. sijeffs New Member

    SP details below
    ***************************
    insert into POLICY_YEARS VALUES(@POLICYKEY,
    DATEADD(day, DATEDIFF(day, '20000101', @DATE), '20000101'),
    DATEADD(day, DATEDIFF(day, '20000101', DATEADD(DAY,-1,(dateadd(year,1,@Date)))), '20000101'))
    WHILE (@Date < @ENDDATE)
    BEGIN
    BEGIN
    SET @Date = dateadd(year,1,@Date)
    END
    IF(@Date > @ENDDATE)
    BREAK
    ELSE
    insert into POLICY_YEARS VALUES(@POLICYKEY,
    dATEADD(day, DATEDIFF(day, '20000101', @DATE), '20000101'),
    dATEADD(day, DATEDIFF(day, '20000101', DATEADD(DAY,-1,(dateadd(year,1,@Date)))), '20000101'))
    CONTINUE
    END
    ****************************
    Thanks
  4. ptimmerm New Member

    At a 10,000 foot level..... why did you chose to do this via a cursor? Cursors are notoriously inefficient and my quick review of your code did not highlight a "need" for a cursor. Rewrite this as a SELECT (you can CASE the @date, @ENDDATE stuff) and then see what the execution time is.
    Paul
  5. sijeffs New Member

    Thanks for the info, but I'm still not sure of the solution.
    How would I rewrite this as a Select?
    I have the table generated for the start and end of the policies, these are not just the start and end of each year, they could be any date in the year. I then want to keep adding one year to the start date and creating a new record until that policy gets part the end date at which point I will move on to the next policy.
    Source table
    Policy Start End
    1 15/02/2006 31/09/2008
    Destination Table
    Policy Start End
    1 15/02/2006 14/02/2007
    1 15/02/2007 14/02/2008
    1 15/02/2008 15/02/2009
    Regards.
    Simon
  6. Adriaan New Member

    Yes, but why would you want to have records to identify each year covered by an existing policy? You only need a record per-year-of-coverage if you need to record specific details per-year-of-coverage --- which you don't.
    You can simply check if a given date is between the Start and End dates of the main policy record, like this:
    SELECT * FROM policies
    WHERE StartDate <= @my_check_date AND EndDate >= @my_check_date
  7. SqlMan4u New Member

    Im suggesting a better practice. Do not use cursors whatever may be the requirement. Cursors create overheads while execution and they are usually slow. Use Correlated subqueries, WHILE loop etc instead of Cursors.
  8. scott2718281828 New Member

    If I understand correctly, you want to create records with Jan 1 and Dec 31 of all years the policy spans. You can use a table of year numbers to help.
    The [master].dbo.spt_values table is good for number sequences up to 2047, if that doesn't work for you then create your own table of year numbers. This should run in much less than two hours.
    SELECT policy_key,
    CASE WHEN d.[Year] = Y1 THEN p.POL_START_DATE ELSE DATEADD(YEAR, d.[Year]-1900,0) END AS StartDate,
    CASE WHEN d.[Year] = Y2 THEN p.POL_END_DATE ELSE DATEADD(YEAR, d.[Year]-1899,0)-1 END AS EndDate
    FROM (
    SELECT table1.policy_key, table1.POL_START_DATE , DATEADD(yy,1,POL_END_DATE) AS POL_END_DATE
    YEAR(POL_START_DATE) AS Y1, YEAR(POL_END_DATE)+1 AS Y2
    FROM table1
    INNER JOIN FACT_POLICY_SUMMARY table2
    ON table1.POLICY_KEY = table2.POLICY_KEY
    ) p
    INNER JOIN (
    SELECT number AS [Year] FROM [master].dbo.spt_values WHERE number BETWEEN 1900 AND 2047 AND [type] = 'P'
    ) d ON d.[Year] BETWEEN Y1 AND Y2
  9. sijeffs New Member

    scott2718281828 thanks for your approach, it worked a treat other than for leap years so I've had to union a second statement for any on the 29th Feb as we were losing a day.
    Thanks again.
    Simon

Share This Page