Database Reference
In-Depth Information
Figure 18-9.
Actions performed during SSRS reporting
As data is retrieved from the data warehouse or your SSAS cubes, it is important to think about select
performance. Select performance is also a consideration as the report definitions and cached results are retrieved
from the SSRS databases. As you might expect, using RAID arrays for the data warehouse, cube folders, and SSRS
databases can increase report performance in general.
Rendering Options
The SSRS web service must be considered if you want to improve report rendering performance. You may
remember from
Chapter 16
that report rendering allows SSRS to create various outputs, such as HTML or PDF.
The rendering process involves large amounts of RAM and processing power, so increasing both of these on the
web server that hosts the SSRS web and Windows services will give you a performance gain.
Filtering Data in SSRS
One simple, but vitally important, aspect of increasing reporting performance is to query only the data needed
for your reports. It is seldom appropriate for you to select all columns and all rows from a table or cube. Restrict
your queries to only what is needed, and you will see marked improvement.
When creating reports that use data directly from the data warehouse, we recommend using stored
procedures that contain parameters to retrieve only the data you need in your reports. This allows you to create
reports with dynamically generated data, and you gain a small performance increase because of the way the
database engine works with stored procedures. (Sadly, SSAS does not include stored procedures, but perhaps one
day this will change.)
Indexing Options
Although indexing is implicit in an SSAS database, adding indexes to your data warehouse tables can increase
select performance. Because report data is often retrieved from many tables using a SQL
JOIN
statement,
creating indexes on columns commonly used in these joins makes sense. Typically, these columns are foreign
