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

CROSS APPLY Question Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:39 PM
Points: 140, Visits: 228
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]

Post #1450714
Posted Wednesday, May 8, 2013 11:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1450725
Posted Wednesday, May 8, 2013 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
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
ServerName MOTSEntries
Server A cat
Server B cat
Server C cat,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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1450728
Posted Friday, May 10, 2013 4:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:39 PM
Points: 140, Visits: 228
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
Post #1451772
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse