Launching SSRS: SSRS is an XML Web Service that, like any .NET assembly, must be compiled from intermediate language on first invocation and recompiled if it's paged out of memory. This means the first time any report is invoked or deployed to the server, you can expect a considerable delay while SSRS is launched and initialized. On my development system (an Intel Core 2 Quad with 8GB RAM), first invocation can take 30-45 seconds, pounding on all four processors.

Loading and interpreting the RDL: Once the report processor validates the user and identifies the report, the report must be fetched from the SSRS report catalog, decompressed and the RDL interpreted. Reports with embedded graphics and code are considerably larger than those with de-referenced graphics or DLL references and, of course, larger, more complex reports take longer to process and cache.

Capturing input parameters: Once the report processor begins interpreting the RDL, the specified database connection(s) are opened, but before any queries can be executed, any unset query parameters (like those used to feed the WHERE clause of a query) or report parameters (those to feed RDL expressions) must be captured from the user or the system. This means that parameter values can be entered or chosen by the user or generated from system information like the time, date, the user’s credentials or other system-provided information.

Note that the Report Manager can also be used to specify default parameter values (and prompts) for specific reports. The report processor does not wait on the user if all parameters have default or computed values but when there are unset values, SSRS must create appropriate UI elements and capture values from the user before the report data region queries are executed. Yes, this might mean that specific queries must be executed beforehand to populate parameter pick lists or deal with cascading parameter schemes.

Generally, it does not take much time to perform this step, as the server simply waits (for awhile) the user to supply the values. However, if the user is required to supply defaults each time, this can increase their impatience so it’s a good idea to figure out what makes the most sense. For best overall performance, it’s not a good idea to execute unfocused queries—those with extremely broad-scope results but I’ll discuss this again later.

Processing the query: Once the input parameters are captured, the data region query(ies) are executed using the supplied parameter values. This phase can also take a considerable amount of time depending on how many connections must be opened, how much query processing must be performed to generate the rowset (the efficiency of the queries), if the data cache can be leveraged, how many rows are returned and other factors. You have at least a modicum of control over all of these performance-gating constraints. This is a great place to focus your attention as you try to optimize report performance.

Generating the report: Along the way, the report processor must also evaluate each of the expressions embedded in the RDL. These expressions can be simple assignment statements, more complex conditional logic, invocations of RDL embedded code or execution of external CLR .NET DLLs. They can be data-driven or as I’ll discuss later, these expressions can be parameter-driven. In any case they’re often used to focus the report on a subset of the data and provide the user with a far more useful report. Again, the complexity of the expressions can impact the processing time—especially if the code must be re- executed on each row in the report.

Rendering the report: One of the last phases handled by the report processor is to pass the formatted report to the report rendering extension which transmogrifies the report to the specified format (such as WMI, HTML, RTF, Excel or TIFF). Consider that the report rendering extension works somewhat like a printer’s rendering engine so it can consume considerable CPU time and memory (that it robs from other processes running on the same system). If the rendering extension has to compete for resources with the SQL Server query engine or some other resource hog like PowerPoint, report performance can suffer. This means you should run the SSRS service on another, dedicated system.

Graphics rendering and processing can also consume additional resources so strategies that move graphics (pictures) to sub-reports makes a lot of sense. In the example reports I discuss here, I created a subreport that did all of the graphics heavy-lifting so the base “query” report that located the product to examine could run faster.

Creating a snapshot of the generated report: The generated report (based on the query parameters provided and the rowsets it consumes), can be persisted in the in the form of a report “snapshot” to be recalled later from SSRS on demand. By using snapshots, you can dramatically reduce or eliminate the load on the DBMS engine caused by query processing thus reducing the time SSRS has to wait for the results to be returned. This approach makes a lot of sense for relatively static data that only changes gradually over time, so it’s especially useful for reports based on rollup or summary data where information that’s a few minutes old won’t pose a problem.