I am using ADO.NET under ASP.NET to retrieve a drill through in a web
application. I am directly connecting to the Data Server. The problem is
that I am getting memory errors because it seems to want to put all of the
rows into memory on my web server. Regardless of whether I use a Dataset
with a DataAdapter or a DataReader it blows up while retrieving to row set.
It works fine when I add the MAXROWS to the MDX statement and limit the
return set to the first 1000 rows. But when I am trying to retrieve 40000
rows it blows up with the error:

aspnet_wp.exe (PID: 3492) was recycled because memory consumption exceeded
the 1200 MB (60 percent of available RAM).

I’ve even adjusted ASP.NET memory limits and I still get the same error.

I want to retrieve row 30000 through 30099. In the data adapter fill method
I’ve the ability to indicate start at 30000 and return 100 rows. It still
blows up because it seems to wants to copy all of the returned rows into
memory first before selecting the 100 rows I want.

Is there anyway to force ADO.NET to keep the resulting row set on the
database server returning only the rows I want or Is there anyway to page the
return results of a drill through MDX statement (specifying the starting row
and the number of rows)?

Is there any other way to deal with this problem. My client wants to be
able to view all of the rows (paged) returned by a drill through and have the
option of dumping them into excel.

Thank You
Joel Isenstadt

you are in the right way, you'll read rows up to 30099, but don't fill the
dataset using the default functions.
fill the dataset by your self, read and discard the rows until you reach the
row 30000, then insert the next 100 rows into the dataset.

another option is to store the result in a temporary file, then display the
rows required from this temp. file when required.
on the first access you execute the drill, then store the result in a CSV
file, and finally render only required rows.
create your own data object to simplify these accesses.


"JIsenstadt" <JIsenstadt (AT) discussions (DOT) microsoft.com> wrote