CROSS APPLY Question

  • 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]

  • 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/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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