Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow Recursive CTE


Slow Recursive CTE

Author
Message
sonia.luthra
sonia.luthra
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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;
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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
sonia.luthra
sonia.luthra
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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
sonia.luthra
sonia.luthra
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
Thanks Indeed, This works perfectly regardless of the No. of rows in FreightContents. :-) Good day
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search