Report Execution and Processing
| SSRS's Report Processor generally executes reports in a three-stage process:
Depending on the method of access, the server determines if it needs to execute all three processes or if it can skip one or two. The trick is in the report history. If the administrator specifies that the report should be rendered from a snapshot or cache, the report is rendered from the intermediate format stored in the database. Otherwise, the Report Server starts its processing from the data gathering stage. Report processing for drill-through reports is similar, except that reports can be auto generated from models rather than report definitions. Data processing is initiated through the model to retrieve data of interest. Report Execution TimeoutsThe time it takes to process a report can vary tremendously. While reports process, they take up time on the Report Server and possibly the report data source. As a matter of practice, most long-running reports take a long time to process due to a long-running query. SSRS uses timeouts to set an upper limit on how much time individual reports can take to process. Two kinds of timeouts are used by SSRS. The first kind is the query timeout. The second kind is the report execution timeout. Query timeouts specify how long an individual query can take to come back from the data source. This value is specified inside the reports, by specifying the timeout property while creating a data set. Query timeouts can also apply to data-driven subscriptions. The report execution timeout is the amount of time a report can take to process. This value is specified at a system level, and can be overridden for individual reports. To set this setting, click the Site Settings tab and modify the Report Execution Timeout property. The default value is 1800 seconds. SSRS evaluates the execution timeout for running jobs every 60 seconds. What this means is that every minute, SSRS enumerates through every running job and compares how long it has been running against how long it is supposed to run. The downside of this is that reports actually have a bit more time than the specified timeout value in which to run. If the timeout for a report is set to 30 seconds, SSRS does not check to see if it exceeded the timeout until 60 seconds, so the report actually gets an additional 30 seconds of runtime. Running ProcessesA process in the Report Server is also called a job. The two kinds of jobs are user jobs and system jobs. User jobs are those jobs that are started by individual users or by a user 's subscription. Some examples of user jobs include the following:
System jobs are those jobs that are started by the Report Server, including the following:
As mentioned in the previous section, SSRS comes by every 60 seconds and checks on the status of any in-progress jobs. These jobs could be querying their data source, rendering into intermediate format, or rendering into final format. It drops the status of these jobs into the Report Server database. This generally means that a job has to be running for at least 60 seconds for it to be canceled or viewed . To cancel or view running jobs, click the Manage Jobs link under Site Settings. From here, administrators can view user and system jobs and cancel any running job. Note Canceling a running job does not guarantee that a query has stopped processing on the remote data server. To avoid long-running queries, specify a timeout for the query during the report development phase. Large ReportsMost of the reports shown so far in the samples are fairly small and easy to run. However, in the real world, you might run into a report that, when rendered, equals hundreds of pages. For these reports, you need to take into account some special considerations. First, the amount of time a report takes to process is almost directly proportional to the amount of rows returned from the database query, and how long it takes to get those rows back. It is a good idea to check with the DBA before running long-running queries against a database. Also, check the execution plan of the query before running it. Perhaps further indexing can be done. Lastly, don't bring back any more rows than needed. Modern RDBMs are very good at sorting and grouping data. Let the RDBMS group and sort the data where it can; this saves CPU cycles on the Report Server as well as the network traffic. Second, take into account the rendering format. You should note that different rendering extensions have different effects on the Report Server. The fastest extensions and those that use the least amount of RAM are those whose output is essentially textMHTML, CSV, and XML. Excel and PDF are very resource intensive , whereas TIFF and JPEG fall in between the two extremes. Third, take into account the delivery method of the report. If a report uses pagination, it can be rendered like any other report. The default rendering format is HTML, which includes a soft page break. The page break is included intentionally and, in effect, produces a sort of poor man's paging. If a report is extremely large, this helps to deliver it via browser. If the report is delivered via subscriptions, it makes sense to deploy it to a file share and let the user's desktop be responsible for opening it. This takes the load off the Report Server and is the recommended course of action if using PDF or Excel. The following list includes some general tips to help handle large reports:
|
EAN: 2147483647
Pages: 254