Link to home
Start Free TrialLog in
Avatar of GhengisX
GhengisXFlag for United States of America

asked on

Batch Report Processing

Is there a way to mange report load using Sql Server Reporting Services?  So of my reports take a while to run.  It would be nice to be able to batch them.    I don't think caching is much of a help since the users change data and then run a report and see results.
Avatar of rickchild
rickchild
Flag of United Kingdom of Great Britain and Northern Ireland image

Are the reports running slowly at the data extraction stage (So would your data query or stored procedure it is based on be running slowly too), or at the rendering stage?
Avatar of GhengisX

ASKER

the data queries are expensive and slow.  the rendering is not an issue.  If multiple users run the report at once it can really bring the server to its knees.

we are tuning the sql and database but it will not be enough.
I see, so you are looking to First-In-First-Out queue the queries really, so they are not running concurrently and killing the database server.

Before we get into this you could consider the caching again, how often are the users updating the data and then needing to look at the reports?  Do the users need the data immediately available after the change?

You may find this is only a few times a day and they don't need it immediately, then you can use the caching to cache a snapshot say every hour or so.  "Render this report from a report execution snapshot "  You could then stagger your snapshots over the hour.
they update data then run a report to see results.  then they update data again and tune.  there is a lot of data so we dont monitor if things have changed between one report run and another, otherwise we could cache.  
ASKER CERTIFIED SOLUTION
Avatar of rickchild
rickchild
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
any experience with service broker out there, it seems to be a big part of what I am looking for?