Large Data Set Dynamic Pivot SQL Server 2016

Question

We have a requirement to pivot a table (has nearly 200 million rows) dynamically.

I am using dynamic SQL (because we don’t know the columns that need to be pivoted at run time) to do the pivot but understandably it is choking. It takes way longer that I would want it to. (in 26 minutes could only bring back 100,000 rows. ) I have put indexes on as well and it is better but doesn't help as much i would have liked it to.

The table contains name values pairs because the end users have a lot of attributes (nearly 350) which they use in their processing and adding all of them to a table is unwieldy.

Having name values pairs also reduces the development time in case new attributes are added.

I would appreciate if someone could get back soon.

The query is given below:

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column  
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([Name]) FROM (SELECT  [Name] FROM [Stage].[Table] group by  [Name] ) AS PivotQuery



--SELECT   @PivotColumns
--Dynamic Query
SET   @SQLQuery = 
    N'SELECT [Object_Key]
      ,[ObjectType]
      ,[SourceSystemCode]
      , ' +   @PivotColumns + '
    FROM [Stage].[Table]
    PIVOT( MAX(Value) 
          FOR [Name] IN (' + @PivotColumns + ')) AS P'

--SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery 

Thanks,

Nimish

Answer 1

Hi Nimish,

If your table “[Stage].[Table]” has many columns, you’d better modify your code as following shows to first filter the related columns as the source query for PIVOT. For detailed information, you could have a look at this blog.

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column  
SELECT   @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([Name]) FROM (SELECT  [Name] FROM [Stage].[Table] group by  [Name] ) AS PivotQuery



--SELECT   @PivotColumns
--Dynamic Query
SET   @SQLQuery = 
    N'SELECT [Object_Key]
      ,[ObjectType]
      ,[SourceSystemCode]
      , ' +   @PivotColumns + '
    FROM (select [Object_Key],[ObjectType],[SourceSystemCode],[name] from [Stage].[Table] where [name] in ('+@PivotColumns +')) s
    PIVOT( MAX(Value) 
          FOR [Name] IN (' + @PivotColumns + ')) AS P'

--SELECT   @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

Then, you could check the execution plan of your query. In your case, you could try to create an index on “[Object_Key],[ObjectType],[SourceSystemCode],[name]” to make your query be better. For further things, please refer to following blog and this similar thread.

https://blogs.msdn.microsoft.com/craigfr/2007/07/09/pivot-query-plans/

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Best Regards,

Albert Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].