How to Load a Billion Rows in a WPF Datagrid

Support for large data sets is one of the most requested features for UI controls and one of our key investment areas. When dealing with large datasets, there are two critical things to consider: the memory footprint and the performance. The best way to solve this is with data virtualization - which allows you to have an overall view of the entire data set without needing it all physically in one location.

Common data virtualization solutions often include client-side pagination or cursor lists. These techniques, however, bring usability implications to users, and, in most cases, we wish to have the ease of scrolling the whole dataset like normal.

With our .NET 6 FlexGrid for WPF, we had effectively implemented solutions to quickly work with multiple millions of rows. Still, we discovered significant issues with performance and memory when it reached one billion rows.

So, we decided to push the performance limitations further by implementing new techniques and overcoming every tiny bottleneck to tackle this and allow any number of rows to be displayed in FlexGrid.

Ready to Load Your Own Billion Rows of Data? Download ComponentOne Now!

The first thing to consider when wanting to display that number of items is that the dataset cannot be all in memory. Creating a List<T> of 1 billion nulls generally takes around 15 seconds and consumes 8GB of memory. Therefore, it requires a data virtualization solution.

For this purpose, we provide the abstract C1VirtualDataCollection class, which is part of the ComponentOne DataCollection library. You can inherit this collection to provide your virtualized content. An example implementation looks like this:

    public class VirtualModeDataCollection : C1VirtualDataCollection<Customer>
    {
        public int TotalCount { get; set; } = 1_000_000_000;

        protected override async Task<Tuple<int, IReadOnlyList<Customer>>> GetPageAsync(int pageIndex, int startingIndex, int count, IReadOnlyList<SortDescription> sortDescriptions = null, FilterExpression filterExpression = null, CancellationToken cancellationToken = default(CancellationToken))
        {
            return new Tuple<int, IReadOnlyList<Customer>>(TotalCount, Enumerable.Range(startingIndex, count).Select(i => new Customer(i)).ToList());
        }
    }

To test the performance and memory footprint, we will create the collection and load the first page, which is similar to what happens when visualizing the collection in FlexGrid. And we will repeat the same for the previous collection and the one containing the improvements.

As you can see, the performance for loading the first page in the new version is unaltered, and the memory footprint is insignificant, even for the billion-items case. The latest version of C1VirtualDataCollection now uses a dynamic list internally that takes almost nothing to be created and uses minimal memory.

Skeleton Loading in FlexGrid

While most of the performance enhancements are within the DataCollection, there are some improvements in FlexGrid.

We took this opportunity to introduce another new feature in FlexGrid that helps with a perceived performance called Skeleton Loading. This feature shows an animated “skeleton” placeholder inside every cell, indicating the data is being loaded underneath. You can enable this feature in FlexGrid through behavior as seen below:

<c1:FlexGrid>
    <i:Interaction.Behaviors>
        <c1:SkeletonLoadingBehavior />
    </i:Interaction.Behaviors>
</c1:FlexGrid>

FlexGrid Overcomes WPF Layout Limits

Another improvement to FlexGrid was concerning rendering cells correctly for very large offsets.

As we worked out the scrolling performance in FlexGrid, we found some limits in the platform in terms of positioning the elements in the layout with huge numbers. We discovered that the content began to look displaced when rendering more than 2 million rows (30 pixels per row).

We reproduced this limitation with a ScrollViewer and simple TextBlocks. When the positioning goes past 60 million pixels or 2 million rows, the TextBlocks are positioned incorrectly.

So, we discovered that ScrollViewer is only good at positioning items up to a certain length, up to about 60 million pixels. After about 9 million rows (270 million pixels), the text stops rendering completely. If you are curious to try this yourself, you can download the sample here.

This limitation was overcome in FlexGrid 2022 v2 as we can go well beyond even 9 million rows to display up to one billion rows successfully. In theory, our WPF Datagrid can display more rows, but we stopped at one billion for this demonstration.

You can download the ComponentOne 2022 v2 update of WPF Edition to see the new FlexGrid Virtual Mode sample and see for yourself how it performs with one billion rows.

Ready to Load Your Own Billion Rows of Data? Download ComponentOne Now!