Very long data display time in a DataGridView

Question

Hello,

I'm developing an application with WinForms in Windows10, and I'm using a DataGridView to display data from a local database with SQLite.
The problem is, when I want to display more than 100 items, my HMI freezes for a few minutes before displaying the data.

Below is my code:

string requeteAllMovies = "SELECT * FROM movies";

SQLiteCommand getAllMovies = new SQLiteCommand(requeteAllMovies, connection);
getAllMovies.ExecuteNonQuery();

SQLiteDataAdapter adapter = new SQLiteDataAdapter(getAllMovies);
DataSet dataSt = new DataSet();
adapter.Fill(dataSt, "movies");

dataGridViewMovies.DataSource = dataSt.Tables["Movies"];

dataGridViewMovies.Enabled = true;

dataGridViewMovies.AutoResizeColumns((DataGridViewAutoSizeColumnsMode)DataGridViewAutoSizeColumnsMode.AllCells);
dataGridViewMovies.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridViewMovies.Columns[0].Width = 70;

Also, in this dataGridView, I put colors on the headers of some columns and bold the title of the columns, which also makes the data load slower.

dataGridViewMovies.EnableHeadersVisualStyles = false;

foreach (Tuple<string, string> nameColumn in listTupleNameColumnAndNameIHM)
            {
                dataGridViewMovies.Columns[nameColumn.Item1].HeaderCell.Style.BackColor = Color.FromArgb(153, 255, 51); 
dataGridViewMovies.Columns[nameColumn.Item1].HeaderCell.Style.Font = new Font("Microsoft Sans Serif", 8.25F, FontStyle.Bold);
dataGridViewMovies.Columns[nameColumn.Item1].HeaderCell.ToolTipText = nameColumn.Item2;
dataGridViewMovies.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            }

Would you know how to speed up data load time and display it quickly on the dataGridView?

Thanks.

Answer 1

First off, no need for getAllMovies.ExecuteNonQuery();.

In regards to slow over 100 records, comment out all formatting, run the app, is there still slowness? If so than focus on this aspect for speeding things up. I'd expect say 5,000 records should load in say one second.

Next up, formatting, I wrote a small extension method which mocks up your column formatting except for column resizing on 7,584 rows, took less than the blink of an eye.

using System.Collections.Generic;
using System.Drawing;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ValidatingFilesApplication.Classes
{
    public static class DataGridViewHelpers
    {
        private static readonly List<string> columnNames = new List<string>()
        {
            "cdatetime", 
            "district", 
            "latitude", 
            "longitude"
        };

        public static async Task FormatColumns(this DataGridView sender)
        {
            foreach (var name in columnNames)
            {
                sender.Columns[name].HeaderCell.Style.BackColor = 
                    Color.FromArgb(153, 255, 51);

                sender.Columns[name].HeaderCell.Style.Font = 
                    new Font("Microsoft Sans Serif", 8.25F, FontStyle.Bold);

                sender.Columns[name].ToolTipText = "Hello";

                await Task.Delay(1);
            }

            sender.ColumnHeadersDefaultCellStyle.Alignment = 
                DataGridViewContentAlignment.MiddleCenter;
        }
    }
}

The following does column resizing and is slow (which is expected) but visually one can see what's going on. Now we can circumvent this by figuring out a head of time what the max width should be and set it before loading data.

This code would be placed in the class above

public static async Task ExpandColumnsAsync(this DataGridView sender)
{
    /*
     * the following works well with a few columns but with a many columns and rows will be
     * slow
     */
    //sender.Columns.Cast<DataGridViewColumn>().ToList().ForEach(col => col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells);


    foreach (var column in sender.Columns.Cast<DataGridViewColumn>())
    {
        column.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
        await Task.Delay(1);
    }
}

Calling the code (note SuspendLayout and ResumeLayout which really does not speed thing up much in the big picture but is worth it)

private async void OnShown(object sender, EventArgs e)
{
    // ensure button is not grey out
    await Task.Delay(1);

    dataGridView1.DataSource = _table;

    try
    {
        dataGridView1.SuspendLayout();
        await dataGridView1.FormatColumns();
        await dataGridView1.ExpandColumnsAsync();
    }
    catch (Exception)
    {
        // ignored fringe case, user closed form before finishing ExpandColumnsAsync
    }
    finally
    {
        dataGridView1.ResumeLayout();
        dataGridView1.ScrollBars = ScrollBars.Both;
    }

}

Last words

• Loading 100+ rows from a database should be instantaneously
• Formatting columns takes time but with the code above will not freeze up the app very long, in my case just under 8,000 records it takes three seconds.