I'm developing a Web application and using the ASPXPivotGrid for crosstab capabilities. As the datasource I have SQL Server 2005 and a DataTable with about 20 fields.  In some user process  my query returns about 6 million records, when executing in my SQL Server Management Studio, the result is ok and in no more than 20 seconds. But when I'm trying to do the same in my aspx page, when the DataTable is loading such records, IIS process memory increase from 300 Mb to more than 1gb and then my page sends me an OutofMemoryException. I tried it with SqlDataSource and the same error occurs.

Is there another best approach for doing this?

Thanks in advance

Actually there isn't any easy way to do this. I'm trying to do the same thing with XtraPivotGrid on Windows Application but the result is still the same.

I was thinking to create an IList of items and let the items query itself from the MSSQL and return data with datareader then dispose itself but that didn't go well neither. That solution resulted RowCount * ColumnCount = nDataReaders and eventually it didn't do any good for memory and kept me waited for long time to return the query.

After tried every possible way, i've always been told to use Analysis Services. And i realise unless they do an implementation for datareader which helps pivot to calculate per row, it is not possible.

If you figure out a way please let me know.

Hello Andrés,

Please read these articles:

How to improve the ASPxPivotGrid performance:
http://www.devexpress.com/Support/Center/ViewKBIssue.aspx?kbid=K18104

A thread containing the discussion of this question with Serkan
http://www.devexpress.com/Support/Center/ViewIssue.aspx?issueid=Q135345

A solution to prevent ASPxPivotGrid from loading some columns from the datasource:
http://www.devexpress.com/Support/Center/CodeCentral/ViewExample.aspx?exampleId=E945