SSRS Tip: Put Parameters in Your Query, Not Your Filter

As a SSRS consultant, we’re often asked how to speed up reports. While there are many ways to do it, I would make this the top one: Put parameters in the query, not in the dataset filter, if at all possible.

Filtering is not your friend. Repeat after me: Put your parameters in your query, not in your filter.

To show you why, let’s look at two reports: one called Parameter in Filter, the other Parameter in Query. Both reports are identical except for one thing—in one report I use a filter on the dataset and in the other I use a filter in the query itself.

Note: I’m using a dataset here based on Adam Machanic’s bigproduct and bigTransactionHistory tables. The view I’m querying has about 700,000 rows—so it’s not a huge set.

When we run either report, we get the same output:

However, there is a one striking difference in the two reports: performance. Parameter in Query results come back instantaneously. Parameter in Filter results take about 10 seconds.

Why is that?

To find out, let’s take a look in Report Builder.

In the first report (Parameter in Filter), I have a very simple layout and one parameter:

And if we look at the query in DataSet1, we can see that the parameter is not in the query:

Figure 1 – No Parameter in Query

It is, instead, referenced in the filters:

Figure 2 – Parameter Used in Filters

In contrast, in my other report (Parameter in Query), you can see that the parameter is in the query:

Figure 3- Parameter Used in Query

SSRS has a view in the database called ExecutionLog3. It’s useful for seeing the differences between the two reports. (You may have seen it in the initial report manager screen shot.)

Let’s take a look:

You can see the path name of the reports on the left. The columns we care about are Time Processing and Row Count.

When the parameter was placed in the filter, SQL Server returned 756,000 rows to SSRS. SSRS then had to plow through them all, which took almost seven seconds. (The processing times given are in milliseconds.) This is even though the report only printed 25 rows.

In contrast, when the parameter is placed in the query, only 25 rows were returned. Which were the rows we wanted.

So, here’s the first rule for fast SSRS reports. Let SQL Server narrow your data as much as possible to avoid bogging down your processing times. If you’re not sure whether you have a problem, compare the row count from the execution log to the row count on the actual report output. If the numbers are way out of balance, you probably do.