SQL Reporting services - Out of Memory Exception

Question

I am having some 10 lac records in my single SQL Table. I need to load this much record in my record. I need to know whether this will load. when i tried loading to report its showing out of memory exception.

Answer 1

This is impossible to answer unless you expand your question. What language are you using? Which report generating framwork? How does the SQL query look like?

Edit: Ah, ok, Microsoft SQL Reporting Services. Well, it should easily handle queries on tables with millions of tuples, I'm sure. It all depends on how you have structured your query, so until you give us that we can't help you.

Answer 2

I am using SQL Reporting services. I need to load a reports with 100,000 records in my SQL table. When i am trying to access the report its telling the out of memory exception

Answer 3

Are you trying to display tens of thousands of records? What user would ever read that? Have you tried scheduling and emailing the report?

Answer 4

Reporting Services (and Cognos, Business Objects, and other BI reporting suites) generally have problems rendering reports that have hundreds of thousands of records or millions of records in the OUTPUT. Most of these systems don't have much of a problem aggregating the data into tens of thousands of records, but once you start going into the hundreds of thousands or millions, you will run into memory errors.

My recommendation is to NOT use Reporting Services for reports that are hundreds of thousands of rows. No person is going to read all the lines in the report. Heck, most of the BI suites won't even output the report if you try to render to Excel due to the 65,556 row limitation. I would recommend using SSIS for large raw data dumps, Analysis Services cubes if you want to allow the user to do exploratory ad hoc slice and dice analysis in Excel, or find ways to break it into smaller, more relevant data that can be consumed by a human -- meaning aggregated or filtered to a few hundred or thousand rows.

If you MUST use reporting services and you want to use it as a tool to get the data into Excel, then you could try rendering to CSV via a subscription. Again, I would recommend just building a SSIS package that does this instead since you won't have memory issues outputting multi-million row CSV files. But if you MUST use reporting services as the output tool, then minimize the memory cost by going with the least memory intensive rendering method.