PivotGrid with large dataset

12 posts, 1 answers
  1. Alex Dybenko
    Alex Dybenko avatar
    119 posts
    Member since:
    Jan 2005

    Posted 04 Jul Link to this post

    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

     

  2. Hristo
    Admin
    Hristo avatar
    716 posts

    Posted 05 Jul Link to this post

    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
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms. For more information check out this blog post and share your thoughts.
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Alex Dybenko
    Alex Dybenko avatar
    119 posts
    Member since:
    Jan 2005

    Posted 05 Jul in reply to Hristo Link to this post

    Hi Hristo,

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

    Thanks

    Alex

     

  5. Hristo
    Admin
    Hristo avatar
    716 posts

    Posted 06 Jul Link to this post

    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
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms. For more information check out this blog post and share your thoughts.
  6. Alex Dybenko
    Alex Dybenko avatar
    119 posts
    Member since:
    Jan 2005

    Posted 08 Jul in reply to Hristo Link to this post

    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

     

  7. Answer
    Hristo
    Admin
    Hristo avatar
    716 posts

    Posted 11 Jul Link to this post

    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:
    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
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms. For more information check out this blog post and share your thoughts.
  8. Alex Dybenko
    Alex Dybenko avatar
    119 posts
    Member since:
    Jan 2005

    Posted 12 Jul in reply to Hristo Link to this post

    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

     

  9. Hristo
    Admin
    Hristo avatar
    716 posts

    Posted 13 Jul Link to this post

    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
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms. For more information check out this blog post and share your thoughts.
  10. Alex Dybenko
    Alex Dybenko avatar
    119 posts
    Member since:
    Jan 2005

    Posted 15 Jul Link to this post

    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

     


  11. Hristo
    Admin
    Hristo avatar
    716 posts

    Posted 15 Jul Link to this post

    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
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms. For more information check out this blog post and share your thoughts.
  12. Alex Dybenko
    Alex Dybenko avatar
    119 posts
    Member since:
    Jan 2005

    Posted 05 Aug in reply to Hristo Link to this post

    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

  13. Hristo
    Admin
    Hristo avatar
    716 posts

    Posted 08 Aug Link to this post

    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
    Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms. For more information check out this blog post and share your thoughts.
Back to Top
UI for WinForms is Visual Studio 2017 Ready