CODEWITHSUNDEEP
sqlsql-serverstored-proceduresreporting-servicesssrs-2008
OffensivelyBad
OffensivelyBad

Reputation: 21

SSRS Best practice for multiple result sets from Stored Procedure

I'm using SSRS 2008 and wanted some advice on the best practices for handling multiple result sets.

I have a stored procedure which returns 4 result sets of related data, however each result set has a different number of records returned. Of course in SSRS only the first result set is processed, so I'm left with 2 options, as far as I can tell:

  1. Create 4 different stored procedures which return the 4 different data sets
  2. Insert all 4 result sets into a temporary table and return the results from that table.

The problem with the first option is that the 4 results are all derived from the same basic data (into a temp table) and then joined/grouped with other tables/data. So to split them out into separate stored procedures seems like it would cause more stress to the DB than a single sproc.

The problem with the second method is that I would have to include the same dataset into SSRS 4 times, pulling different pieces of the result set each time and filtering out the nulls on the correct columns.

For instance, let's say I have 4 result sets that return 4 columns each and 4 records each. The first 4 columns and the first 4 records are related to the first result set (the rest of the columns are null). The second result set only populates columns 5-8 and records 5-8, etc.

The question is, which way is more efficient? Multiple stored procedures or 1 stored procedure used multiple times in SSRS? Thanks!

Upvotes: 2

Views: 8692

Answers (2)

Ross Bush
Ross Bush

Reputation: 15185

This depends largely on the query processing time. If the four datasets take a long time to process, then the second option would make more sense. For example, if each query takes 10 minutes to process data, then the wiser choice would be to roll through the data only once and return a big fat Frankenstein result set and sort it all out in the report with filters and whatnot. Especially if the alternative is ~40 minutes of similarly repetitive data processing.

Alternatively, if the data processing is relatively light (and especially if reuse case could be made) between the four variations then the first option could be more ideal.

If it is more of a half and half, then some combination thereof could be used.

Alas, could you post the approximate data processing time and number of records returned by the four datasets?

Upvotes: 0

leqid
leqid

Reputation: 931

Sounds like the data retrieval is the more expensive operation, so you probably would want to do that only once. (Although I'm not sure you would "stress" SQL Server, if that's the data source: the basic data and joined/grouped data would likely be in memory at that point, so additional physical reads probably would not happen if different stored procedures re-read the data.)

Consider an Entity Framework LINQ-to-SQL query with several entities INCLUDEd: the shape of a result set is very wide, with a column for each field of each entity. But this avoids a trip to the database for each included entity.

Unless your report has hundreds of pages or is otherwise rendering- or processing-intensive, I think option #2 probably is more efficient.

Are you able to look at the execution statistics in the SSRS execution log? I always use the ExecutionLog3 view, and check the times of TimeDataRetrieval, versus TimeProcessing and TimeRendering.

Edit

I know including just links is frowned upon here, but I don't want to include possibly copyrighted stuff that I didn't create.

Please see the section Examining Queries Sent to the Database here http://www.asp.net/web-forms/tutorials/continuing-with-ef/maximizing-performance-with-the-entity-framework-in-an-asp-net-web-application for an example of a LINQ-to-SQL query along the lines of ...Departments.Include("Person").Include("Courses"). Note especially the picture of the Text Visualizer, which shows the query constructed: it is similar to your option #2 in that multiple entities are included in the same result set.

Upvotes: 0

Related Questions