Currently, we are using a view which I created that pulls data from a number of tables. The major factor about this is getting the view to specifications involves a mix of CTEs and string concatenating to combine multiple rows into one column (multiple times).
While the view works, users have recently complained about it being too slow.
Currently, the view setup looks something like this:
;
With thing1 AS
(
SELECT DISTINCT
/* huge block of code with joins and such
that gets the individual fields and concatenates
personnel names to one row */
)
,
thing2 AS
(
SELECT TaskName,
Company,
Lease,
TaskBegin,
TaskEnd,
Field,
Personnel,
Invoice,
InvDate,
InvTot,
ROW_NUMBER() OVER (Partition by TaskName ORDER BY [TaskName] ASC) rn
FROM thing1
)
SELECT TaskName,
Company,
Lease,
Field,
ISNULL(Personnel, '') as 'Personnel',
TaskBegin,
TaskEnd,
ISNULL (STUFF( (SELECT ', ' + Invoice
FROM thing1
WHERE (thing1.TaskName = thing2.TaskName)
AND thing2.rn = 1
ORDER BY Invoice
FOR XML PATH('')), 1, 1, ''), '') AS 'Invoice',
ISNULL (STUFF( (SELECT ', ' + InvDate
FROM thing1
WHERE (thing1.TaskName = thing2.TaskName)
AND thing2.rn = 1
ORDER BY Invoice
FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceDate',
ISNULL (STUFF( (SELECT ', ' + InvTot
FROM thing1
WHERE (thing1.TaskName = thing2.TaskName)
AND thing2.rn = 1
ORDER BY Invoice
FOR XML PATH('')), 1, 1, ''), '') AS 'InvoiceTotal'
FROM thing2 WHERE rn = 1
GO
So it works as intended. Problem is, once I get to that final SELECT that concatenates the Invoice, InvDate, and InvTot, the query goes from not needing a single second (that is, a straight SELECT from thing2) to taking up to 7 seconds to run its course. (Average time is about 5 seconds.) I'm fairly confident that part of the blame lies in the ever-growing database–this view loads much faster on the test environment, which has far fewer records in it.
My question is thus: Is it possible to go about this in a way that is faster and less resource-intensive on the DB?
Best Answer
Looks like the issue in your code is multiple access to the CTEs and all the complicated logic you have in there. You should try to do everyting in a single pass with UNPIVOT/PIVOT. I did something similar here: http://spaghettidba.com/2011/10/13/concatenating-multiple-columns-across-rows/
In my example you will find the concatenation of sentences in different languages (not exactly what you're after), but it seems to me that you could use the same technique.