I have a view that quickly returns 28000 rows of data within 3 seconds. However, when I use this view to create SSRS Matrix (pivot) report, it takes almost 2 minutes to run.
More detail about the view:
- Gets data from a linked server
- Only about 10 columns with date field and amounts (Date field is what I use pivot on in SSRS to get Amount total)
What I have tried so far:
- Dumped view into a temp table
- Added OPTION (RECOMPILE);
The report is very simple. Without any parameters. This is one of those reports that users can run and do a data dump into excel before importing it into another system.
Any suggestions?
I would look into doing as much of the aggregation as you can on the server, if that's what's taking the time, especially as it sounds like a relatively static report. Give the data to SSRS in a state where it has to do as little work as possible.
If your query then takes up to two minutes to run on SQL Server, you could look into performance tuning, indexing, etc.