SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow running query


Slow running query

Author
Message
eseosaoregie
eseosaoregie
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1202
I am currently working on project where we are extracting data from some AX tables. The data from these extracts is transformed in vary ways to be consumed by various applications. One of the of the transformations requires many column values to be concatenated into one column values. The query that is currently used to do this was written by a contractor who has now left. It uses the PATH mode FOR XML Path to concatenate values. The query currently takes 24 minutes to run. We are currently in development but ideally in production this data needs to be extracted every 7 minutes. I would just like to know if there is a way I can tune the query or rewrite it in another way that could speed up the retrieval. The source tables contain about 4- 5 million records.

The query is shown below


SELECT i.ITEMID AS CRItemID, i.PSTPRODUCTID AS CRProductID, i.NAMEALIAS AS CRName, i.CREATIONPERFORMER AS CRPerformer,
i.CREATIONDURATION AS CRDuration, i.CREATIONSTATUS AS CRStatus, i.[MODIFIEDDATETIME] AS CRModifiedDateTime,
STUFF
((SELECT ' ; ' + n.CRNUMBERTYPEID + '|#|' + n.CRNUMBER + CASE n.CRSOCIETYCODE WHEN '0' THEN '' ELSE '|#|' + n.CRSOCIETYCODE END
FROM CRALTNUMBERS n
WHERE n.CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRNumbers,
STUFF
((SELECT ' ; ' + t .CRTITLE
FROM CRALTTITLES t
WHERE t .CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRTitles,
STUFF
((SELECT ' ; ' + LTRIM(m.FIRSTNAME + ' ' + m.NAME)
FROM MATCHNAME m
WHERE m.CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRNames,
STUFF
((SELECT ' ; ' + b.BOMID
FROM BOM b
WHERE b.ITEMID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRParents,
STUFF
((SELECT ' ; ' + b.ITEMID
FROM BOM b
WHERE b.BOMID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRChildren,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Episode title' FOR XML PATH('')), 1, 3, '') AS CREpisodeTitle,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial brand' FOR XML PATH('')), 1, 3, '') AS CRComBrand,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial product' FOR XML PATH('')), 1, 3, '') AS CRComProduct,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial type' FOR XML PATH('')), 1, 3, '') AS CRComType,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Country of origin' FOR XML PATH('')), 1, 3, '') AS CRCountry,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Year of production' FOR XML PATH('')), 1, 3, '') AS CRYear
FROM INVENTTABLE i




I have also attached a copy of the execution plan of the latest query run. Any help would be much appreciated.
Attachments
ExecuteXMLPlan.sqlplan (16 views, 149.00 KB)
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5421 Visits: 35319
I would start with looking at what indexes exist on tables
[CRALTNUMBERS]
[MATCHNAME]
[BOM]
[CRALTTITLES]




Table Column Operation Index Actual Rows Est Rows diff
[BOM] BOMID Index Spool (Eager Spool) 311,697 90,394,960 90,083,263
[CRALTNUMBERS] CREATIONID Index Spool (Eager Spool) 147,989 40,412,558 40,264,569
[MATCHNAME] CREATIONID Index Spool (Eager Spool) 811,516 39,993,036 39,181,520
[BOM] ITEMID Index Spool (Eager Spool) 185,470 19,798,585 19,613,115
[CRALTTITLES] CREATIONID Index Spool (Eager Spool) 155,497 1,639,801 1,484,304




________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12303 Visits: 8546
The way you have done the SELECT (SELECT...), (SELECT..) ... FROM forces those massive nested loop joins on 900K rows and the logical IO from those is stunningly high.

First, make sure you have a covering index on each of the correlated subqueries. But even that may not help you with all those 900K iterations.

If it isn't fast enough, I would switch to a CLR object to do the concatenation. That will probably be the most efficient. It also avoids not one but TWO scenarios currently where you can get the WRONG OUTPUT!! A) you have no order by in the correlated subqueries meaning they can put the concatenation in any order on output and B) XML "special characters" can actually BREAK the FOR XML processing (think <, >, etc).

You could even try a cursor-based solution to build the output of each of those correlated subqueries.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
eseosaoregie
eseosaoregie
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1202
Hi guys,

thanks for the responses. I will try that alternative approach and let you know what my results are. CLR came up when I was discussing this with a colleague of mine. I will also check this out and let you know what my findings are.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12303 Visits: 8546
eseosaoregie (4/13/2013)
Hi guys,

thanks for the responses. I will try that alternative approach and let you know what my results are. CLR came up when I was discussing this with a colleague of mine. I will also check this out and let you know what my findings are.


I think Adam Machanic has done some blogging on the CLR side of things that could be useful.

You could also see here for some (mostly bad) options for string concat: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

And this post shows you why the COALESCE/ISNULL method isn't viable (another ordering issue just like the FOR XML problem): http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85224 Visits: 41078
eseosaoregie (4/13/2013)
I am currently working on project where we are extracting data from some AX tables. The data from these extracts is transformed in vary ways to be consumed by various applications. One of the of the transformations requires many column values to be concatenated into one column values. The query that is currently used to do this was written by a contractor who has now left. It uses the PATH mode FOR XML Path to concatenate values. The query currently takes 24 minutes to run. We are currently in development but ideally in production this data needs to be extracted every 7 minutes. I would just like to know if there is a way I can tune the query or rewrite it in another way that could speed up the retrieval. The source tables contain about 4- 5 million records.

The query is shown below


SELECT i.ITEMID AS CRItemID, i.PSTPRODUCTID AS CRProductID, i.NAMEALIAS AS CRName, i.CREATIONPERFORMER AS CRPerformer,
i.CREATIONDURATION AS CRDuration, i.CREATIONSTATUS AS CRStatus, i.[MODIFIEDDATETIME] AS CRModifiedDateTime,
STUFF
((SELECT ' ; ' + n.CRNUMBERTYPEID + '|#|' + n.CRNUMBER + CASE n.CRSOCIETYCODE WHEN '0' THEN '' ELSE '|#|' + n.CRSOCIETYCODE END
FROM CRALTNUMBERS n
WHERE n.CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRNumbers,
STUFF
((SELECT ' ; ' + t .CRTITLE
FROM CRALTTITLES t
WHERE t .CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRTitles,
STUFF
((SELECT ' ; ' + LTRIM(m.FIRSTNAME + ' ' + m.NAME)
FROM MATCHNAME m
WHERE m.CREATIONID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRNames,
STUFF
((SELECT ' ; ' + b.BOMID
FROM BOM b
WHERE b.ITEMID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRParents,
STUFF
((SELECT ' ; ' + b.ITEMID
FROM BOM b
WHERE b.BOMID = I.Itemid FOR XML PATH('')), 1, 3, '') AS CRChildren,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Episode title' FOR XML PATH('')), 1, 3, '') AS CREpisodeTitle,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial brand' FOR XML PATH('')), 1, 3, '') AS CRComBrand,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial product' FOR XML PATH('')), 1, 3, '') AS CRComProduct,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Commercial type' FOR XML PATH('')), 1, 3, '') AS CRComType,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Country of origin' FOR XML PATH('')), 1, 3, '') AS CRCountry,
STUFF
((SELECT ' ; ' + p.DATA
FROM PSTPRODTMPLDATA p
WHERE p.TEMPLATEID = I.Itemid AND p.SPECID = 'Year of production' FOR XML PATH('')), 1, 3, '') AS CRYear
FROM INVENTTABLE i




I have also attached a copy of the execution plan of the latest query run. Any help would be much appreciated.



The major problem with that query is that it recalculates the colon separate list for like rows which is a huge waste of resources. Using "Divide'n'Conquer" methods, a separate table should be calculated to hold single instances of each concatenation grouped by I.ItemID and p.SPECID using a single query with a GROUP BY to build such a thing.

The same is also true of the other lookup tables (like the BOM table). The same information is concatenated over and over and over for each row.

The key to performance on this problem will be to correctly "pre-aggregate" the concatenations in separate tables and then join to those tables.

To wit, even the introduction of a CLR to do the concatenation might not be as performant as it could be because it would still have to do the concatentation of identical data using the current structure of the current query.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12303 Visits: 8546
Good points Jeff. I would definitely look at pre-populating the concatenated objects as temp tables. I had that in my notes I took while looking at alternatives for this and just missed putting it in the reply! Hehe

I looked at prepopulating those temp objects tho and I think in order to get the table key and the concatenated string is a double hit on the table for the methods I checked (except for cursor, which has is it's own issues obviously). SQL CLR into a temp object could still be best.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85224 Visits: 41078
Here's an example of what I'm talking about. Of course, I don't have access to the data so the code is completely untested but this will calculate the delimited aggregations just once for each TemplateID/SpecID combination in the PstProdTmplData table instead of 6 identical recalculations for each and every row of the InventTable.

While the following looks like a lot of code, it will allow your code to run with comparatively blazing performance as to the way it is currently structured. It's a very common method (pre-aggregate and pivot using a Cross Tab) to solve the problem of using an EAV table effectively.

WITH
ctePreAgg AS
(
--===== Preaggregate the semi-colon delimited "Data" for each TemplateID/SpecID combination
-- for performance. We'll pivot the data later
SELECT p1.TemplateID
, p1.SpecID --To be used as a join filter in another query.
, SpecIDs =
STUFF(
(
SELECT ' ; ' + p2.Data
FROM dbo.PstProdTmplData p2
WHERE p2.TemplateID = p1.TemplateID
FOR XML PATH('')
)
,1,3,'')
FROM dbo.PstProdTmplData p1
WHERE p1.SpecID IN
(
'Episode title'
,'Commercial brand'
,'Commercial product'
,'Commercial type'
,'Country of origin'
,'Year of production'
)
AND p.TemplateID IN (SELECT ItemID FROM dbo.InventTable) --Implicitly DISTINCT and as fast as a join.
GROUP BY p1.TemplateID, p1.SpecID
) --=== Now, pivot the data so that it's normalized instead of being an EAV-style result set and store it
-- all in a temporary lookup table for easy and very high performance joining in the final query.
SELECT TemplateID = ISNULL(TemplateID,0) --Makes the column in the lookup table NOT NULL.
, CREpisodeTitle = MAX(CASE WHEN SpecID = 'Episode title' THEN SpecIDs ELSE '' END)
, CRComBrand] = MAX(CASE WHEN SpecID = 'Commercial brand' THEN SpecIDs ELSE '' END)
, CRComProduct = MAX(CASE WHEN SpecID = 'Commercial product' THEN SpecIDs ELSE '' END)
, CRComType = MAX(CASE WHEN SpecID = 'Commercial type' THEN SpecIDs ELSE '' END)
, CRCountry = MAX(CASE WHEN SpecID = 'Country of origin' THEN SpecIDs ELSE '' END)
, CRYear = MAX(CASE WHEN SpecID = 'Year of production' THEN SpecIDs ELSE '' END)
INTO #Lookup_PstProdTmplData
FROM ctePreAgg
GROUP BY TemplateID
;
--===== Add a Primary Key for extra join performance. We let the system name the PK on Temp Tables
-- because such constraints must be uniquely named in the database and we don't want to destroy
-- the ability of more than one instance of the code to run conncurrently.
ALTER TABLE #Lookup_PstProdTmplData
ADD PRIMARY KEY CLUSTERED (TemplateID)
;




Once that's inplace, the final query becomes a blazing-performance cake walk. (Note that I didn't pre-aggregate/pivot all the tables that should be. You have to have some of the fun! :-P )


SELECT i.ITEMID AS CRItemID, i.PSTPRODUCTID AS CRProductID, i.NAMEALIAS AS CRName, i.CREATIONPERFORMER AS CRPerformer,
i.CREATIONDURATION AS CRDuration, i.CREATIONSTATUS AS CRStatus, i.[MODIFIEDDATETIME] AS CRModifiedDateTime,
p.CREpisodeTitle, p.CRComBrand, p.CRComProduct, p.CRComType, p.CRCountry, p.CRYear
FROM dbo.InventTable i
JOIN #Lookup_PstProdTmplData p
ON p.TemplateID = i.ItemID



As a bit of a sidebar, this isn't Oracle and we don't have the 30 character object name limitation. Consider NOT using abbreviations for table names in the future as they serve only to daze and confuse the uninitiated. For example, whoever designed these tables used the name "PstProdTmplData" for a table that should have been named "PostProductionTemplate". It's only 7 characters longer and there's no chance of someone misreading the "Tmp" in the original abbreviated name as meaning "Temporary" especially if the miss the "l" in the name.

Also, we all know tables have data in them so the word "Data" in the table name is a bit superfluous.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
eseosaoregie
eseosaoregie
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 1202
Hi Jeff, thanks for your reply. I will give this a go when i am back at the office. Just one question for future reference. What is an EAV table?
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39061 Visits: 38518
eseosaoregie (4/13/2013)
Hi Jeff, thanks for your reply. I will give this a go when i am back at the office. Just one question for future reference. What is an EAV table?


Entity-Attribute-Value hence EAV.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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