“Blazing-Fast” Data Binding for Large VCL Datasets

Version 12.1 of the DevExpress VCL controls is now available. One exciting feature that customers have been anticipating is called “Server Mode” for the VCL grid. This feature, available in the .NET grid from DevExpress, allows the control to bind to huge amounts of data and work fully featured with that data while maintaining solid performance.

I recently explored this new feature in a small sample created using Delphi XE2. To start with, I created a new table, LotsaRows, in a Microsoft SQL Server database. I then used a simple script to populate the table with one million rows.

Armed with some sample data, I created my Delphi project. The project consists of four different grid configurations:

  1. A normal TDBGrid *no sorting, grouping, filtering
  2. A TcxGrid in “Grid Mode” (View.DataController.GridMode set to True) *no sorting, grouping, filtering
  3. A “Fully Featured” TcxGrid
  4. A TcxGrid using the new “Server Mode”

The “Server Mode” feature is comprised of a few different components. There is a new TcxGrid view as well as new datasources that handle the querying of data. In my sample I used a TdxServerModeADODataSource for connecting to Microsoft SQL Server and a TcxGridServerModeTableView for my grid.

Fetching the data for the first three grids (which use traditional datasources) takes around 6 seconds for all one million rows on my setup. Binding both the TDBGrid and the TcxGrid in “Grid Mode” is nearly instant. Binding one million records to a TcxGrid setup for all features (sorting, grouping, filtering) takes around 20 seconds for me. And, with one million rows, even after loading that data the sorting and grouping can be sluggish.

The fourth configuration, the TcxGrid in “Server Mode”, is nearly instant to fetch the ADO data (compared to 6 seconds previously). In addition, binding the data to the TcxGrid control takes only 150-175 milliseconds (compared to 20 seconds). The sorting, grouping, and filtering is all much faster than the third configuration as well.

So how is this magic accomplished? Once configured, the TcxGridServerModeTableView will only request the data it needs, via the TdxServerModeADODataSource, in order to render data on screen. Initially, enough data is queried from the datasource to populate the visible rows, as well as determine things like the scrollbar position. As the user begins to make use of the features of the grid, the combination of the grid view and datasource will only query the pieces of information needed in order to perform the operation and render the results. For instance, clicking a column to sort the contents will only query those rows needed to render the results on screen, using an appropriate ORDER BY clause.

You can run a trace on SQL Server in order to see what some of these SQL queries look like:

SELECT TOP 128 "ID", "Column1", "Column2", "Column3", "Column4" FROM "LotsaRows"   ORDER BY "Column2" asc, "ID" asc
SELECT "ID" FROM (SELECT "ID", ROW_NUMBER() OVER(ORDER BY "Column2" asc, "ID" asc) as "DXROWNUMBER" FROM "LotsaRows" )DXRESULTSET WHERE DXRESULTSET.DXROWNUMBER > 395100 AND DXRESULTSET.DXROWNUMBER <= 395100 + 4151
exec sp_executesql N'SELECT "ID", "Column1", "Column2", "Column3", "Column4" FROM "LotsaRows" WHERE ("ID" IN ((@P1), (@P2), (@P3), (@P4), (@P5), ..., (@P55)))  ',N'@P1 nvarchar(38),@P2 nvarchar(38),@P3 nvarchar(38),@P4 nvarchar(38),@P5 nvarchar(38),...,@P55 nvarchar(38)',N'{6C2B027D-46C0-44E7-99FF-B60FBFB06091}',N'{477745E8-8011-45A8-AB6C-B6280E74BD87}',N'{5E49139F-67D8-4D8B-8B6C-B633698223E9}'...

You can download the Delphi source and SQL scripts for the example here.

Advertisements

One thought on ““Blazing-Fast” Data Binding for Large VCL Datasets

  1. Pingback: Getting Started with Server Mode for ExpressQuantumGrid « Development Technobabble

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s