Where to Execute? SSRS OR SQL

Question

When I'm creating an SSRS report, I always have a dilemma about "how to create the report with the least generation time possible".

In general the generation time (or performance time) is divided into two main parts:

1. The SQL Query.
2. The Report components (expressions, groups etc.).

As you know some of the things that are being performed in SSRS can be done in the SQL query and vice versa.

For example:

• I can use Group by clause in SQL, but can do the same when using a Table with Groups definition.
• I can use Casting in order to compare two values in SQL and also directly inside an expression.

and many more...

My questions are:

A. Which part (SQL query or SSRS) costs more time (assuming that the task can be made in both SSRS and SQL) ?

B. What are the guidelines, if any, on which I should base a decision when having a dillema where to execute the given situation?

Answer 1

As always with performance issues:

• Don't prematurely optimize. If something's simpler in SSRS then do it there. Only when a problem arises consider trading clarity for performance (possibly by moving code to the SQL side).
• Measure. Use the ExecutionLog2 view to get a general idea about where your bottle-necks are. Do more measuring and testing so you're sure you're investing time in improving performance of bits that matter.

Bottom line: let clarity of code guide where you solve a particular problem, and optimize selectively when performance becomes an issue.

---

Eric Lippert wrote a nice blog post about when and how to worry about performance. The context is C#, but the basic idea holds for other situations such as SSRS/SQL as well.

By the way, if you have a look at mentioned ExecutionLog2 view, you'll notice that there's in fact three components in performance you should know about:

1. Data retrieval (SQL)
2. Report model (transforming the dataset to an internal model)
3. Rendering (transforming the model into an XLS, PDF, etc)

Knowing in which part a bottle-neck lies is key to knowing how to solve a performance problem.

---

To end with a suggestion based on my experience:

As a rule of thumb, prefer SQL over SSRS if you're worried about performance, especially for aggregation. Also consider tuning your database (indexes and such) if needed.

This rule of thumb would be best if I could back it up by facts and research. Alas, I don't have any. I can say that in my own experience, most often when I had performance problems with reports moving aggregation and calculation from SSRS to SQL would help in solving this issue.