Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS 2012 performance/unperformance Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:37 PM
Points: 3, Visits: 29
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:

RequestType Format ItemAction TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
Interactive RPL Render 2014-02-19 04:36:46.240 2014-02-19 04:40:36.920 944 94492 134386 Live rsSuccess 116664650 143978
Interactive RPL Render 2014-02-18 09:11:26.197 2014-02-18 09:20:54.423 835 145305 423307 Live rsSuccess 376430942 200865
Interactive RPL Render 2014-02-18 09:09:24.297 2014-02-18 09:09:50.080 3190 18035 4304 Live rsSuccess 4194446 47221
Interactive RPL Render 2014-02-18 09:00:24.823 2014-02-18 09:01:04.003 6729 26010 7823 Live rsSuccess 4220018 48663
Interactive RPL Render 2014-02-18 08:38:39.763 2014-02-18 08:39:25.047 6467 29808 8103 Live rsSuccess 1238772 47221

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.

Post #1542980
Posted Wednesday, February 19, 2014 10:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 1,184, Visits: 1,218
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
Post #1543134
Posted Wednesday, February 19, 2014 11:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:40 AM
Points: 64, Visits: 676
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.
Post #1543142
Posted Thursday, February 20, 2014 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:37 PM
Points: 3, Visits: 29
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.
Post #1543518
Posted Thursday, February 20, 2014 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:37 PM
Points: 3, Visits: 29
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...
Post #1543526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse