November 24, 2010 at 6:47 am
With some reports that I have written for SSRS 2008 R2, the performance is problematically slow when the number of rows in the table control gets over a few hundred.
I am curious as to whether this is a problem with SSRS, or something to do with my setup. What sort of performance do any of you forum readers get? Here is a test report that I have been using.
- Create a blank report.
- Create a dataset that executes this query (it simply returns 1000 rows of dummy data)
[font="Courier New"] declare @d table (id int, Result varchar(50))
declare @i int
set @i = 1000
while @i > 0
begin
insert @d values (@i, 'Data')
set @i = @i - 1
end
select Id, Result from @d
[/font]
- Add a Table control
- Add the ID and Result fields to the table detail fields
- Set the "Keep together on one page if possible" option in Tablix Properties / General / Page break options
- Deploy the report to the report server
Now, when you view the report with Internet Explorer, how long does it take to render and display the report? For me, it takes 20 to 30 seconds, which is terribly slow for 1000 rows.
When you view the report with Google Chrome, how long does it take? For me, it takes about 2 seconds, which is an enormous increase over IE.
December 6, 2010 at 11:53 am
December 7, 2010 at 10:20 am
I am still getting terrible performance. I've measured the report execution times for both Chrome and IE with varying number of rows. Please see the results below.
(The SQLServerCentral forum does not appear to keep the spaces in the table below intact, so unfortunately it looks messy and compressed)
Duration (seconds)
RowsIEChrome
121
221
421
821
1631
3231
6451
12891
256181
512331
1024672
20481282
4096n/a4
8192n/a9
16384n/a18
32768n/a36
65536n/a75
As you can see, IE's performance is abysmal. The duration for IE beyond 2048 rows is not listed because it would over 2 minutes and thus unusable.
December 8, 2010 at 7:14 am
I don't know if it's a problem with IE specifically, I'll have to go test now, but we've had some issues with reports running ridiculously slow once deployed, when they run fine in BIDS. To fix that, the common workaround seems to be (after googly-moogling ;-))to pass the parameters into your sp, then immediately store into local variables, and use the local variables to retrieve the data.
Not sure why that works, sounds sorta like parameter sniffing problems, but you're still using a variable, so no idea how that would fix the problem.
hope that helps,
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 8, 2010 at 8:26 am
Thanks for your input.
It is definitely not an issue with the speed of execution of the SQL code. I know this because:
1) The report is not executing a stored procedure. it is executing the T-SQL as shown in my first post.
2) When I use SQL Profiler, the T-SQL always executes in less than a second.
3) Google Chrome is able to view the same report in 2 or 3 seconds.
December 8, 2010 at 11:14 am
Our system is using the MS ReportViewer.Webforms object to populate an ASP response page with the report contents. There is great variation in the javascript processing time between the various versions of IE. When we used the 2005 version of the reportviewer object. a report would render in about 20 seconds. When we switched to the 2010 version of the report viewer, IE8 rendering times jumped to 75 seconds. This is all using the same version of Windows and SQL Server. (2008r2 for both) I discovered that our system was causing IE8 to force IE5 emulation mode when rendering the report. Using IE7 emulation mode reduced the rendering time to 40 seconds, IE8 standards mode reduced this down to 30 seconds. These two pages helped me to sort out what was happening.
http://msdn.microsoft.com/en-us/library/cc288325(v=VS.85).aspx
http://msdn.microsoft.com/en-us/library/dd565628(VS.85).aspx#browsermodes
March 2, 2011 at 5:46 pm
EITCL,
I am having the same problem with one of my larger reports on SSRS 2008 R2, but when I work through your example I am not seeing the expected performance lag. Therefore here are my questions:
What version of the BID client tools were used to develop the example report? (Reason: When working with MSFT in the past, I believe they mentioned something about an issue with the keep together option and converting SQL 2005 RDLs to SQL 2008 R2. The bad conversion could be causing your IE browser to eliminate IE 5 browser.)
Have you applied any CU patches to your SSRS server? (Reason: I think CU3 has a fix a parameter bug KB#2276203.)
Can you post the RDL file?
What version of SQL is your data source connecting using? (The TSQL has to run on some platform.)
March 3, 2011 at 3:06 pm
We had the same problem with slow spped when rendering SSRS 2008 R2 reporst on IE8, particularly reports with parameters. MS do not have a fix and we are now rendering our reports using Firefox which has resolved the speed issue. One drawback with Firefox is that the back arrow does not return a sub report to the previous page.
March 8, 2011 at 7:24 am
Hi,
Thanks for your input. The version of BIDS reported when I go to Help About is:
Microsoft Visual Studio 2008
Version 9.0.30729.4462 QFE
Microsoft .NET Framework
Version 3.5 SP1
The report RDL was developed on this version of BIDS, it hasn't been converted.
I've attached the RDL file of the report (saved as a .TXT file).
No CU patches have been applied to out server.
The SSRS is running an SQL 2008 R2 and the report is pointing to the same server.
Cheers
April 7, 2011 at 4:15 am
Hi
I am having an issue with rendering a report in Internet Explorer. When the report is displayed in Print Layout in Visual Studio it displays about 500 pages which works fine, but in the Report Viewer in Internet Explorer the report defaults to the standard layout which hangs because it is trying to render all the pages into one. Is there a way around this?
Thanks in advance
Gideon
April 12, 2011 at 2:30 pm
Hi- Your problem is that on your report there is a setting that says "Keep together" if possible on your table and/or page. Uncheck that option and it will render to multiple pages. Also check for that setting at the group break level. -DG
February 7, 2013 at 10:45 am
Refer to this article on Parameter Sniffing. I encountered the same problem and utilized Option 2 and the problem was resolved. 🙂
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
December 18, 2014 at 11:01 am
This actually fixed the problem for me. By looking at the server logs, I was able to determine that the problem wasn't SSRS but the client side. Following your suggestion, the report started to work right away.
Thank you so much for posting this.
Rick
December 19, 2014 at 6:49 pm
I think this is related to why I get faster results in SSMS when I export to text rather than to grid. The rendering engine plays a part in the whole data displaying assembly process.
----------------------------------------------------
February 3, 2015 at 1:20 pm
Could you please tell me what solution you used to fix the issue?
Thannks
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply