PivotGrid with large dataset

[Alex Dybenko]

Hi,

i have got System.OutOfMemoryException while assigning dataset with 3+ milllion records to PivotGrid. I understand that this is sql driver memory limitation, but how do you suggest to solve it besides using analysis services?

 

Thanks

Alex

 

[Hristo]

Hello Alex,

Thank you for writing.

We have a data provider which can bind to any collection implementing the IQueryable interface. Since cubes are not suitable for your particular scenario you can try RadPivotGrid`s QueryableDataProvider. Detailed information is available here: http://docs.telerik.com/devtools/winforms/pivotgrid/populating-with-data/using-the-queryabledataprovider.

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo Merdjanov
Telerik

[Alex Dybenko]

Hi Hristo,

yes, this what also thought about, perhaps you have some sample project, which uses QueryableDataProvider, to start with?

Thanks

Alex

 

[Hristo]

Hello Alex,

You can basically follow the example as demonstrated here: QueryableDataProvider.

I have also set up a sample project which should get you going. 

I hope this helps. Please let me know if you need further assistance.

Regards,
Hristo Merdjanov
Telerik by Progress

[Alex Dybenko]

Hi Hristo,

i got the  same out of memory exception running it on my large database at
this.ordersTableAdapter.Fill(this.nwindDataSet.Orders);

I understand it should be a special TableAdapter/Dataset for that purpose, or something else, maybe you can give me some idea how to start with it?

Thanks

Alex

 

[Hristo]

Hi Alex,

Thank you for writing.

The exception is raised because calling the fill method of a table adapter would eventually fetch all data locally. From that moment all of your queries will run against objects which are already in the memory: http://stackoverflow.com/questions/4968664/return-iqueryable-datatable-before-sql-execution

In case you are able to optimize the data in your database or your query you could work with a local provider:

• http://www.codeproject.com/Questions/330290/System-OutOfMemoryException-to-fill-the-dataadapte
• http://stackoverflow.com/questions/5092510/system-outofmemoryexception-when-filling-dataadapter
  

If optimizing your query is not possible and you need to work with all of the data you would need to use an IQueryable collection. I am sending you attached a sample project using Entity Framework and the Northwind database. In order to run the project, you would need to restore the database and if necessary modify the connection string. Please check if a similar approach would fit your actual project.

I hope this helps. Please let me know if you need further assistance.

Regards,
Hristo Merdjanov
Telerik by Progress

[Alex Dybenko]

Hi Hristo,

thank you very much, excatly what I need!

One more question: when I add/remove fields with this method I do not see "wait" animation while SQL Server process query, is it possible so see it as when PivodGrid process data locally?

Thanks

Alex

 

[Hristo]

Hi Alex,

Thank you for writing back.

I am glad that the suggested approach fits your local setup. Regarding your question, I am not sure that I completely understand it. The built-in waiting animation is displaying fine on my end. In case you keep experiencing this issue please open up a support ticket and send us your project.

Additionally, you may check the StatusChanged event of the QueryableDataProvider. It reports the status of the update operation.

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo Merdjanov
Telerik by Progress

[Alex Dybenko]

Hi Hristo,
you can try with your sample, if you add several millions records to [Order details], you see that while SQL server process query it do not show waiting animation, but it shows animation when SQL server batch is completed and PivotGriv works with local data.

I also tried to put custom wait animation in StatusChanged event, it shows animation, but do not run (animate) it. As I understand it should in separate thread in order to run it...

Alex

 

[Hristo]

Hello Alex,

Thank you for writing.

Indeed, there is no animation indication while the query is being processed on the server. The busy indicator which I referred in my previous post is handled locally. 

The experienced behavior is caused by the QuearyableDataProvider which is currently blocking the UI thread. I have logged a feature request on our feedback portal. You can track its progress, subscribe for status changes and add your vote/comment to it on the following link -  IMPROVE. RadPivotGrid - QueryableDataProvider should not block the UI thread. I have also updated your Telerik points.

I hope this information was useful. Should you have further questions please do not hesitate to write back.

Regards,
Hristo Merdjanov
Telerik by Progress

[Alex Dybenko]

Hi,

back to "sample project using Entity Framework and the Northwind database" - works fine, but I need to define entity set in designer. What if I want to use ad-hoc query, say, user makes a view and wants to run PivotGrid on it? Is it possible to make QueryableDataProvider work on ad-hoc SQL?

Thanks

Alex

[Hristo]

Hi Alex,

Thank you for writing back.

As discussed previously, the QueryableDataProvider can bind to anything implementing the IQueryable interface. In this respect, you are free to choose whatever approach fits best your local scenario and fill such a collection. 

A similar question is discussed here: http://stackoverflow.com/questions/26143571/using-sqlquery-to-get-iqueryable. You can also check our KB resource demonstrating how you can bind RadGridView in Entity Framework using code first approach: http://www.telerik.com/support/kb/winforms/gridview/details/binding-radgridview-to-entity-framework-using-code-first-approach. This solution will give you control on your models and the LINQ queries you execute.

I hope this helps. Please let me know if you need further questions.

Regards,
Hristo Merdjanov
Telerik by Progress