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
No comments:
Post a Comment