January 24, 2006 at 2:01 pm
My report queries are fast when run through query analyzer.
However, when I run a report for the first time they are slow, up to twice a long as subsequent requests. I'm thinking this is a cache issue. How can I speed up the reports? Any tips welcome.
January 24, 2006 at 2:26 pm
There is no way I can tell to Optimize the report rendering.
It is likely that the first time the report is run the data is NOT Cached, so it must return the dataset which probly is what is taking much of the time. Once the data is cached it takes almost no time.
Have you checked the performance on the query/procedure used on the dataset?
January 25, 2006 at 7:57 am
If the report manager is slow to load on Windows 2003 server then you can:-
1. Click on "Start | Settings | Control Panel | Administrative tools | Internet Information Services (IIS) Manager"
2. Expand the server node then "Application Pools"
3. Right click on "DefaultAppPool" and "Properties"
4. Click on "Performance"
5. Change the "Shutdown worker processes after being idle for (time in minutes)" to 480 (8 hours)
6. Click "OK" and exit
There will still be a delay first time each morning the report server is accessed but this can be fixed by running a dummy report on a schedule at 6am before the users use the system.
Hope it helps
Dale
January 26, 2006 at 6:13 pm
Another thing to keep in mind is that the more aggregation and grouping that is done through RS, the more time the report will take to run. You may want to look into doing any grouping/aggregation in your SQL before it gets to RS.
Also, if your data isn't needed to be "real time" but can be from a snapshot, you might look into that.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 28, 2006 at 6:10 am
I've not tried this but I'm assuming you could try to warm the cache - ie schedule a run of the report/s at (say) 6am so that the data is cached and the first run is quicker. This relies on a few things: i) reports that are not parameterised, or if they are, everyone seems to be using the same parms; ii0 you've got control over the life of the cache (which i thought you could set through report manager?)
You could do the warming through a call to the webservice or the url access mode. If you did this, i'd be tempted to warm the .net app too (ie report manager) so that it returns quickly for the first user of the day too. Again, this will be impacted by the lifetime you've set on the app-pool that's running the site.
hth,
Steve.
January 28, 2006 at 6:12 am
If the data isn't going to change (ie you can warm the cache with the intention of every user accessing the same data from within it) thinking about this, you'd be better off making it a snapshot as Jason suggested. Plus if you keep the snapshots, people can go back in history and review a previsou days run values.
Steve.
January 30, 2006 at 10:41 am
I like the idea of keeping the snapshots for historical reference although I don't want to have my general users using the Report Manager. I'm trying to keep everything accessible through the report viewer. This allows me to integrate tighter and cleaner into my app and also pass some parameters to the report that rely on the session.
Has anyone accessed old snapshots through the report viewer? Is it possible?
I think I'll take my grouping out of the reports but this may make the reports harder to group visually the way I want.
January 2, 2007 at 10:06 am
Have you tried putting your SQL into a stored procedure, then running the report from the stored procedure? This technique sped up my reports immensely.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply