How to use pagination for huge reports

Question

Post reply

How to use pagination for huge reports

SQL Guy 1

SSCoach

Points: 15979
More actions
December 28, 2023 at 11:29 pm

#4353762

Hi all,
I created a SSRS report that supposed to load about 600k records. In order to minimize loading time, I used pagination, by 50 records each page. However, when I refresh it in a Preview mode it throws "out of memory" exception.
I expected that if I use pagination it will only load 1st page for me, which should be very quick. And load the rest pages only if users request them.
Is there a setup in SSRS that will limit number of pages/rows loaded at each time when users refresh or go to the next page?
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Is your "report" going to return all 600 Thousand "records" or is it going do some form of aggregation?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

It's just a select from 3 tables, and 2 of them are with left join. For aggregations, I would have done it on SQL Server side. Now I am thinking to make pagination on server side, and present 2 parameters to users: batch size and batch number.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

SQL Guy 1 wrote:

It's just a select from 3 tables, and 2 of them are with left join. For aggregations, I would have done it on SQL Server side. Now I am thinking to make pagination on server side, and present 2 parameters to users: batch size and batch number.

How useful is a report that returns 600K 'records'? Nobody will ever be able to review everything included in that report.

Instead of trying to figure out how to make this run faster or page faster, I would recommend figuring out how to provide the users with what they actually need.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Jeffrey Williams wrote:

SQL Guy 1 wrote:
It's just a select from 3 tables, and 2 of them are with left join. For aggregations, I would have done it on SQL Server side. Now I am thinking to make pagination on server side, and present 2 parameters to users: batch size and batch number.
How useful is a report that returns 600K 'records'? Nobody will ever be able to review everything included in that report.
Instead of trying to figure out how to make this run faster or page faster, I would recommend figuring out how to provide the users with what they actually need.

To that very point, my suggestion was going be...

"Ask the users of this report what they're going to do with it because there may be a MUCH better and faster way to do it"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

SQL Guy 1 wrote:

I expected that if I use pagination it will only load 1st page for me, which should be very quick. And load the rest pages only if users request them.

The performance of a paginated query with a large result set can be significantly impacted by the absence of appropriate indexes. If there aren't indexes on the columns involved in the joins and sort operations, the query might be forced to perform full scans on the tables, leading to a noticeable performance hit.

Could you please check if there are suitable indexes on the columns used in joins and the sorting process? If not, adding indexes could greatly improve the query performance.

Additionally, have you already assessed the query's execution plan or run any performance diagnostics to identify potential bottlenecks? This could provide valuable insights into areas that might need optimization.