GhengisX
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.
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?
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.
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.
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
any experience with service broker out there, it seems to be a big part of what I am looking for?