Slow Recursive CTE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Slow Recursive CTE

Slow Recursive CTE

Rate Topic

Display Mode

Topic Options

Author

Message

sonia.luthra

Posted Tuesday, February 19, 2013 8:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2013 4:19 AM
Points: 3, Visits: 17

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:
---------------------------------
HeaderKey PO_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;

Post #1421675

Eugene Elutin

Posted Tuesday, February 19, 2013 9:14 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 2,541, Visits: 4,376

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1421713

sonia.luthra

Posted Tuesday, February 19, 2013 9:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2013 4:19 AM
Points: 3, Visits: 17

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

Post #1421719

Eugene Elutin

Posted Wednesday, February 20, 2013 3:27 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:57 AM
Points: 2,541, Visits: 4,376

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1422010

sonia.luthra

Posted Wednesday, February 20, 2013 3:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2013 4:19 AM
Points: 3, Visits: 17

Thanks Indeed, This works perfectly regardless of the No. of rows in FreightContents. Smile

Good day

Post #1422012

« Prev Topic | Next Topic »

Permissions