Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

Figure 2-5. Viewing the Execution Plan tab displayed in SSMS

The query took one second to execute, and from this execution plan it’s easy to see which section of

the query had the highest cost percentage. There was a total cost of 11 percent when determining the

TrxTypeID and the ServiceTypeID values used as a filter in the WHERE clause. For reference, the TrxTypeID

integer field specifies the type of financial transactions as charges, payments, or adjustments. You’re

concerned only with the TrxTypeID value of 1, representing charges. For the service type, you’re

interested only in “V,” representing visits, and not in other types of billable services, such as medical

supplies. If you could get the cost of the WHERE clause down to a lower number, the query might improve

the overall performance.

Optimizing Performance: Dividing the Load

Because SSRS and T-SQL share many data formatting and manipulation functions, you can choose in

which process—query or report—these functions should be used. You can choose to have the query

handle the bulk of the processing. This limits the number of rows that the report has to work with,

making report rendering much faster. Alternatively, you can limit the selectivity of the query, allowing it

to return more rows than are possibly required. You can then have the report perform additional

filtering, grouping, and calculations, which allows the query or stored procedure to execute faster. With

many users accessing the report simultaneously, having the report share the processing load also limits

the impact on the data source server (in this case, SQL Server).

In this query, based on initial benchmarking, we’ve determined we’ll remove the portion of the

WHERE clause that specifies that the query should return only service types with a value of “V” for visits.

Instead, we’ll let the report filter out any service types that aren’t visits. When you remove the service

type criteria from the query and re-execute it, you can see that the overall execution time remains