Reporting Services 2005 BestPractises Question

Hi,

We're starting to migrate from Crystal Enterprise to SSRS2005, and had some performance-related questions in relation to complex reports running against large tables.

Scenario:

. We have fairly large source tables, some greater than 5 million rows

. We use fairly complex stored procs which use dynamic sql based on about 10-15 search criterias entered on web page

. Currently our web middle tier calls a stored proc which inserts into a report table. Then UI calls the report which has SQL query to filter the report table on the UserId and shows only the rows for that user.

Question:

. Is this method - running stored proc in app which inserts into table, then asking report to filter the table based on UserID - considered the best way for designing and running complex SSRS2005 reports with lots of data?

(or)

. Is it better to directly run the stored proc from the report and return the resultset directly to the report?

Advantages or disadvantages of either method, useful articles, best practises...all are appreciated.

Thanks,

JGP

In general, only feed the data you want to present into the report. It's much faster to filter data out on the back end than it is to package it up (even if it's not going across the wire) and send it to Report Server.

You can replace the default parameter handling (by using the WebForms control or Url Access) if you don't like the way our parameters are shown (with 10-15 search criteria, this is probably a good idea).

Why not have the report itself call your stored procedure directly w/o the need for the intermediate table? If the number of rows returned is reasonable (ie, criterial + userid is passed to the stored proc) then this should be fine. How many rows per criteria/userid will be returned? How many KB? Query Analyzer will tell you this.

I would make sure to test whatever choices you make. Don't take anyone elses word for how your machines will perform. Use Microsoft ACT or Visual Studio 2005 and validate all assumptions. Please.

Thanks, Donovan.

|||

I must have a misunderstanding on this. I thought it was better to package it up and send it over to RS and use filters so that RS could cache the result set. Do I have a disconnect here?

R

|||

Sending 5m rows across the wire is slow. Filtering/aggregating in SQL is much faster than the overhead of serializing/deserializing and hitting the wire with a bunch of data a particular rendering isn't going to use. Live reports work much better if the only data RS has to deal with is the data it actualy displays. This isn't just true of RS but any application that deals with a database backend.

Of course, I'm generalizing and simplifying and the real answer is more of an "it depends on your report and your usage". But hopefully this explains my comment better.

Thanks, Donovan.

|||

I think the value in cache is negated by the potentially large recordsets that RS must sift through for grouping/sorting. Use procs for everything, we use temp tables rathen than real table with each temp table being called from a perspective proc and destroyed at connection close. For example, we have reports that return a weeks worth of data(40,000 rows), I organize it by day/product by selecting from the base table into a temp and then returning the 7 rows for the day report to RS.

I also find manging paramters in RS beteer than procs. For example, I ask for one date parameter and the derive all the others from it where I can. For example, a report will show this week, last week and last six months turn around time for a product(s). By managing the parameters in RS, I can just pass the different params to the same sproc although they are set up a different datasets. I've toyed with the idea of adding another input parameter to the procs to push the date manipulation back into the backend.

My general philosphy is to use RS(or any other tool) as a presentation layer and do all calcs except for basic summing, avergaing, etc.. in the DB.