CODEWITHSUNDEEP
c#exceldatagridviewfiltering
Iason
Iason

Reputation: 249

Implement filtering like excel (performance wise) in large datagridview datasource

This question comes as a separate question from Suggestions for more efficient filtering on large datagridview datasources. So right now my filtering is slow on large datasources (220k rows). The way I filter is by binding source:

mBindingSource.Filter = Filter;

This is slow because it possibly goes through all the rows of the datatable.

When I try to filter the same size of data in Excel, the filtering is many times faster. I would like to know whether someone knows, or can point me to the right direction as to how Excel implements its filtering. Maybe a snippet of code could also help.

Upvotes: 1

Views: 1343

Answers (1)

Pabinator
Pabinator

Reputation: 1641

Once you have a dataGridView full of rows it is very fast to filter. Your problem is probably that you are reloading the dataGridView before you are applying the filter.

I did a test with a database with +800K rows and it filters in less than a second (Maybe Excel will take longer).

This is how I tested:

Take the table from the Data Sources window and drag it and drop it on your Form. In my case I'm taking a table called Products.

Products dataGridView

That creates a dataGridView on your Form named productsDataGridView so the CS code was like this:

private void button1_Click(object sender, EventArgs e)
{
    BindingSource bs = (BindingSource)productsDataGridView.DataSource;

    bs.Filter = string.Format("SerialNumber Like  '%{0}%'", textBox1.Text);

    productsDataGridView.DataSource = bs;
}

In my case, every time I press the button1 the productsDataGridView that has more than 820,000 rows was filtered in less than a second.

I hope this helps you debug your code.

Upvotes: 2

Related Questions