CODEWITHSUNDEEP
reporting-services
KellyLynch
KellyLynch

Reputation: 251

SSRS and a report of 3,000,000 rows

I have SQL Server 2008 R2 with SSRS. I have created an SSRS report that may contain up to 3,000,000 rows.

When I tried to generate such huge report I saw the following picture:

An error has occurred during report processing. (rsProcessingAborted)
There is not enough space on the disk.

“There is not enough space on the disk.” – this was probably about the disk drive on that server where the Windows page file was mapped into. The drive had 14 GB of free space.

A NOTE: the report was not designed as a single-page report. It is divided on pages by 40 rows. When I try to generate the same report with 10,000 rows – it takes just 1 minute.

The question is: can this be fixed somehow?

Upvotes: 1

Views: 3160

Answers (1)

Jeroen
Jeroen

Reputation: 63729

SSRS is extremely ill-suited for this kind of scenario. Tools like BCP or SSIS seem much more suited to this task. The question you ask ("can my situation be fixed") is not quite answerable, apart from being answerable by you by demonstrating that it can be done.

In my experience though, I wouldn't think of trying to get 3 million rows to work in SSRS.

If you insist or are compelled to try anyways, here's a few things you can do to improve the situation:

  • Dive in to the rdl and remove everything you don't need: font instructions, dimensions, images, etc. Check regularly in the designer if the file is still valid. You could even consider rebuilding the report with as little extra features as possible.
  • Move any expression or dynamic ssrs bit to the query.
  • Remove all formatting and formats for cells.
  • Increase disk en memory space. If you are compelled to generate such big reports in SSRS you're gonna need it. Close any other application you can, your PC or Server's gonna need all the resources it can get for this. Normally I'd consider this a non option / as an indication you need different tooling though. But I'm beginning to sound like a broken record :)
  • Choose the export format wisely. Excel or PDF is gonna take many additional resources, e.g. CSV renderer will be much friendlier.

Upvotes: 2

Related Questions