[SQL Server Data Warehousing] Performance issue while Pivoting huge records and creating a view

There's no doubt that pivoting that amount of data will use a lot of resources - however the question is not whether you should perform the pivot, but whether you have the hardware to support it.

Once you've started the query, you can have a look at sys.dm_exec_query_memory_grants to see how much memory has been requested - the request is based on the query plan so is only as accurate as the plan itself.  Also, put the script into a stored procedure to execute it to ensure it runs server-side, rather than using your local resources (assuming the server is more powerful of course...). 

If the query is simply going to be too resource intensive for your hardware, then you need to rethink the solution.  For example, you could try using an aggregate query rather than a pivot query, or perhaps break the pivot up into batches (based on your key column(s)) and store in a temp/staging table etc. 

How is the data currently pivoted?  Is there a need to unpivot the data in the first place?

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Performance issue while Pivoting huge records and creating a view

I have a table1 with more than 300 columns and 90 million records.  I have 40 pivoted columns in this table.  I would like to remove the pivoted column from the table1 but I would like to have the data somewhere. So I have created a table2 with the 4 columns and load the data from table1 after un pivoting the required column. Now the table2 has more than 150 million records. Now I would like to create a view by combining the table1 and table2 to get the same like the table1 which had the pivoted column previously.  Because existing applications will need those pivoted columns. I would like to create a view after pivoting the table2 data and combining it with the table1 to get a complete view which has all the columns when it was before.

I am having performance issue like out of memory exception error while pivoting data from table2.  Is it a good idea to pivot more than 150 million records and creating as a view? Please suggest do we have any other best way to solve my requirement.

Note: Table2 has composite Primary key columns

---

Anand

.

social.technet.microsoft.com/Forums