• ChrisM@Work

    SSC Guru

    Points: 186043

    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.

    gmrose

    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

    CREATE UNIQUE CLUSTERED INDEX CIrn ON #Workings ([rn] ASC)

    -- get the results

    ;WITH Calculator AS (

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

    FROM #Workings WHERE rn = 1

    UNION ALL

    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

    OPTION( MAXRECURSION 0)

    -- 400,000 rows: 15 seconds

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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