ContextQMD
Back to Devexpress

Pivot Grid Performance

corelibraries-403710-devexpress-pivot-grid-core-library-pivot-grid-performance.md

latest2.8 KB
Original Source

Pivot Grid Performance

  • Feb 07, 2022
  • 2 minutes to read

This article describes the Pivot Grid functionality that allows you to increase data processing speed.

First, you should estimate the amount of data the Pivot Grid processes to select a suitable data source:

  • Use an SQL Server data source if you have 150000 rows or less.
  • Use OLAP data sources if you have more than 150000 rows.

The threshold number of data rows depends on multiple circumstances, for example:

  • The number can be higher if you have powerful hardware.
  • The number can be lower if you store data in XML or Microsoft Access formats.

SQL Server

When you use SQL Server as a Pivot Grid data source, the application response time consists of the data load, calculation, and rendering times. To improve the data load time, you can do the following:

  • Establish a faster and more reliable connection between the data and web servers.
  • Place the data server on the same machine as the web server.
  • Tune your data server to achieve the best possible performance.
  • Cache retrieved data in the Session object.

You can optimize an SQL query to “pregroup” data on the server. For example, change the select Category, Product, Sales from Sales query to select Category, Product, sum(Sales) from Sales group by Category, Product. This optimization can greatly reduce the number of rows the Pivot Grid has to calculate.

OLAP

The OLAP data source does not reload and recalculate data on each request. The data source caches data and calculation results, and returns only the requested slice. OLAP data source performance is slower than SQL Server for small queries, but faster for large datasets (2-4 million rows).

Refer to the following platform-specific topics for more information on how to bind a Pivot Grid to OLAP data sources:

Database Server Mode

Server mode is designed to work with large datasets. In this mode, the Pivot Grid performs data-aware operations on the database server-side. Server mode ensures quick access to data, even if it is grouped and filtered.

Refer to the following platform-specific topics for more information about server mode: