Should aggregates be created in a temp table or CTE or there is any better way of doing it ?

Question

I want to know if aggregates should be created in a TEMP table or CTE in a SP ?

The requirement is to plot aggregated data with detail dimension data . I wanted to know if I m using SSRS as the front end and SQL server 2016 as the back end .Should I created my aggregates in a temp table or CTE and then join them with the dimension dataset in SP before plotting them on SSRS tabular report ?

I have 30 dimensions and 20 measures .The 20 new measures have to be computed and added to an existing SSRS report .

My data size is 38k records and can grow up to max 70K records.

Answer 1

With current information, it is hard to say to use which method. Both method is supported in SSRS dataset.

Personally, I tend to use temp table for performance , since your dataset is relatively big.

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I think you can create some views to achieve it.

Answer 3

Is it from a performance perspective you ask?

If so, then it is impossible for us to say.

Sometimes a cte is better since sql server can make "one query" of it all.

But sometimes it is better to break up one gigant query into several smaller queries, the results stored in temp tables, so the optimizer don't "choke" on the gigant query.

Answer 4

If the choice is between a temp table and a CTE - and this is for SSRS - then a CTE is the better option. SSRS and temp tables don't mix well - and can cause some issues when developing the reports.

If using a CTE causes performance issues then you may need to resort to temp tables and figuring out how to make sure SSRS works with that procedure.