Performance Tuning - Cursor Based Procedure

  • Hey everyone,

    I see that another SQLServerCentral member posted a cursor-based query he/she had and you made suggestions to improve the performance. I have a cursor-based procedure that I am hoping you can help with.

    The purpose of this procedure is basically to turn this:

    HsLog

    ClientNum InDate OutDate

    4 2013-11-04 16:37:10.000 2013-11-09 12:42:41.000

    4 2013-11-09 12:42:41.000 2013-11-13 02:10:22.000

    4 2013-11-13 02:10:22.000 2013-11-15 06:17:58.000

    Into this (nevermind that the ClientNums aren't the same):

    Reformatted_HsLog

    ClientNumHousingDate MinutesInFacility

    4 2013-11-04 443

    4 2013-11-05 1440

    4 2013-11-06 1440

    4 2013-11-07 1440

    4 2013-11-08 1440

    4 2013-11-09 1440

    4 2013-11-10 1440

    4 2013-11-11 1440

    4 2013-11-12 1440

    4 2013-11-13 1440

    4 2013-11-14 1440

    4 2013-11-15 377

    I need to turn the date ranges (InDate - OutDate) into individual days, tracking the number of minutes that a client

    is in one of our facilities for each day.

    I made a procedure to do this using a cursor (I know, bad practice). I couldn't think of any other way to do it.

    I gave this procedure a spin and it never finished. I stopped it at about 2 hours of run time - I may have won the

    award for longest-running procedure in my organization 🙂

    Here it is:

    ----------------------------------------------------------------------------------------------------------------------------------

    CREATE PROCEDURE Reformat_HsLog

    AS

    BEGIN --Begin procedure

    SET NOCOUNT ON; --Improve procedure performance

    /*Set up the variables & cursor*/

    DECLARE @client_num AS char(10); --For the cursor

    DECLARE @client_indate AS datetime; --For the cursor

    DECLARE @client_outdate AS datetime; --For the cursor

    DECLARE @client_cursor AS cursor; --The cursor

    DECLARE @previous_client_num AS char(10); --To keep track of the client number of previous row

    DECLARE @minutes AS int; --To tally up minutes for each date

    DECLARE @previous_outdate AS datetime; --To keep track of outdate of previous row

    SET @client_cursor = CURSOR FOR

    SELECTclientNum, InDate, OutDate

    FROMHsLog;

    OPEN @client_cursor;

    FETCH NEXT FROM @client_cursor INTO @client_num, @client_indate, @client_outdate; --The first fetch

    SET @previous_client_num = @client_num; --Start keeping track of the client number

    /*Let the fun begin!*/

    ------------------------------------------------------------------------------------------

    WHILE @@FETCH_STATUS = 0 --Gets to the end of the table

    BEGIN --Begin while loop #1

    ------------------------------------------------------------------------------------------

    WHILE (CAST(@client_indate AS date) = CAST(@client_outdate AS date)) AND

    (@client_num = @previous_client_num) --While current indate = current outdate and current client num = previous client num

    BEGIN --Begin while loop #2

    SET @minutes = @minutes + DATEDIFF(minute, @client_indate, @client_outdate); --Grab the minutes

    SET @previous_outdate = @client_outdate; --Keep track of previous outdate

    SET @previous_client_num = @client_num; --Keep track of client number of previous row

    FETCH NEXT FROM @client_cursor INTO @client_num, @client_indate, @client_outdate; --Next row

    IF CAST(@client_indate AS date) = CAST(@previous_outdate AS date) AND

    (@client_num = @previous_client_num) --If current indate = previous outdate and current client num = previous client num

    SET @minutes = @minutes + DATEDIFF(minute, @previous_outdate, @client_indate); --Grab the minutes

    ELSE

    INSERT INTO Reformatted_HsLog(ClientNum, HousingDate, MinutesInFacility) --Insert record

    VALUES(@previous_client_num, CAST(@previous_outdate AS date), @minutes);

    SET @previous_outdate = '1900-01-01 00:00:00'; --Reset variable

    SET @minutes = 0; --Reset variable

    END; --End while loop #2

    ------------------------------------------------------------------------------------------

    WHILE CAST(@client_indate AS date) <> CAST(@client_outdate AS date) --While current indate <> current outdate

    BEGIN --Begin while loop #3

    SET @minutes = @minutes + DATEDIFF(minute, @client_indate,

    DATEADD(day, 1, DATEADD(dd, DATEDIFF(dd, 0, @client_indate), 0))); --Next day at 00:00:00.000

    INSERT INTO Reformatted_HsLog(ClientNum, HousingDate, MinutesInFacility) --Insert record

    VALUES(@client_num, CAST(@client_indate AS date), @minutes);

    SET @client_indate = DATEADD(day, 1, DATEADD(dd, DATEDIFF(dd, 0, @client_indate), 0)); --Next day at 00:00:00.000

    SET @minutes = 0; --Reset variable

    END; --End while loop #3

    ------------------------------------------------------------------------------------------

    END; --End while loop #1

    ------------------------------------------------------------------------------------------

    END; --End procedure

    -----------------------------------------------------------------------------------------------------------------------------------

    Any ideas of how I can turn this into a set-based procedure, or improve it?

    Let me know if the procedure doesn't make sense and I will explain it more.

  • Hi and welcome to the forums! Kudos on wanting to get rid of this menace of code. You have a cursor and nested inside that cursor are two while loops. YIKES!!! This looks to be replaceable with a single insert statement using a tally table. What I don't quite understand though is what this is trying to do. You did a good job posting sample data but the desired output doesn't match the sample data which makes it really difficult to understand what you are trying to do.

    Please take a few minutes and read the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks for your quick response, Sean. I will try to make it more understandable.

  • Would something like this work for you?

    http://www.sqlservercentral.com/Forums/Topic1732401-3412-1.aspx#bm1732424

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Possibly. I am looking at your tally table. Thank you for sharing.

  • CKinley (11/6/2015)


    Possibly. I am looking at your tally table. Thank you for sharing.

    Feel free to ask any questions that you might have.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • This may give you some ideas. I used a placeholder of <your_tally_table> for the tally table. Replace that with your own tally table name, either a physical tally table or cte tally table. Naturally also change the column name "tally" to whatever the column name in your tally table is.

    SELECT

    ClientNum,

    DATEADD(DAY, (days.tally - 1), InDate_Midnight) AS HousingDate,

    SUM(

    CASE WHEN is_first_day = 1

    THEN CASE WHEN is_last_day = 1

    THEN DATEDIFF(MINUTE, hl.InDate, hl.OutDate)

    ELSE DATEDIFF(MINUTE, hl.InDate, DATEADD(DAY, days.tally, InDate_Midnight))

    END

    WHEN is_last_day = 1 THEN DATEDIFF(MINUTE, DATEADD(DAY, (days.tally - 1), InDate_Midnight), hl.OutDate)

    ELSE 1440 END

    ) AS MinutesInFacility

    FROM #HsLog hl

    CROSS APPLY (

    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, hl.InDate), 0) AS InDate_Midnight

    ) AS assign_alias_names1

    INNER JOIN <your_tally_table> days ON days.tally BETWEEN 1 AND DATEDIFF(DAY, hl.InDate, hl.OutDate) + 1

    CROSS APPLY (

    SELECT

    CAST(CASE WHEN days.tally = 1 THEN 1 ELSE 0 END AS bit) AS is_first_day,

    CAST(CASE WHEN DATEDIFF(DAY, DATEADD(DAY, (days.tally - 1), hl.InDate), hl.OutDate) = 0 THEN 1 ELSE 0 END AS bit) AS is_last_day

    ) AS assign_alias_names

    GROUP BY ClientNum, DATEADD(DAY, (days.tally - 1), InDate_Midnight)

    ORDER BY ClientNum, HousingDate

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply