.NET PivotGrid and Dashboard: OLAP Performance Improvements

OK, let me state the blindingly obvious for a moment: the performance of aggregation or sorting operations (or both!) are key factors when you’re working with OLAP data sources. So, with v15.1, we took the time to do some research and design and have managed to increase the speed of the data engine used in the .NET Pivot Grid and Dashboard by optimizing some basic data processing – anything from constructing queries to the parsing of returned data.

Sorting is now performed on the client side. This allows you to speed up the process of visualizing data in complex scenarios such as sorting by summary and sorting in Tabular mode. Moreover, as an added bonus, using client sorting gives you the capability to perform custom sorting with the CustomServerModeSort events.

To compare the new Pivot Grid’s performance with other products in OLAP mode, we investigated two scenarios. With both scenarios, the pivot data and row areas display fields from the Adventure Works cube and are deployed on a local machine.

Pivot Grid Performance Improvements v15.1

 

Scenario PivotGrid v14.2 PivotGrid v15.1 Competitor 3 Competitor 4
#1 6 sec 3 sec 5 sec 4 sec
#2 1 min 20 sec 19 sec 35 sec 1 min 40 sec

The chart and table shown illustrate the performance improvements of the Pivot Grid in OLAP mode in v15.1 when sending queries to the Adventure Works cube. The data shows a comparison with the old v14.1 version and a couple of our (unnamed) competitors in this space. Note that the ADOMD provider was used to establish a connection to the OLAP cube.

Here is the data we used for each of the two scenarios.

Scenario 1

Data area: Internet Sales Amount, Internet Order Quantity
Row area: Postal Code, Product
Row count: ~ 100 000

Pivot Grid Performance Improvements Scenario 1

Scenario 2

Data area: Sales amount, Total Product Cost
Row area: Business Type, City, Product
Row count: ~ 600 000
Sorting: Product field is sorted by Total Product Cost

Pivot Grid Performance Improvements Scenario 2

I’m sure you’ll agree with me that these basic performance improvements will enhance the snappiness of the data analytics parts of your applications.

.NET data access engine–improvements in v15.1

Data access. Pretty mundane, wouldn’t you say? It’s … just there, doing its stuff, while you worry about how to best present the sea of data so that your users can glean the most important information from it. Well, in order to make your job even easier, we’ve made some major changes to our data access components in v15.1. Are you ready?

Query Builder improvements

It was in v14.1 that we first introduced our Data Access library to our product line. This library provides a set of tools intended to simplify the process of binding to different kinds of data sources.

Among this set of tools, I think the Query Builder reigns supreme. It's used by several wizards to provide you and (or?) your end users with a visual interface for constructing custom SQL queries. Unfortunately the Query Designer had some notable limitations from the outset (for example, it did not cover such SQL features as sorting and grouping).

To address this issue, we have made several improvements in v15.1 to the Query Builder’s functionality and usability, without forgetting increasing performance where necessary.

QueryBuilder v15.1: user experience and functionality

New features:

  • A columns list that allows you to preview the set of table columns before adding a table to a query.
  • Revised dialog interface.
  • Improved performance. Column information is now loaded on demand for each data table separately. The performance increase will be especially notable when binding to databases containing many tables with a massive number of columns (for example, when binding to Teradata – see below for information on this).
  • Support for aggregate functions.
  • Support for sorting. If the resulting data is sorted by more than one column, the order in which sorting is applied can be adjusted manually. Specified sorting settings correspond to the optional ORDER BY section of the resulting SQL string.
  • Support for grouping. Specified grouping settings correspond to the GROUP BY section of the resulting query string.

Adding the ability to sort, group and aggregate the data provides more comprehensive coverage of the data shaping functionality of SQL. When you apply such a shaping to a field, it will be marked with a corresponding icon, and this makes the whole process of shaping data more visual and intuitive.

Improved memory consumption

For 15.1 we have improved the internal structure of our SqlDataSource class, as well as the algorithms we use to obtain data from a database table. The upshot of this work is that we’ve drastically reduced its memory consumption in real-life scenarios.

To see the difference between the memory consumption of a SqlDataSource object (bound to the same database) between versions 15.1 and 14.2 , take a look at the chart below. This shows the aggregated profiling information we obtained as provided by the dotMemory profiler. This chart illustrates the dynamic aspect of memory allocation by SqlDataSource from the beginning to the end of the data loading process.

SqlDataSource: Memory Consumption improvements in v15.1

The raw profiling data was obtained from loading the same 3.5-million-row data table, consisting of 20 columns of differing data types.

As you can see, the memory usage has been reduced in v15.1 by about 65%. The improvement in memory consumption is even more pronounced when binding to data tables that contain mostly columns of numeric data types.

Teradata support

With v15.1 we have added support for Teradata (version 13 or later) to our Data Access library. Teradata is a massively parallel processing database management system that typically operates on extremely large amounts of data. Yes, you got it: we’re suddenly talking Big Data.

You can now find Teradata in the list of available data providers in the Data Source Wizard when creating a SQL data source. After that, the process of setting up a data connection will be the same as for any other SQL data provider: you need to specify the server address, the required database name, and the user credentials.  

Teradata support in the Data Source Wizard in v15.1

After a connection is set up, you can go to the next wizard page and construct an SQL query to obtain data from tables in the database. All the connection magic is hidden; you just use it as any other SQL data provider.

WinForms and WPF Charting–Performance improvements in v15.1

In all walks of programming life, performance matters. It is especially true when you are using third-party controls where you have no influence over how quickly the controls and their content renders on the screen. We agree and have always considered high performance as a vital feature of our products. The charting products are no exception: we are always on the lookout to makes our chart rendering engine faster and faster.

Throughout 2013 and 2014, we introduced data aggregation and other great features to our charts, which helped make the rendering of our ASP.NET, WinForms and WPF charts faster several times over, but for 2015 we faced a big challenge: what else can we improve? In v15.1, then, we spent some time analyzing our charting product for Windows applications, and have come up with a few new strategies to increase performance and, also, reduce memory consumption.

Here are some results from our internal tests.

WinForms app, Numeric data:

DataLoading Numeric: AddPoints

DataLoading Numeric: SeriesBinding

DataLoading Numeric: SeriesTemplate


WinForms app, DateTime data:

DataLoading DateTime: AddPoints

DataLoading DateTime: SeriesBinding

DataLoading DateTime: SeriesTemplate

I’m sure you’d agree, those are some really impressive results. If I were you, I’d download 15.1 now – it was released yesterday! – test the new version in your environment on your data, and share your results with us in the comments.

DevExpress Universal v15.1 has been released

If you are a customer with an active subscription, you should already have received an important email from us: that Universal v15.1 has been published to the Download Center and it’s ready for you to, well, download.

If you have been watching our community site here over the past couple of weeks, you will have realized that this is a release that is packed to the gills with new controls, new features, new improvements across the board, for all the .NET platforms we currently support. And of course we should not forget CodeRush and DevExtreme either. In essence, if your basis for development is Visual Studio, this release is almost overwhelming.

This whole week we have been also presenting live webinars on the new features, four down and two to go. Thank you to everyone who has attended the four so far – they’re on our YouTube channel if you want to watch them again or for the first time – and thank you if you’ll be there tomorrow and next Tuesday for the two remaining ones.

(Of course, if you’ve reached this point and are wondering about our VCL Subscription, rest assured we have not forgotten. Now that .NET is “out of the way”, we shall be talking about VCL starting next week. Stay tuned.)

UI Controls for Desktop, Web and Mobile: DevExpress v15.1

Normally at about this point in these announcement blog posts, I like to point out those features that have most impressed me about our new release. This time, I’m finding it most difficult indeed to limit the bullet list. For example, there’s:

  • The WPF report designer CTP and the new smooth document viewer
  • The pivot grid and scheduler in DevExtreme – like, wow!
  • The new Excel export engine (and the new Data Access engine, come to that)
  • High DPI support in WinForms and WPF – and, mirabile dictu, the RTL support in WinForms
  • Conditional formatting of data everywhere, it seems
  • The ASP.NET Runtime chart designer
  • The dramatic (as in order of magnitude) speed increase for the PDF document processor

And, so on, so forth. As I said, a really impressive release. The teams have really outdone themselves this time, so imagine how much more they’re going to have to do to impress us for v15.2 (cue the CTO evil laugh).

So, please do navigate over to the Download Center and grab the latest installer. You will not be disappointed. As always, if you do have some feedback for us, the support team are ready and waiting for your messages.

It’s Microsoft TechDays next week. Where? The Hague!

Thursday and Friday next week (that’s the 28th and 29th May to you), DevExpress will have a booth at Microsoft TechDays at the World Forum in Den Haag. We were there last year as well and liked it enough to come back this year as a Silver Sponsor! Present will be Don Wibier and myself doing the technical stuff – essentially a repeat of the Techorama dynamic from last week – and John Martin will be in charge of giving out the swag. And, well, in charge of us two also. Seriously, it was great fun last year and we’re excited to be back!

So, if you’re going to be there in The Hague, please do come over to the DevExpress booth. We’ll be happy to talk about our current product line and show off what’s coming up in v15.1. Also, given the recent news out of Build, you’ll get an excellent opportunity to ask us what we think about the possibilities for new features that face us in the second half of the year. You know: Windows Universal Apps, Windows 10, Visual Studio 2015, ASP.NET 5, etc, etc. Plus, remember that Don can speak Dutch like a native (because he is)so you can chat away with him and I’ll just stand there with a fixed smile and hand you a t-shirt. Mind you, Don is presenting a couple of sessions (“Breaking Bad: you CAN make Fast Web Pages” on Thursday at 15:00, and “Consuming Azure Mobile Services in a JavaScript App” on Friday at 13:30), so I’ll be taking charge of the booth during those hours.

Oh, and Rachel tells me that we will have a raffle as well, maybe even one each day. So you just have to come to the booth and grab a ticket. Maybe you’ll be the winner!

See you Thursday!

More Posts