February 19, 2014 at 7:02 am
Hi, Infrequent reader/first time poster here,
I would like some help determining how I should proceed:
Environment:
SQL Server 2012 sp1 (Evaluation version) using SSRS 2012, virtualized environment (VMware ESXI v5.1), currently allocated 8 GB RAM/8 cores (2 sockets/4 cores each), disk is a non-factor.
SQL Server stored proc currently returns approx. 200,000 rows. During my testing, I've stripped all parameters so it is a straight query that takes between 15-30 secs to return the rows. If I re-enable the parameters, the proc might take 40 secs (after fiddling with performance using an IN clause, I found that I'm better off dumping my multi-valued parameters to a temp table and using an inner join to filter the users' selections)
SSRS report uses a series of cascading multi-valued parameters to derive a set of 5 parameters (Cash vs. Accrual, GL Posting Date range, cost centers, cost accounts,) that are passed to the stored proc that actually retrieves the data using the temp table logic above. In theory, if a user selects all cost centers and all cost accounts, the matrix could have 3000+ columns (cost centers) and 1000+ rows (cost accounts). Because there are only 200,000 rows that are returned, there is not data for all combinations of cost centers and cost accounts but potentially, the matrix would need to render all combinations. I have found that performance is directly related to the number of columns and the number of rows that need to be rendered based on the values in the parameters selected by the user. If a user selects 10 cost centers and 50 cost accounts, the report renders quickly (less than a minute) which is ideal. If they select all cost centers and all cost accounts, it could take several minutes to render. Results from the Executionlog3 are below - the shorter render times are smaller datasets (the last column are the number of rows returned) so the first two items are returning bigger datasets than the others:
RequestTypeFormatItemActionTimeStartTimeEndTimeDataRetrievalTimeProcessingTimeRenderingSourceStatusByteCountRowCount
InteractiveRPLRender2014-02-19 04:36:46.2402014-02-19 04:40:36.92094494492134386LiversSuccess116664650143978
InteractiveRPLRender2014-02-18 09:11:26.1972014-02-18 09:20:54.423835145305423307LiversSuccess376430942200865
InteractiveRPLRender2014-02-18 09:09:24.2972014-02-18 09:09:50.0803190180354304LiversSuccess419444647221
InteractiveRPLRender2014-02-18 09:00:24.8232014-02-18 09:01:04.0036729260107823LiversSuccess422001848663
InteractiveRPLRender2014-02-18 08:38:39.7632014-02-18 08:39:25.0476467298088103LiversSuccess123877247221
Management needs to be able to review any combination of cost centers based on their criteria on a timely basis (less than a minute, in my opinion). Sooo, how do I improve the performance of the rendering of the reports in SSRS? The dataset is necessary and the ability to drill down to a report that lays out all of the selected cost centers side by side is the appeal of SSRS. However, user interest is directly related to the ability to render reports quickly. I fooled around with bumping the RAM on the virtual machine to 16 GB and it really didn't seem to make a difference. When I watched the performance indicators on Taskmgr and the Resource Monitor, the cpus are not being taxed (3% the last time I ran this report) and memory use actually declined during processing (down to less than 4 GB). I guess I'm trying to figure out whether SSRS is the appropriate tool for what I'm trying to accomplish and I'm interested in feedback ( I really don't want to have to go down a different path, but...) or maybe there is something that I have not considered that will improve performance.
Thanks.
February 19, 2014 at 10:42 am
Is the performance issue in the report or the query? I'm guessing if you just pull the query out and run it in SSMS you'll see the majority of the execution time going here. Then you can look at the query execution plan to tune the query.
HTH,
Rob
February 19, 2014 at 11:11 am
You stated “Management needs to be able to review any combination of cost centers based on their criteria on a timely basis”. You should look at something else than a SSRS report, when your requirements are that dynamic. Remember that the in-memory solutions out there only have to execute the query once for any combination of cost centers, while a report will execute the query for most combination changes.
I would try Power View, based on the limited information you gave, it could be Power Views sweet spot.
Since you use the evaluation version of SQL Server, you are probably not set on the technology, consider trying other vendors like Tableau, Spotfire or Qlikview. They all offer free trials.
February 20, 2014 at 8:04 am
Hi Rob and thanks for the reply. The query in SSMS only takes 15-20 seconds to return the data even if I select all values although I had someone tell me that 15 seconds was too long for a query execution. My feel and the results I posted seem to indicate that SSRS begins to struggle as more columns are added dynamically based on the query results. So I guess I'm really looking for some feedback as to what volume of data is reasonable for SSRS to process in a matrix style report so I can determine whether I should look for an alternative solution.
February 20, 2014 at 8:11 am
Hi lars and thanks for the reply,
I guess that is really what I'm after. Is SSRS really the correct tool to use in this arena? I appreciate the suggestions and plan to review them as either supplements or alternatives to the reports I've built in SSRS. I do have an investment of time in SSRS and not all of it would be wasted if I select an alternative tool, but before I change directions, I am interested in hearing from others such as yourself as to what realworld limitations you have discovered with SSRS...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply