Can this CTE based query be written more efficiently?

  • Hi there,

    I am trying to get data in the format of table ##Temp1 into the format of table ##Temp2

    IF OBJECT_ID(N'tempdb..#Temp1') IS NULL

    BEGIN

    CREATE TABLE ##Temp1 (

    pkTransactionID int,

    ComponentText varchar(8),

    DateTimeReceived datetime

    )

    END

    GO

    IF OBJECT_ID(N'tempdb..#Temp2') IS NULL

    BEGIN

    CREATE TABLE ##Temp2 (

    TransactionID int,

    TOT int,

    UniqueEnvs int,

    GBX int,

    IEX int,

    UNK int,

    DateTimeReceived datetime

    )

    END

    GO

    INSERT INTO ##Temp1 (pkTransactionID, ComponentText, DateTimeReceived)

    SELECT 14922061, 'TOT3', '20100319 08:05:08.207'

    UNION ALL

    SELECT 14922061, 'GBX1', '20100319 08:05:08.207'

    UNION ALL

    SELECT 14922061, 'IEX0', '20100319 08:05:08.207'

    UNION ALL

    SELECT 14922061, 'UNK0', '20100319 08:05:08.207'

    UNION ALL

    SELECT 14922392, 'TOT446', '20100319 08:09:17.830'

    UNION ALL

    SELECT 14922392, 'GBX298', '20100319 08:09:17.830'

    UNION ALL

    SELECT 14922392, 'IEX24', '20100319 08:09:17.830'

    UNION ALL

    SELECT 14922392, 'UNK0', '20100319 08:09:17.830'

    INSERT INTO ##Temp2 (TransactionID, TOT, UniqueEnvs, GBX, IEX, UNK, DateTimeReceived)

    SELECT 14922061, 3, 1, 1, 0, 0, '20100319 08:05:08.207'

    UNION ALL

    SELECT 14922392, 446, 322, 298, 24, 0, '20100319 08:09:17.830'

    I have managed it with the query below but I am guessing there is probably a more efficient way to do this.

    WITH PrintRunSummary

    AS (

    SELECT pkTransactionID,

    CASE

    WHEN SUBSTRING(ComponentText,1,3) = 'TOT'

    THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)

    ELSE 0

    END AS [TOT],

    CASE

    WHEN SUBSTRING(ComponentText,1,3) = 'GBX'

    THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)

    ELSE 0

    END AS [GBX],

    CASE

    WHEN SUBSTRING(ComponentText,1,3) = 'IEX'

    THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)

    ELSE 0

    END AS [IEX],

    CASE

    WHEN SUBSTRING(ComponentText,1,3) = 'UNK'

    THEN CAST(SUBSTRING(ComponentText,4,LEN(ComponentText)) AS int)

    ELSE 0

    END AS [UNK],

    DateTimeReceived

    FROM ##Temp1

    )

    SELECT pkTransactionID TransactionID,

    SUM(TOT) TOT,

    SUM(GBX) + SUM(IEX) + SUM(UNK) UniqueEnvs,

    SUM(GBX) GBX,

    SUM(IEX) IEX,

    SUM(UNK) UNK,

    MAX(DateTimeReceived) DateTimeReceived

    FROM PrintRunSummary

    GROUP BY pkTransactionID

    As you can see, the TOT, GBX, IEX and UNK values are simply the integers following the prefixes from ##Temp1. UniqueEnvs is the sum of GBX, IEX and UNK values.

    If anyone can show me a more optimal approach, I'd be really appreciative.

    Regards,

    Paul.

    P.S. The real tables this example comes from are optimally indexed.

  • You can use a PIVOT or a Cross-Tab. But only testing will tell which is the better option amongst all.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thanks for your response.

    Sorry for sounding like a simpleton but do you have an example of how I would use PIVOT to format the query? PIVOT and UNPIVOT are next on my list of things to learn after I get to grips with CTEs so, currently, I couldn't pivot my way out of a paper bag I'm afraid.

    Regards,

    Paul.

  • SELECTpkTransactionID, [TOT],

    ISNULL( [TOT], 0 ) + ISNULL( [GBX], 0 ) + ISNULL( [IEX], 0 ) + ISNULL( [UNK], 0 ) UniqueEnvS,

    [GBX], [IEX], [UNK]

    FROM(

    SELECTpkTransactionID, LEFT( ComponentText, 3 ) ColName,

    CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue, DateTimeReceived

    FROM##Temp1

    ) T

    PIVOT( SUM( ColValue ) FOR ColName IN ([TOT],[GBX],[IEX],[UNK]) ) P

    This is the Pivot method.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (3/25/2010)


    SELECTpkTransactionID, [TOT],

    ISNULL( [TOT], 0 ) + ISNULL( [GBX], 0 ) + ISNULL( [IEX], 0 ) + ISNULL( [UNK], 0 ) UniqueEnvS,

    [GBX], [IEX], [UNK], DateTimeReceived FROM(

    SELECTpkTransactionID, LEFT( ComponentText, 3 ) ColName,

    CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue, DateTimeReceived

    FROM##Temp1

    ) T

    PIVOT( SUM( ColValue ) FOR ColName IN ([TOT],[GBX],[IEX],[UNK]) ) P

    This is the Pivot method.

    Nice Query,

    DateTimeReceived was missing in select list

    Vaibhav K Tiwari
    To walk fast walk alone
    To walk far walk together

  • Oh, I didn't see that.

    SELECTP.pkTransactionID, [TOT],

    ISNULL( [TOT], 0 ) + ISNULL( [GBX], 0 ) + ISNULL( [IEX], 0 ) + ISNULL( [UNK], 0 ) UniqueEnvS,

    [GBX], [IEX], [UNK], MAX(DateTimeReceived) DateTimeReceived

    FROM(

    SELECTpkTransactionID, LEFT( ComponentText, 3 ) ColName,

    CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue

    FROM#Temp1

    ) T

    PIVOT( SUM( ColValue ) FOR ColName IN ([TOT],[GBX],[IEX],[UNK]) ) P

    INNER JOIN #Temp1 T1 ON P.pkTransactionID = T1.pkTransactionID

    GROUP BY P.pkTransactionID, [TOT], [GBX], [IEX], [UNK]

    And the below i think would be a better method called the Cross Tabs and is similar to your initial method. This is better than PIVOT for bigger tables.

    SELECTT.pkTransactionID,

    SUM( CASE WHEN ColName = 'TOT' THEN ColValue ELSE 0 END ) TOT,

    SUM( ColValue ) UniqueEnvS,

    SUM( CASE WHEN ColName = 'GBX' THEN ColValue ELSE 0 END ) GBX,

    SUM( CASE WHEN ColName = 'IEX' THEN ColValue ELSE 0 END ) IEX,

    SUM( CASE WHEN ColName = 'UNK' THEN ColValue ELSE 0 END ) UNK,

    MAX( DateTimeReceived ) DateTimeReceived

    FROM(

    SELECTpkTransactionID, LEFT( ComponentText, 3 ) ColName,

    CONVERT( INT, REPLACE( ComponentText, LEFT( ComponentText, 3 ), '' ) ) ColValue,

    DateTimeReceived

    FROM#Temp1

    ) T

    GROUP BY T.pkTransactionID


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • try this select TransactionID,

    sum(TOT) as TOT,

    sum(UniqueEnvs) as UniqueEnvs,

    sum(GBX) as GBX,

    sum(IEX) as IEX,

    sum(UNK) as UNX,

    DateTimeReceived

    from ##temp2

    group by TransactionID,

    DateTimeReceived

    but bad thing is i didnt use ##temp1 table

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • after seeing Execution plan, i should say CTE query working better the PIVOT one.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (3/25/2010)


    after seeing Execution plan, i should say CTE query working better the PIVOT one.

    Yes. And it is also faster than the Cross Tab. But as i said it may change depending on the load. So testing with the actual data will be better.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Bhuvnesh (3/25/2010)


    try this select TransactionID,

    sum(TOT) as TOT,

    sum(UniqueEnvs) as UniqueEnvs,

    sum(GBX) as GBX,

    sum(IEX) as IEX,

    sum(UNK) as UNX,

    DateTimeReceived

    from ##temp2

    group by TransactionID,

    DateTimeReceived

    but bad thing is i didnt use ##temp1 table

    I am trying to get data in the format of table ##Temp1 into the format of table ##Temp2

    see the starting post

    temp2 is already formatted and he is trying to get the data from temp1 in the format of temp2.

    Vaibhav K Tiwari
    To walk fast walk alone
    To walk far walk together

  • Actually, the original query is a cross-tab. It just uses a CTE instead of a subquery. The Case statements in the CTE convert the rows into columns, and then the query which references the CTE does the GROUP BY and totals. There is nothing at all wrong with that approach ... it will perform just fine. Look at the execution plans and see.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Hi there,

    ^__^ I hope this helps

    DECLARE @tbl TABLE (TransactionID INT,Code CHAR(3),Num INT, DateTimeReceived DATETIME)

    INSERT INTO @tbl

    SELECTpkTransactionID

    --,ComponentText

    ,LEFT(ComponentText,3)

    ,RIGHT(ComponentText,LEN(ComponentText)-3)

    ,DateTimeReceived

    FROM ##Temp1

    SELECTDISTINCT

    tot.TransactionID

    ,tot.NumAS 'TOT'

    ,UniqueEnvs = gbx.Num + iex.Num + unk.Num

    ,gbx.NumAS 'GBX'

    ,iex.NumAS 'IEX'

    ,unk.NumAS 'UNK'

    ,tot.DateTimeReceived

    FROM @tbl tot

    INNER JOIN @tbl gbx ON (tot.TransactionID=gbx.TransactionID AND gbx.Code='GBX')

    INNER JOIN @tbl iex ON (tot.TransactionID=iex.TransactionID AND iex.Code='IEX')

    INNER JOIN @tbl unk ON (tot.TransactionID=unk.TransactionID AND unk.Code='UNK')

    WHERE tot.Code='TOT'

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • I really appreciate all of your input guys.

    Many of your queries generate identical query plans to my original at small numbers of rows. I shall see if this is the same at a much higher table size and go from there.

    Many thanks to you all, I've learned quite a bit from this post. 🙂

    Paul.

Viewing 13 posts - 1 through 12 (of 12 total)

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