January 9, 2006 at 4:45 pm
Has anyone created custom paging with Reporting Services?
I have some reports that return half a million rows.
It looks as though RS is passing the whole dataset at the time of report execution, much in the same way a default datagrid works.
If this is the case, it will cripple IIS.
Is there something I can do to only return one page results or do I have to do custom paging within SQL Server to return only one page at a time to RS?
January 12, 2006 at 8:00 am
This was removed by the editor as SPAM
January 16, 2006 at 3:09 pm
I can't believe no one has created custom paging for RS...
March 16, 2008 at 11:10 am
To show in the screen, we used custom pagination. We retrieved from the SQL only that number of records, that has to be shown. The stored procedure was changed.
March 17, 2008 at 12:34 am
@gallan:
You will need to do your paging SQL-side. If you think about how Reporting Services is designed there's really no other way for it to work: your half-million row result set has to be stored somewhere if RS is to display it in pages for you. RS just uses a dotNET DataSet bound to some type of grid control, so those half-million rows are going to be in memory on the IIS box hosting RS. That'll probably crush a dedicated RS server, let alone one that's hosted on the SQL Server itself...
But it begs the question - what are you doing that requires a report outputting half-a-million rows? I can't imagine the kind of user who could possibly digest that much info in a meaningful way. And if they're simply sitting there paging through the results, eyeballing it surely they'd be better served with a more focussed query in the first place...?
Regards,
Jacob
March 17, 2008 at 6:46 am
Perhaps he's not displaying all half million rows, but using a summary or matrix to show totals by group. Even in this case, RS still has to handle the half million rows behind the scenese and it bogs down RS. So, I try to use one report that points to a SQL proc that returns only a summary. If I want to display details, I have the SQL proc have parameters that are required so that a reasonable number of details are returned.
This requires creative thinking and extra design time usually in how you design your reports. I would be interested in hearing more about this.
smv929
March 18, 2008 at 4:20 pm
Good ideas all. I would suggest "gallan" jump back in with some more details.
Some thoughts:
Thowing half a million rows at a matrix or summary probably isn't a great way to achieve a scalable solution. You'll be much further ahead if you create a MSAS cube.
Even if you don't want to go the extra step of creating a cube, there's got to be some sort of aggregation or filter (parameter) that would be in play here, either via SQL or a stored procedure. Not all of that work should be done on the front end, regardless of the technology.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply