When I run a report thru ReportViewer 2010 with @ 8,000 records, two levels of grouping, a detail group and some filtering, one tablix and one chart it sends w3wp.exe cpu utilisation to 100% for @ 80-90 seconds. The same report runs in @ 8 seconds thru Report Builder. Is anyone else hitting this problem?

Hi, You may be running into the same or a similar problem that I have. This only occurs on reports that are run in local mode and that do not have a static grouping expression. Both the syntax Fields("MyFieldName").Value and Fields(Parameters!MyParamName.Value).Value are considered dynamic. Each grouping expression that uses such an expression causes about 15 seconds of delay in rendering per grouping parameter when in a WebForm Viewer running in local mode. In a WinForm Viewer it causes less delay but still about 8 seconds per dynamic grouping parameter. Here I also have a time factor of about 8 to 1, so that could fit your situation. If that is also your case - IMHO this is a bug. See my thread - http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/6d89e2ce-3528-465f-9740-7e22aa7b7aae Alle

Not sure this is exactly the same problem but may be related to a general problem with the efficiency of ReportViewer when it is called from an aspx page. In any case thanks for the reference to your post. I have added on to your Microsoft Connect submission.

"Not sure this is exactly the same problem ...." It is not clear for me, whether you are running a report server report in the web viewer in remote mode (the report is actually then run on the report server) or are running the same report in in local mode on the web server. If the latter is the case then I assume that you have checked to insure that it is in fact the only the report local processing that is causing the delay and load on the cpu? If for example if your report has subreports that each require separate data or the data delivery itself is the bottle neck (since you are now not on the report server for that either). Have you tried to just pull the data as the report requires it on the same web server (including subreports and chart)? Particularly, if your report server is also the database server there can be huge differences between local and remote processing.

I am having the same problem. I converted an application from asp.net 3.5 to 4.0 . I am running reports in local mode. My reports have dynamic grouping. Both the old (asp.net 3.5) and new (asp.net 4.0) consume 50% CPU. In the old version a large report executes in 15-20 seconds. I the new version the execution is stuck for minutes - so a restart is needed in IIS. After narrowing the report data still the old report executes much faster. Both reports process the same data from the same sql server.

Hi Yosi, please also comment on the connect bug at - https://connect.microsoft.com/VisualStudio/feedback/details/561679/reporting-services-using-parameter-for-dynamic-grouping-very-slow Alle

Hi Alle The report is a local report running on the web server. I am not running report server. The system is just about to go live and I am doing stress and performance testing. The timings I stated above are for the report running in isolation i.e. no other load on the server. When I pull the data down from the database which is also on the web server by running it in local mode in report builder preview it runs 10 times faster. I am not using dynamic grouping, I am grouping on dataset fields but I do have some filtering. This is clearly a bug in report viewer. There is no way it should cause wpw3.exe to spike to 100% cpu for 80 to 90 seconds for a report that only takes 8 seconds normally. Regards Allan Ricci

Hi Allan, hi Yosi, Yosi's post was interesting. I have a small test app that runs the report in either static or dynamic states to test the running times. I hadn't thought of just changing the target framework for the project. The problem only seems to occur with the Framework 4.0. If I change the target Framework to 3.5, then the problem is gone. According to the web.config the same version of the viewer (10.0.0.0) is being loaded as an assembly which would indicate to me, that the problem is internal in the framework processing of the report and not in the viewer itself. Of course this is not a real solution, but it does narrow things down a little bit. Alle

Hi Alle I updated your Connect submission - if I compile against 3.5 it also drops the report runtime from 80-90 seconds to 7-8 seconds. I could only run it from the built-in development server, in IIS7 I cannot access the site at all, it displays the dreaded ""has not been pre-compiled" error. I played around for hours with various config.sys changes, recopying the site etc, no go. Cheers Allan

Hi Allan, if you can compile in 3.5 without errors on the dev box it should run in IIS 7 too. Check the AppPool settings whether the framework set there is 3.5. I tend to throw out the "compatability" settings that VS 2010 adds to the web.config and the framework declaration in the web.config and just change the AppPool setting of the framework version. Of course this means that your controls must be OK for both frameworks and I don't update the web.config file anymore in the deployment. However I definately do NOT consider targetting the 3.5 framework to be a work around. This can only be a stop gap measure until a hotfix or update is supplied. Alle

Yes it should but unfortunately it doesn't. Downgrading from .net 4 to 3.5 doesn't appear to be supported. btw I had the AppPool set to 2.0 which is correct. I could spend many more hours on it but instead I think I will use one of my free support incidents. Maybe that will be faster than stumbling around in the dark or waiting for a resolution via Microsoft Connect.

I think you will find that the ScriptManager that you are using will not run in an AppPool with FW 2.0 (because of the asynchronous mode capability). Since this is in the ASPX markup it must precompile, but can't with that FW. Either set the pool to 3.5 (shouldn't really hurt anything else but do test) or use an older script manager. The step to 3.5 was mainly WCF. But we're getting off topic now. I would really like to hear from MS what is going on with the FW 4.0 implementation and when we can expect a resolution? Obviously we are not the only ones affected by this problem. Alle

There is no option to set an App Pool to 3.5. http://www.hanselman.com/blog/HowToSetAnIISApplicationOrAppPoolToUseASPNET35RatherThan20.aspx

Hi Allan, Hi Yosi, I still have no real solution and have now optimized an XLinq XML "changer" to convert the dynamic grouping to static values at run time. This bypasses the problem I had but of course is not a real solution. It works for most scenarios though. If you like, I can provide it for your use. At the moment I am trying to escalate the problem at MS. There have been suggestions that this could be due to the new sandbox AppDomain model in 4.0, but even if I give the Viewer / Report full privileges and full trust there is no change. I expected that since if that were the problem the other usages of parameters in a report should have been negatively affected too. At least in my case it only seems to be the usage of parameters in the grouping expressions. Allan, in your case you indicated you do not have any dynamic grouping. Did you ever narrow it down to find out exactly what usage is causing the slow down? Possibly the chart? Or did you get it running on 3.5 and decide to forget 4.0? Alle

Hi Allie I have managed to implement our system after compiling against .Net 3.5. I am able to get around the "not compiled bug" if I publish the system by right clicking on the project rather than using aspnet_compiler. It may be something to do with the "allow this precompiled site to be updateable" flag. In any case it is a successful workaround for us. Cheers Allan Ricci

Well in the past weeks I have been in contact with the Microsoft developer team regarding this problem. It seems there is "kind of good news" and not so good news. The problem seems to be a degression that occured with the transition of the report viewer local mode to Net 4.0 running in the sandbox Domain (the Sandbox Domain is used by default in Net 4.0). There is a good blog post from Brian Hartmann describing the reasoning and importance behind using the sandbox domain (for example to avoid memory leaks when running the report viewer in an aspnet page). Expression Evaluation in Local Mode Unfortunately this problem was not caught in the Beta Testing. The "not so good" news is that the problem seems to be related to expression host (in the sandbox domain) calls into the application domain (IOW cross domain calls) which at least in the case of the grouping expression are apparently not handled very effeciently resulting in extremely long execution times that are in fact cumulative. (For each grouping expression that is considered dynamic the penalty occurs. 5 dynamic groupings = 5 penalties). It could be that other scenarios, other than dynamic grouping, also cause this behavior. Extreme means about 15 seconds per grouping parameter in ASPNET or about 7 seconds per grouping paramter in a WinForms app. Since this area (cross domain calls) is a very sensitive area that also seems to be tightly tied into the CAS security changes in Net 4.0, it doesn't seem to be possible to make a quick "hot fix" or so to resolve the problem. In fact, it may well be possible that the problem can only adressed in a major service pack or new release. The bug is filed and the team is aware of the problem. The "kind of good" news is that for many scenarios it is possible to work around this problem. However, be aware that going back to the FW 3.5 or using any of the following methods other than the replacement of the dynamic expressions, will result in the report viewer being run in the current AppDomain. This in turn means that you will have a memory leak for each report that is executed until this report or the AppDomain is unloaded. In the case of an ASPNET application this could well be the session timeout for each report and can quickly become a problem. For a WinForms application this should not be a problem. Workarounds: You can compile your application in Net 3.5 and avoid all performance problems with the Report Viewer (which is multi- targeted for 3.5 and 4.0). If you do so, DO NOT use the viewer option "ExecuteReportInSandboxDomain" since that will bring back the problem (duh). You are then running in the current AppDomain, could have memory leak problems with an ASPNET application, and of course abandon any use of other Net 4.0 features. Loading the old viewer (can be done) does allow the use of the sandbox domain but would possibly bring back the problem of the sandbox domain not unloading properly. It's a no win situation I think. Alternatively you can stay with Net 4.0 but force the use of´ legacy CAS security. Disclaimer - I do not know what other ramifications this could have with other controls or methods in your application. Use at your own risk! Although this was suggested by MS as a possible workaround you are setting application level security policies with these tags! For a WinForms application you can use <NetFx40_LegacySecurityPolicy enabled="true" /> in the configuration -> runtime section of the app.config file which forces the usage of the current AppDomain. Although the MS documentation for this property does not indicate it, this tag only has an effect in WinForms applications. In an ASP Net application you can use <trust legacyCasModel="true" level="Full"/> in the system.web section of the web.config file to achieve the same result. The last method that I am aware of is to replace the parts of the report definition that result in the extremely poor performance at runtime. This includes at least the use of parameters in the grouping expression and could also be caused by other elements. This keeps the report performance, still uses Net 4.0 and keeps the report in the sandbox domain with all of it's advantages (including the fixed issue of the memory leak). This is actually easier than one might think and actually outperforms dynamic grouping in the current AppDomain! Since this post is already very long, I will post this separately. HTH Alle

Replacing Dynamic Grouping at Runtime Since the release of Visual Studio 2010 and Net 4.0 there has unfortunately been a major loss of performance when dynamic grouping is used in a report loaded into a report viewer running in local mode in the sandbox Domain (which is default in Net 4.0). This is a blocking bug which is known at Microsoft but probably will not be resolved in the near future (see my previous post). In order to still use Net 4.0 in the standard configuration and use the current version of the report viewer in local mode it is possible to load the report definition at run time, after having replaced the dynamic grouping expressions with static expressions. At the moment this seems to be the easiest with XLinq. Since I assume most of you are not necessarily more experienced with XLinq then I am I have included an example of how this can be done. Any better suggestions are appreciated. I would suggest, that one continues to design the reports as originally intended using parameters since then when the problem is fixed it would only be necessary to comment out the replacement code. By doing this and assigning the parameters in the code behind / beside you also open up the possibility of very generic processing of the repacement process since the named parameter usage in the report definition can easily be determined using a similar XLinq process. Again, I will try to cover this in a separate post when I have time. Sample replacement of the dynamic grouping parameter at runtime in code behind: //this is the loading of the report definition in XLing and changing the value of "Group1" XElement report = XElement.Load(Server.MapPath("/") + "Reports/" + "MyReport.rdl"); report = ChangeXMLGroupingValues(report, "Group1", "ModifiedDate", null); //see below //report has now been changed and the dynamic expression has been replaced //we now need to load the report definition in the ReportViewer.LocalReport using (System.IO.MemoryStream str = new System.IO.MemoryStream()){ report.Save(str); str.Position = 0; using (System.IO.StreamReader read = new System.IO.StreamReader(str)){ ReportViewer1.LocalReport.LoadReportDefinition(read); } } //create a DataSource and add it Microsoft.Reporting.WebForms.ReportDataSource rds = new Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", dt); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(rds); //Add Parameters to the local report here - must be after the report definition is loaded //refresh / run the report ReportViewer1.LocalReport.Refresh(); The definition of the function ChangeXMLGroupingValues(....) : protected XElement ChangeXMLGroupingValues(XElement reportXML, string strGroupName, string strGroupBy, string strSortBy) { string dns = "{" + reportXML.GetDefaultNamespace().ToString() + "}"; var Result = from el in reportXML.Descendants() where el.Name == (dns + "Group") && (string)el.Attribute("Name") == strGroupName select el; if (Result.Count() > 0) { XElement MyElement = Result.First(); MyElement.Element(dns + "GroupExpressions").Element(dns + "GroupExpression").Value = "=Fields!" + strGroupBy + ".Value"; if (!string.IsNullOrEmpty(strSortBy)) { MyElement.Parent.Element(dns + "SortExpressions").Element(dns + "SortExpression").Element(dns + "Value").Value = "=Fields!" + strSortBy + ".Value"; } else{ //use the Group Expression as Sort Expression MyElement.Parent.Element(dns + "SortExpressions").Element(dns + "SortExpression").Element(dns + "Value").Value = "=Fields!" + strGroupBy + ".Value"; } } return reportXML; } HTH Alle

I found that when my reports upgraded, static filters (e.g. =Fields!ColumnNumber.Value = 1) got converted to appear dynamic (e.g. =Fields!ColumnNumber.Value equals =1). I wrote about it here http://i-mack.blogspot.com/2011/01/reportviewer-2010-localreport-rending.html Alle...thanks for all your input into the issue. Your solutions kept business rolling while the root cause was found. Rob