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.

sql

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.

Reporting Services 2005 and Business Logic

I'm working on a app with complex reporting requirement and would like to centralize the business logic for app and reporting. We are not planning to use SP for reporting. Having said that, i would ideally like the reports call the business layer (business.dll) assembly which in turn will call the data access layer (data.dll) assembly to run the required queries and get the results via business assembly as a custom business entity or data set. I'm not sure if this possible with Reporting Services 2005. I have looked at the custom data processing extensions but i dont think it will provide me the required business-data separation unless i'm missing something.

I was wondering if anyone has done this before and if yes, could you please provide me some direction or insight or sample. It will be of great help. I also saw the custom references tab where you can reference an .NET assembly and invoke a method within .NET assembly but i'm not sure if that method could return a custom entity that could be consumed by the reports. Please recommend the right approach to achieve this.

Thanks in advance

There are at least four approaches you could consider:

1. Your app can call down to the business logic layer to retrieve the entity or an ADO.NET dataset and bind it to a local report. This scenario assumes that you don't need to deploy the report to the report catalog (the report is available to the application only). For more information about local vs. remote processing, read this article.

2. If you need to share the report with other users (deploy to the report catalog), write a CDE that will take the serialized entity (e.g. as an ADO.NET dataset) and expose it to the report, as demonstrated here.

3. Assuming SQL Server 2005, implement a CLR stored procedure(s) to call down to the business layer and surface the results to the report, as shown here.

4. Write a web service to return the results in XML and use the RS 2005 XML data provider (see this post).

The first approach is probably the easiest to implement.

|||

Hello Teo,

Thanks for your response. Since this is a web app and would like to deploy to the report catalog i'm thinking of option# 2. I have read the customer data extensions and i see it more as a data layer assembly. We will have to retrieve the custom entity from the middle tier, update it (only if required) and then display using CDE. Now based on that i have these follow up questions

1. Our entity is not going to be a ADO.NET data set at the business layer level, its going to be a custom .NET class so will be possible for the CDE to retrieve this custom entity and then display it. I can serialize the custom .NET class into a XML and then have the reports consume the XML but i'm concerned about the app performance serializing/deserializing.

2. I do not require any open/close connection in the CDE since this will be handled by the app data layer. Is this OK?

3. What is your opinion about accessing .NET code via assembly references and embedded code within the report properties? Is this a good option to think about.

Thanks

|||

1. Performance should be an area of concern. Assuming 100 Mbts LAN, it will take a fraction of time to send the serialized payload across the wire. I didn't see any performance issues with my CDE which serializes ADO.NET datasets.

2. The CDE that I pointed you to doesn't establish a connection either. This is entirely optional.

3. You cannot use this approach to bind a dataset to a report region if this is what you are after. That's because the DataSet property is not expression-based. Other than that, I do use external .NET code extensively to call common functions, e.g. to handle number formatting. In general, I'd recommend you put any .NET function that you need in your reports in an external assembly so you can reuse it across reports.