Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow Recursive CTE Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 7:17 AM
Points: 3, Visits: 18
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
Posted Tuesday, February 19, 2013 9:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:50 AM
Points: 2,856, Visits: 5,124
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1421713
Posted Tuesday, February 19, 2013 9:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 7:17 AM
Points: 3, Visits: 18
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
Posted Wednesday, February 20, 2013 3:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:50 AM
Points: 2,856, Visits: 5,124
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1422010
Posted Wednesday, February 20, 2013 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 7:17 AM
Points: 3, Visits: 18
Thanks Indeed, This works perfectly regardless of the No. of rows in FreightContents. Good day
Post #1422012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse