Ticket T687056
Visible to All Users

Ways to improve Pivot Grid List Editor or PivotChart performance with large amounts of data

created 6 years ago (modified 6 years ago)

I have a window application project developed by ExpressApp Framework.
This project was developing for calculating export and import product quantity , price and etc.
Then I use PrivotGridListEditor to display those data that have more than 200,000 records.
But Now, I'm getting the issue is that the performance is slow much (More than 5 minutes till It occurs).

I'm finding a way to tune my application's performance.
It would be good , If I can find some recommendation here.
Thanks.

Answers approved by DevExpress Support

created 6 years ago (modified 6 years ago)

Hello,

Thank you for your report. As you probably know, our pivot grids do not support server mode for XPO, unlike regular grids. In your particular case, all 200K persistent objects (with all the references, collections and other related data) are loaded and processed in memory, which requires a lot of time, as expected.

Having said that, the best solution here is to avoid loading the entire object graph in memory and process data on the database server, if possible.

That is all achievable using custom various solutions:
1.  Set DataAccessMode = DataView for your ListView and leave only visible columns under the ListView | Columns node in the Model Editor. If you use the PivotChart module, invoke the Application Designer for the YourSolutionName.Wxx/WxxApplication.xx file and the DevExpress.ExpressApp.PivotChart > PivotChartModuleBase > DataAccessMode property to DataView. The benefit of the DataView mode here is that it allows you to avoid loading entire persistent object instances. Instead, you can load only required scalar data values from the database server, including aggregate values specified via criteria expressions, which will then be processed by the visual control. As a result, you can configure a pivot table that loads and performs faster. See also: Concepts > UI Construction > Views > List View Data Access Modes Overview.
2.  Specify a criterion to further limit the amount of loaded data: Concepts > Filtering > Criteria Property of a List View's Collection Source.
3.  Additionally use the solution from the How to set PivotGridControl.OptionsBehavior.UseAsyncMode to True for the first data load thread for the best UX.
4.  If our Pivot Grid List Editors performance does not meet you requirements at this stage, consider binding them to a specially prepared custom data source:
    4.1.  Create a database View based on an highly optimized SQL query that returns required analysis results. Map a persistent class to this database View as per  How to: Map a Persistent Class to a Database View Which Has No Key Field. This approach preserves most of the standard Pivot Grid List Editors functionality and is the most reliable in this solution category.
    4.2.  Bind the underlying pivot grid control to a required custom data source at runtime. You can create a ViewController or PivotGridListEditor descendant and override the default data binding. For instance, use the standard ADO.NET DataSet, XPO XPDataView or rather LinqServerModeSource. At runtime, you will be able to retrieve the queryable source (XPQuery) using the IObjectSpace.GetObjectsQuery method and pass it to the Server Mode component as described at T233255. If you use the PivotChart module, refer to the Q361155 ticket for an example. Bear in mind that LinqServerModeSource is not specially designed for XPO data and will not support certain XPO criteria expressions and other features. Our Pivot Grid List Editors ans PivotChart analysis editors were not specially designed for custom data sources and may require additional testing and configuration in this scenario. Implement it at your own risk.
5.   If our built-in editors do not meet your needs completely, consider one of the following popular options:
    5.1. Implement a fully custom user control or form to manage your data and the resulting display as your business requirements dictate. There, you can you use direct SQL, OLAP, server mode for EF or LINQ to SQL as described in the pivot grid documentation: OLAP Data SourceDatabase Server Mode, etc.,  exactly as you would do this without XAF. See also: Concepts > UI Construction > Using a Custom Control that is not Integrated by Default.
    5.2 Use our Dashboards Module that implements the same solution ideas slightly differently: Dashboard Performance With Large Data Sources.

Regardless of the data representation method you use, you may also want to tune your database or SQL query performance. This general documentation may help in this regard: How to measure and improve the application's performance | Database maintenance recommendations for applications created or used with XPO or XAF. Let me know once you have had an opportunity to research and test these options in your project or if there are any difficulties as you go.

Search keywords
pivot, PivotGridControl, ASPxPivotGrid, performance, speed, memory, slow, long, slowness, PivotGridListEditor, ASPxPivotGridListEditor, server mode, async, a lot of data, huge, big

    Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

    Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.