Clever Geek Handbook

Where to do it? SSRS OR SQL

When I create an SSRS report, I always have a dilemma "how to create a report with minimal generation time".

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

  • SQL Query.
  • Report components (expressions, groups, etc.).

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

For example:

  • I can use a Group by

    statement in SQL, but I can do the same when using a table definition with groups.
  • I can use Casting

    to compare two values ​​in SQL as well as directly inside an expression.

and much more...

My questions:

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

B. What are the guiding principles, if any, on which I should base my decision in the presence of dillema, where to implement the given situation?

+3


source to share


2 answers


As always with performance issues:

  • Should not be optimized prematurely. If anything simpler in SSRS, do it there. Only when a problem arises, consider trading clarity for performance (perhaps by moving the code to the SQL side).
  • Measure. Use the ExecutionLog2 view


    to get a general idea of ​​where your bottles are. Do more measurement and testing, so you're sure you're investing time in improving the performance of the bits that matter.

Bottom line : keep the code guide clear where you are solving a specific problem, and optimize selectively when performance becomes an issue.

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

By the way, if you look at ExecutionLog2


, you will notice that there are actually three performance components you should be aware of:
  • Data Search (SQL)
  • Report Model (converting dataset to internal model)
  • Rendering (convert model to XLS, PDF, etc.)

Knowing which part the spoon of the bottle is in is the key to understanding how to solve a performance issue.

To complete a sentence based on my experience:

Generally prefers SQL over SSRS if performance concerns you, especially for aggregation. Also consider tweaking your database (indexes, etc.) if needed.

This rule of thumb would be better if I could support his facts and research. Alas, I don't have them. I can say that in my own experience, more often than not when I had performance issues related to converting reports and calculating from SSRS to SQL, it would help solve this problem.

+5


source


It's important to remember that SSRS is smart and keeps the exception until you need it. If you export, it will fetch all data. Also, if you are browsing online and you expand and collapse lines, they will not execute until you want to view it. In this theory, basic SQL is preferred.

Your best bet is to leave the aggregation in SSRS as the report will try to aggregate anyway in the tablix. As far as charts go, it's best to fill them out if you don't have a tab either.

As far as simple calculations go, this should be done in SQL, like comparisons, etc.



Remember SSRS is smarter than you;) and the simplest SQL allows this service to perform best and this service is primarily for display.

If you are using MS SQL Server for dataset, the service will work at its best.

+1


source






More articles:


All Articles