Reports take "too long" to run!

I've been developing SSRS reports for a few years now and have not been
immune to client claims that a report takes "too long" to run.
Obviously "too long" is relative but I have always been of the midset
that in general, the closer to real time and greater the amount of data
a report inclues, the longer it will take to render.
As a consultant I try to manage these expectations with my clients and
offer options such as subscriptions, snapshots and data caching as ways
to improve render times (at the expense of real time data). I'm curious
how others determine optimal report perfromace and how you manage
client expectations.
As a developer I am also curious what solutions others have come up
with as ways to improve performace - in particlar for reports that must
include real time data (as of run time).
Thanks for reading. Hope to hear from folks.I personally think that 'any reporting against a RDBMS' should be
forbidden.
you should hit a cube; and have the cube process once a day or
something
-Aaron
Jim wrote:
> I've been developing SSRS reports for a few years now and have not been
> immune to client claims that a report takes "too long" to run.
> Obviously "too long" is relative but I have always been of the midset
> that in general, the closer to real time and greater the amount of data
> a report inclues, the longer it will take to render.
> As a consultant I try to manage these expectations with my clients and
> offer options such as subscriptions, snapshots and data caching as ways
> to improve render times (at the expense of real time data). I'm curious
> how others determine optimal report perfromace and how you manage
> client expectations.
> As a developer I am also curious what solutions others have come up
> with as ways to improve performace - in particlar for reports that must
> include real time data (as of run time).
> Thanks for reading. Hope to hear from folks.|||I usually do two kinds of reports, reports based on OLAP cubes and reports
based on SQL Server.
The SQL Server ones tend to be faster than the OLAP ones, but with less
complex data. Over the last two years I've come up with some personal
guidelines on how to write the MDX for the OLAP reports, using Descendants
for what it's worth, and adding parameters to the main query and not the
where clause whenever possible. With RS 2005, I've also found that multi
value parameters have to be treated very carefully. With the SQL reports, I
usually create SQL functions for the more complex queries. Most importantly:
do as much of the calculations in the query and not in the report.
For the time being, most of the reports I make replace the good old paper
reports that some poor soul has laboured over in Excel for 4 days. Now the
same reports shows up after 2-3 minutes in your browser. OK, it's not
instant, but it's a lot more instant than having to track down some lowly
employee and make that person do the whole thing in Excel. So if the users
complain about the report using 2-3 minutes, I usually ask them how long
they used to have to wait for it, which sort of makes them quiet.
But the resources on the server also plays a part. The same report can run a
lot faster on a server with the right hardware, and where a clever techie
has tweaked SQL Server and Analysis Services and what other apps that need
to be tweaked. A few indexes on the most commonly used tables might also
speed things up a bit.
Kaisa M. Lindahl Lervik
"Jim" <emailjeffp@.yahoo.com> wrote in message
news:1159201046.044272.12070@.m7g2000cwm.googlegroups.com...
> I've been developing SSRS reports for a few years now and have not been
> immune to client claims that a report takes "too long" to run.
> Obviously "too long" is relative but I have always been of the midset
> that in general, the closer to real time and greater the amount of data
> a report inclues, the longer it will take to render.
> As a consultant I try to manage these expectations with my clients and
> offer options such as subscriptions, snapshots and data caching as ways
> to improve render times (at the expense of real time data). I'm curious
> how others determine optimal report perfromace and how you manage
> client expectations.
> As a developer I am also curious what solutions others have come up
> with as ways to improve performace - in particlar for reports that must
> include real time data (as of run time).
> Thanks for reading. Hope to hear from folks.
>|||I have found that if you design reports that are designed for a person to
look at (and not just replacing some mainframe based paper report) that you
can get very good performance. By this I mean query execute time plus a few
seconds. Limit data to what I person will look at (a few pages) and then
make liberal use of drill through (not drill down). Drill down is a great UI
but is slow due to the amount of data being rendered. I have found users to
find drill through very intuitive. Also, html and csv wherever possible, not
PDF or Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jim" <emailjeffp@.yahoo.com> wrote in message
news:1159201046.044272.12070@.m7g2000cwm.googlegroups.com...
> I've been developing SSRS reports for a few years now and have not been
> immune to client claims that a report takes "too long" to run.
> Obviously "too long" is relative but I have always been of the midset
> that in general, the closer to real time and greater the amount of data
> a report inclues, the longer it will take to render.
> As a consultant I try to manage these expectations with my clients and
> offer options such as subscriptions, snapshots and data caching as ways
> to improve render times (at the expense of real time data). I'm curious
> how others determine optimal report perfromace and how you manage
> client expectations.
> As a developer I am also curious what solutions others have come up
> with as ways to improve performace - in particlar for reports that must
> include real time data (as of run time).
> Thanks for reading. Hope to hear from folks.
>