• gmrose (10/21/2010)

    For timing, yesterday's script ran for over 23 hours and was less than 5% complete when I halted it. Today's script succussfully completed in less than 4 minutes. That's what I call a real improvement.


    Heh Jeff's gonna hate me for this but I couldn't resist it.

    400,000 rows in 15 seconds:

    IF OBJECT_ID('tempdb..#tempstore') IS NOT NULL DROP TABLE #tempstore;

    CREATE TABLE #tempstore (acct_nbr INT,

    ckid INT,

    stat varchar(10),

    Grp int);

    INSERT INTO #tempstore (acct_nbr, ckid, stat)

    SELECT 1, 101 ,'Open' UNION ALL

    SELECT 1, 102 ,'Open' UNION ALL

    SELECT 1, 103 ,'Open' UNION ALL

    SELECT 1, 104 ,'Void' UNION ALL

    SELECT 1, 105 ,'Void' UNION ALL

    SELECT 1, 106 ,'Open' UNION ALL

    SELECT 1, 107 ,'Open' UNION ALL

    SELECT 1, 108 ,'Open' UNION ALL

    SELECT 1, 109 ,'Void' UNION ALL

    SELECT 1, 110 ,'Open' UNION ALL

    SELECT 2, 101 ,'Open' UNION ALL

    SELECT 2, 102 ,'Open' UNION ALL

    SELECT 2, 103 ,'Open' UNION ALL

    SELECT 2, 104 ,'Void' UNION ALL

    SELECT 3, 105 ,'Void' UNION ALL

    SELECT 3, 106 ,'Open' UNION ALL

    SELECT 3, 107 ,'Open' UNION ALL

    SELECT 4, 108 ,'Open' UNION ALL

    SELECT 4, 109 ,'Void' UNION ALL

    SELECT 4, 110 ,'Open';

    -- 400,000 rows of data

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;

    CREATE TABLE #temp (acct_nbr INT,

    ckid INT,

    stat varchar(10),

    Grp int);

    INSERT INTO #temp (acct_nbr, ckid, stat)

    SELECT acct_nbr+(4*n), ckid+(20*n), stat

    FROM #tempstore

    CROSS JOIN (SELECT TOP 20000 [n] = ROW_NUMBER() OVER(ORDER BY a.[name])-1 FROM master.dbo.syscolumns a, master.dbo.syscolumns b) n

    -- copy our data to a working table

    SELECT rn = ROW_NUMBER() OVER (ORDER BY acct_nbr, ckid),

    acct_nbr, ckid, stat

    INTO #Workings

    FROM #temp

    ORDER BY acct_nbr, ckid


    -- get the results

    ;WITH Calculator AS (

    SELECT rn, acct_nbr, ckid, stat, NewGroup = CAST(1 AS INT)

    FROM #Workings WHERE rn = 1


    SELECT w.rn, w.acct_nbr, w.ckid, w.stat,

    NewGroup = CASE

    WHEN w.acct_nbr <> c.acct_nbr THEN 1

    WHEN w.stat <> c.stat THEN c.NewGroup + 1

    ELSE c.NewGroup END

    FROM #Workings w

    INNER JOIN Calculator c ON c.rn+1 = w.rn)

    SELECT acct_nbr, ckid, stat, NewGroup

    FROM Calculator


    -- 400,000 rows: 15 seconds

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden