I have a PIVOT query that executes quite slowly. Looking at the execution plan I can see that the SORT operand is costing 96% of the execution. Is there a way to rewrite the query to remove this sort and/or reduce its cost to speed up the execution?
@Oleg thanks. Sorry, ReportingName was a mistake, it should be ReportName. The data does return correctly however it is very slow. The query maybe badly written but the SORT is taking up the great majority of the cost of execution.
The Survey table contains the following rows and the views are below:
Perhaps I should clarify the necessity of pivoting data bound to be used as the source for SSRS report because all reporting tools do a pretty good job pivoting data on their end (placing the column on Columns area usually does the trick). Instead of trying to understand the reasons behind the necessity of pivoting data, I will instead try to restate the query in question in order to make it faster. From what I see, it looks like the reason for slowing it down is due to the fact that too much (too wide to be exact) info is used for pivoting whereas in reality only few columns need to participate, and the fluff may be added later. Here is the script (to the best of my ability to just type without any actual data available on my end):
This way, the pivot involves nothing but the Survey table so the sort operation will be cheaper. Once the pivot is done, the results are then joined with others for details. I still retained the joins to the views instead of unwinding them to the actually needed tables. The optimizer is usually quite capable of expanding the guts of the view definitions into the plan. The only possible problem may be caused by the LEFT join present in the views when it may not have been needed, depending on data quality.
Hope this helps.
answered Dec 01, 2017 at 07:10 PM