Juan Velasquez
asked on
Need help optimizing a pivot view
Hello,
I have the following view that's taking 40 seconds to execute. I've been trying to speed it up and am having no lock. I am using MS SQL server 2005
SELECT p1.USI,
MAX([Review1]) AS [Review1],
MAX([Review2]) AS [Review2],
MAX([Review3]) AS [Review3],
MAX([Review4]) AS [Review4],
MAX([Review5]) AS [Review5],
MAX([Review6]) AS [Review6],
MAX([Review7]) AS [Review7],
MAX([Review8]) AS [Review8]
FROM (
SELECT USI, Review, 'Review'+CONVERT(VARCHAR(2 ),rn) ReviewPvtCol
FROM (SELECT USI, Review,
ROW_NUMBER() OVER (PARTITION BY USI ORDER BY Review) rn
FROM dbo.vwViewAllForPivot) AS SourceTable
) p
PIVOT
(
max(Review)
FOR ReviewPvtCol IN ([Review1], [Review2], [Review3], [Review4], [Review5], [Review6], [Review7], [Review8])
) AS P1
GROUP BY P1.USI
GO
I have the following view that's taking 40 seconds to execute. I've been trying to speed it up and am having no lock. I am using MS SQL server 2005
SELECT p1.USI,
MAX([Review1]) AS [Review1],
MAX([Review2]) AS [Review2],
MAX([Review3]) AS [Review3],
MAX([Review4]) AS [Review4],
MAX([Review5]) AS [Review5],
MAX([Review6]) AS [Review6],
MAX([Review7]) AS [Review7],
MAX([Review8]) AS [Review8]
FROM (
SELECT USI, Review, 'Review'+CONVERT(VARCHAR(2
FROM (SELECT USI, Review,
ROW_NUMBER() OVER (PARTITION BY USI ORDER BY Review) rn
FROM dbo.vwViewAllForPivot) AS SourceTable
) p
PIVOT
(
max(Review)
FOR ReviewPvtCol IN ([Review1], [Review2], [Review3], [Review4], [Review5], [Review6], [Review7], [Review8])
) AS P1
GROUP BY P1.USI
GO
How many rows does dbo.vwViewAllForPivot have?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.