May 8, 2013 at 11:40 am
Hello All,
I had a request to concatenate the various entries of a specific column into one row per server.
In other words, a server can have multiple entries of a specific column resulting in multiple rows for a single server:
Server Name MOTS Entry
Server A cat
Server B cat
Server C cat
Server C dog
Server C mouse
The user requested to have all of the MOTS Entries for Server C in one row:
Server Name MOTS Entry
Server A cat
Server B cat
Server C cat, dog, mouse
The query I wrote using a CROSS APPLY works except it takes almost 12 minutes to complete which is not good. There are over 24k rows in the result set. Hardware is not an issue because it's running on server class hardware - XEON processors (8), 16GB RAM, etc.
Is that normal for CROSS APPLY type queries to take that long to render a result set? Is there a better approach to accomplish the same results?
I've included the actual query being used for this.
Any assistance of how to improve the run time would be greatly appreciated!
Thanks in advance
Ronnie
select DISTINCT
[WITT DB ID]
,[Server Name]
,SUBSTRING(D.[MOTS Acronym],1,LEN(D.[MOTS Acronym]) -1)
,[Server Status]
,[Support Org]
,[AIA Certification]
,[App Contact]
,[Supervisor ID]
,[VP SBCUID]
,[VP Last Name]
from v_MOTS_RELATED_CONTACT_DATA_N p1
CROSS APPLY(SELECT [MOTS Acronym] + ', '
FROM v_MOTS_RELATED_CONTACT_DATA_N p2
WHERE p2.[WITT DB ID] = p1.[WITT DB ID]
ORDER BY [MOTS Acronym]
FOR XML PATH('') ) D ( [MOTS Acronym] )
ORDER BY [Server Name]
May 8, 2013 at 11:55 am
It is nearly impossible to offer much assistance because we don't have any details to work with. At the very least we would need to see actual execution plan. ddl (including indexes) and some sample data wouldn't be a bad thing either.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2013 at 11:59 am
my best guess is this will perform better, but it depends on the data;
also it looks like you are pulling from a view(v_MOTS_RELATED_CONTACT_DATA_N), which itself could be pulling in a lot of unused data/columns that might not be needed
myCTE is a placeholder for your real table name, and you might need to tweak the column names, as I removed any whitespace:
/*--Results
ServerNameMOTSEntries
Server Acat
Server Bcat
Server Ccat,dog,mouse
*/
With MyCTE (ServerName,MOTSEntry)
AS
(
SELECT 'Server A','cat' UNION ALL
SELECT 'Server B','cat' UNION ALL
SELECT 'Server C','cat' UNION ALL
SELECT 'Server C','dog' UNION ALL
SELECT 'Server C','mouse'
)
SELECT ServerName,stuff(( SELECT ',' + MOTSEntry
FROM MyCTE s2
WHERE s2.ServerName= s1.ServerName --- must match GROUP BY below
ORDER BY MOTSEntry
FOR XML PATH('')
),1,1,'') as [MOTSEntries]
FROM MyCTE s1
GROUP BY s1.ServerName --- without GROUP BY multiple rows are returned
ORDER BY s1.ServerName
Lowell
May 10, 2013 at 4:14 pm
Thank you all for your input.
I was able to resolve issue.
It wasn't a straight forward approach but it worked.
The problem appeared to be related to concatenating the result set of a view. I created a table and then ran the concatenation and my execution time went from 12 minutes to 9 seconds. Hugh difference!
Thanks again!
Ronnie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply