3

People at my organization use SQL Queries within SSRS reports: for instance

SELECT name, age 
FROM egTable

Then, this query is run on the database and then they use a FILTER within SSRS to get rid of unwanted rows. For instance, there is a filter in the SSRS called AGE, and this will be something like Age = 11

My proposition was that, this is bad: this way of doing things means that we are querying the ENTIRE table, and then from the GIANT resultset we are just getting rid of unwanted rows. Instead, we should be introducing a parameter: @age, and then write the query as follows:

    SELECT name, age 
    FROM egTable
    WHERE age = @age

Am I correct in saying that the first method pulls the entire table, whereas mine is far more efficient because it only returns a small result set?

How can I verify/prove this?

  • I believe it depends on the implementation overall, I mean maybe you want the whole 1000+ batch of records being reported or maybe you only need from age 10 'till 15 or more specific. But yeah, in many cases a report with variable input for filtering its more useful than a table-full of query. – Nelz Mar 31 '15 at 17:19
  • Although you are probably correct, what people ultimately care about are speed and accuracy. So perhaps you could create an alternate version of the report that uses the filter in the query rather than in the report. Prove that it loads the same (assumed accurate) data faster than the original. If you need a tool to provide hard numbers, you can grab Fiddler telerik.com/fiddler. – mmarie Apr 6 '15 at 21:00
  • You could always run a trace using SQL Server Profiler on the 2 separate queries and compare the results – B.M. Apr 27 '15 at 16:32
4

TL/DR

Yes you are correct, you can prove it by showing the result of ExecutionLog in the SSRS database.

Longer Answer

I created 2 identical reports based on the Adventureworks database, one with a filter on the dataset for City and one with a parameter in the query for City.

Report 1

The query for this report is:

SELECT  Person.Address.*
FROM    Person.Address

And the filter is added like this:

enter image description here

The result of this report is:

enter image description here

Report 2

The query for this report is

SELECT  Person.Address.*
FROM    Person.Address WHERE city=@city

There are no filters on this dataset

enter image description here

The result of this report is:

enter image description here

Where you can see the same data is shown, but the behaviour is different since the user has to put in a value for the filter (marked in yellow)

If this is undesired this can be overcome by adding a default value to the parameter and setting the visibility to hidden like this:

enter image description here

and this:

enter image description here

Proof of efficiency

The efficiency of both reports can be proven by querying the executionlog table and views like this:

SELECT [ItemPath], [Parameters], [TimeDataRetrieval], [TimeProcessing], [TimeRendering], [RowCount] 
FROM ExecutionLog3;

Which for these 2 reports returns:

+--------------------------+--------------+-------------------+----------------+---------------+----------+
|         ItemPath         |  Parameters  | TimeDataRetrieval | TimeProcessing | TimeRendering | RowCount |
+--------------------------+--------------+-------------------+----------------+---------------+----------+
| /Report Project3/Report1 | NULL         |               669 |           1878 |           880 |    19614 |
| /Report Project3/Report2 | city=Bothell |                 8 |             42 |             4 |       26 |
+--------------------------+--------------+-------------------+----------------+---------------+----------+

So the second method not only fetched a lot less rows (26 versus 19614) but also consumed less time processing and rendering the report.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.