Slow Recursive CTE

  • Hi All

    I am using a CTE query for recording a list of purchase order numbers separated by a comma for a HeaderKey under one column as below:

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

    HeaderKeyPO_list

    1000215 101142-00-36, 101820-00-37, 103643-00-37, 104120-00-37, 104530-00-37, 105095-00-37, 105700-00-37, 5062 SD, 99273-00-37, FIAT SAMPLES, S.RAPSON

    1000219 102288, 104545

    1000220 104321-00-3, 104321-00-4, 104321-00-5, 105715-00-4, 105715-00-5, 105715-003

    I have run into the problem of excessive throughput time when the list of purchase orders has more than 15 entries listed in the Freightcontents table. I hope someone has a solution for this issue.

    The table definition from where I am reading the list is:

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

    CREATE TABLE [dbo].[FreightContents](

    [Description] [char](35) NOT NULL,

    [DetailKey] [char](35) NOT NULL,

    [HeaderKey] [char](35) NOT NULL,

    [ProductCode] [char](20) NOT NULL,

    [PurchaseOrderNumber] [char](30) NOT NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

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

    The data looks like below:

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

    INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3')

    INSERT INTO FreightContents VALUES('FJ#1000211','SAMPLE' )

    INSERT INTO FreightContents VALUES('FJ#1000211','Hydraulic Cylinder' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148/10182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148/101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101913' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101185' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101186' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101187' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101188' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101189' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101110' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101111' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101112' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101113' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101181' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182')

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101185' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101186' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101187' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101186' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101185' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101184' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101183' )

    The CTE definition is :

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

    SELECT HeaderKey, CAST('' AS VARCHAR(8000)), CAST('' AS VARCHAR(8000)), 0

    FROM Database1..FreightContents WITH (readuncommitted)

    GROUP BY HeaderKey

    UNION ALL

    SELECT p.HeaderKey, CAST(PO_list + CASE WHEN length = 0 THEN '' ELSE ', ' END + rtrim(PurchaseOrderNumber) AS VARCHAR(8000)),

    CAST(PurchaseOrderNumber AS VARCHAR(8000)), length + 1

    FROM CTE c INNER JOIN

    Database1..FreightContents p WITH (readuncommitted) ON c.HeaderKey = p.HeaderKey

    WHERE p.PurchaseOrderNumber > c.product_name)

    SELECT HeaderKey, PO_list

    FROM (SELECT DISTINCT substring(HeaderKey, 4, 10), PO_list, RANK() OVER (PARTITION BY HeaderKey

    ORDER BY length DESC)

    FROM CTE) D (HeaderKey, PO_list, rank)

    WHERE rank = 1;

  • One of the reason you don't have much of answers is that your posted code cannot be executed without error...

    INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3')

    ...

    results with error:

    Msg 213, Level 16, State 1, Line 1

    Column name or number of supplied values does not match table definition.

    So, which columns the above data should be inserted into?

    Also, runnable query with CTE would help more than just some cut out of it.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Thanks for that. I should have tried it myself first:

    Here goes the working syntax for the table:

    USE [BRI DW]

    GO

    /****** Object: Table [dbo].[FreightContents] Script Date: 02/19/2013 16:21:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    Create TABLE [dbo].[FreightContents](

    [HeaderKey] [char](35) NOT NULL,

    [PurchaseOrderNumber] [char](30) NOT NULL,

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    INSERT INTO FreightContents VALUES('FJ#1000210','104001-00-3')

    INSERT INTO FreightContents VALUES('FJ#1000211','SAMPLE' )

    INSERT INTO FreightContents VALUES('FJ#1000211','Hydraulic Cylinder' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148/10182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148/101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148' )

    INSERT INTO FreightContents VALUES('FJ#1000211','99148' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101913' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101185' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101186' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101187' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101188' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101189' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101110' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101111' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101112' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101113' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101181' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182')

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101914' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101185' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101186' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101182' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101187' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101186' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101185' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101184' )

    INSERT INTO FreightContents VALUES('FJ#1000211','101183' )

    Here goes the working CTE:

    USE [Database2]

    GO

    /****** Object: View [dbo].[CTE_POList_UK] Script Date: 02/19/2013 16:26:23 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE VIEW [dbo].[CTE_POList_UK] as

    WITH CTE ( HeaderKey, PO_list, product_name, length )

    AS ( SELECT HeaderKey, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0

    FROM Database1..FreightContents with(readuncommitted)

    GROUP BY HeaderKey

    UNION ALL

    SELECT p.HeaderKey, CAST( PO_list +

    CASE WHEN length = 0 THEN '' ELSE ', ' END + rtrim(PurchaseOrderNumber) AS VARCHAR(8000) ),

    CAST( PurchaseOrderNumber AS VARCHAR(8000)), length + 1

    FROM CTE c

    INNER JOIN Database1..FreightContents p with(readuncommitted)

    ON c.HeaderKey = p.HeaderKey

    WHERE p.PurchaseOrderNumber > c.product_name )

    SELECT HeaderKey, PO_list

    FROM ( SELECT Distinct substring(HeaderKey,4,10), PO_list,

    RANK() OVER ( PARTITION BY HeaderKey ORDER BY length DESC )

    FROM CTE ) D ( HeaderKey, PO_list, rank )

    WHERE rank = 1 ;

    GO

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

    Once created it shows the results upon right clicking the query in object explorer and selecting

  • I still could not run your CTE due to type mismatch error. (I guess when you do CHAR concatenation in the recursive CTE you should cast to NVARCHAR). However, looks like your solution will not perform good enough anyway.

    Try this instead:

    SELECT HeaderKey

    ,STUFF((SELECT ', ' + RTRIM(CAST(f2.PurchaseOrderNumber AS VARCHAR(30)))

    FROM FreightContents AS f2

    WHERE f2.HeaderKey = f1.HeaderKey

    ORDER BY PurchaseOrderNumber

    FOR XML PATH ('')), 1, 2, '') AS PO_list

    FROM FreightContents AS f1

    GROUP BY HeaderKey

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Thanks Indeed, This works perfectly regardless of the No. of rows in FreightContents. 🙂 Good day

Viewing 5 posts - 1 through 4 (of 4 total)

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