CHAPTER 2 REPORT AUTHORING: DESIGNING EFFICIENT QUERIES
that you’ll be working with to produce queries is important, but for the sake of the example, the joined
tables are typical of a normalized database where detailed transactional data is stored in a separate table
from the descriptive information and therefore must be joined. The Trx table in Listing 2-3 is where the
transactional patient service information is stored, while the descriptive information of the specialty
services such as “Physical Therapy” is stored in the Services table.
Other tables, such as the Patient and Employee tables, are also joined to retrieve their respective data
elements. You use the SQL functions COUNT and SUM to provide aggregated calculations on cost and
service information, and RTRIM to remove any trailing spaces in the concatenated patient and employee
names. You can use the ORDER BY PATID clause for testing the query to ensure that it’s returning multiple
rows per patient as expected. It isn’t necessary to add the burden of sorting to the query. As you’ll see in
the next chapters, sorting is handled within the report. Dividing the load between the SQL Server
machine that houses the report data and the report server itself is important and often requires
performance monitoring to assess where such tasks as sorting, grouping, and calculating sums or
averages for aggregated data will be performed. If the report server is substantial enough to shoulder the
burden and is less taxed by user access than the actual data server is, it might be more efficient to allow it
to handle more of the grouping and sorting loads. More often than not though, it is considered best
practice to have the relational database engine perform as much of the work as possible to alleviate
some of the load on the report server.Testing Performance with SQL Server Management Studio (SSMS)
Now that you have developed the query, you’ll look at the output to make sure it’s returning accurate
data within acceptable time frames before moving on to the next phase of development. Figure 2-4
shows the results of the output from SSMS and the time it took to execute the query. You can directly
modify the query further in SSMS if you want to. However, one of the best features of SSMS you’ll notice
is the ability to view quickly both the number of records returned and the execution time. Once you’ve
done that, the next step is to create the stored procedure.
You now have the data the way you want, and the query is executing in an average of one second. To
verify the execution times, run the query 15 times in sequence from two different sessions of SSMS.
Execution times will vary from zero to two seconds for each execution. For 3,924 records, which is the
number of records the query returns, the execution time is acceptable for a single-user execution.
However, you need to improve it before you create the stored procedure, which you will want to scale
out to accommodate hundreds of users, and begin building reports.
Looking at the Execution Plan tab in SSMS will give you a better understanding of what’s happening
when you execute the query. In SSMS, click the Display Estimated Execution Plan button on the toolbar.
When the query is executed, the Execution Plan tab appears in the Results pane. Alternatively, if you just
want to see the execution plan without returning results, just press CTRL+L.