//
You're reading...
Controls

How to load large dataset in DataGridView

Today I came here to discuss a popular industrial issue with a work around, which hopefully, you will find useful.

As the title depicts, we commonly have a large data set which needs to be display in DataGridView control and it is expected that the larger the dataset, the greater time it will take to load rows in control and UI may hang up.

I will discuss the ways adopted by the IT individuals and will create a benchmark on it. Also, I would end up with a very useful solution that will work for almost all .Net frameworks.

NOTE: I have more than 100,000 rows in the datatable which is used for testing, and this is the query I would use to demonstrate:

Dim query as string = “select top 10000 * from dbo.Logging where id > " & dt.Rows.Count

Here: `dt.Rows.Count` used as a filter to fetch next 10k rows. If required on the second run. All cases uses the same query

Now let’s have a closer look to the details:

Solution 1: Loads rows using For loop (Slower method)

Private Sub LoadMoreRows()

        Dim dt As DataTable = fillDataTable(CType(dgv.DataSource, DataTable))

 

        If (dgv.Columns.Count = 0) Then

            For col As Integer = 0 To dt.Columns.Count – 1

                dgv.Columns.Add(New DataGridViewTextBoxColumn()
With {.HeaderText = dt.Columns(col).ColumnName})

            Next

        End If

 

        For row As Integer = 0 To dt.Rows.Count – 1

            dgv.Rows.Add(dt.Rows(row).ItemArray)

        Next

End Sub

 

 

1st run: (approx 7 sec)

 

Start time : 30-03-2014 03:39:31.48069

End time   : 30-03-2014 03:39:38.51909

 

2nd run: (approx 8 sec)

 

Start time : 30-03-2014 03:39:50.13576

End time   : 30-03-2014 03:39:58.86226

 

Conclusion:  This method should not be used, because it hangs up the UI. We can overcome the hang up UI issue by using it with BackgroundWorker class. But why? If we have other options?

So, have look at another option.

Solution 2: Setting DataSource property: (A bit faster)

Private Sub LoadMoreRows()

        Dim dt As DataTable = fillDataTable(CType(dgv.DataSource, DataTable))

        dgv.DataSource = dt

End Sub

 

1st run:   ( approx. 4 sec)

 

Start time : 30-03-2014 03:54:17.50337

End time   : 30-03-2014 03:54:21.70761

 

2nd run: (approx.  0 sec)

Start time : 30-03-2014 03:54:34.16732

End time   : 30-03-2014 03:54:34.39033

 

 

 

Setting a datasource property was experienced a faster technique since instead of adding a single row every time
to the control’s row collection it binds the whole data once
.

Conclusion: So it CAN be use, and have an acceptable result.

 

Have you imagine what if the data source has large number of rows ?

 

 

Yes, assume if the datasource has say 100,000 rows and the above solution will load and bind 100,000 rows at once.

 

1st Run: (approx 2 sec)

 

Start time : 30-03-2014 04:02:06.28518

End time   : 30-03-2014 04:02:08.72932

 

Conslusion: It is good to set DataSource property to get result set quickly BUT it NOT good to load the whole dataset at once IF you have large number of rows. Time will go up with the number of rows have to be loaded (e.g, Table has 100k rows and all is going to be load at once). I would suggest to go ahead with the NEXT solution instead.

Solution 3: Read and Merge DataSource:

(Fast and optimized – Recommended)

 

The idea behind this solution is to fetch the data in chunks and merge this chunk in the already retrieved dataset.

Here is the source code:

Private Sub LoadMoreRows()

        Dim dt As DataTable = fillDataTable(CType(dgv.DataSource, DataTable))

        If dgv.DataSource IsNot Nothing Then

            CType(dgv.DataSource, DataTable).Merge(dt)

        Else

            dgv.DataSource = dt

        End If

        ‘ Scroll to last row (Optional)

        dgv.FirstDisplayedScrollingRowIndex = CType(dgv.DataSource, DataTable).Rows.Count

End Sub

 

1st Run: (approx 1 sec)

 

Start time : 30-03-2014 04:31:12.71807

End time   : 30-03-2014 04:31:13.13209

 

2nd Run: (approx 1 sec)

 

Start time : 30-03-2014 04:31:17.18933

End time   : 30-03-2014 04:31:17.67535

 

3rd Run: (approx 1 sec)

 

Start time : 30-03-2014 04:31:25.72281

End time   : 30-03-2014 04:31:26.30485

 

4th Run: (approx 1 sec)

 

Start time : 30-03-2014 04:31:31.73316

End time   : 30-03-2014 04:31:32.35919

 

Have you noticed you have 40,000 rows in datagridview (each run took 10,000 rows) and the fetch time is
always the SAME ?

Conclusion: It is wise to retrieve data in chunks and then bind with datagridview control USING MERGE, since with this way you have the next N records with no overhead and takes less than a second.

Hope you will find this post useful and going to use this in future. If you have any comments, ideas to make it more useful please feel free to come up.

Await your feedbacks!

Here is a screen shot of how it looks like:

demo

 

Advertisements

About Shahan Ayyub

Shahan is a Microsoft Certified Professional of .Net. Mostly self taught programmer and a quick learner, who just love to code and always eager to find unique solutions to the common problems. Currently working as an iOS developer.

Discussion

13 thoughts on “How to load large dataset in DataGridView

  1. Hello, Nice case study Shahan.
    How if you implement threading method if load large data.
    The main form will not Freeze right ?

    Posted by Ageng D Prastyawan | January 19, 2015, 10:59 am
    • Yes, for that BackgroundWorker class can be used. The DoWork() and RunWorkerCompleted() events can be used to fetch data from database but make sure the binding to the grid happens in the main thread.

      Posted by Shahan Ayyub | January 20, 2015, 7:33 pm
      • I use backgroundworker class and then the data is populate, but the vertical scroll bar don’t work.
        I don’t know why, my data is around 15K Rows, and the format is XML.
        do you have any suggestion how to load it efficiently ?
        🙂

        Posted by Ageng D Prastyawan | January 20, 2015, 10:42 pm
  2. Could you please provide sample data and snippet you have tried with. I will give it a look and try my best to solve your issue. Also, could you please confirm you have binded the merged datatable to your gridview ? if you did it then scrollbar should work and would assume you fetch in background and bind in main thread.

    Posted by Shahan Ayyub | January 21, 2015, 6:39 pm
  3. It is very good. But I am not getting one thing “fillDataTable” how this works using dataset.

    Posted by Carab | January 24, 2015, 5:39 pm
    • This post demonstrate the function with datatable (single). For dataset object, an additional step is required, you have to first order them to appear in a single grid or in a single DataTable object(which is not covered in this post), rest of the approach to combine the fetched result set with already fetched result set, remains the same.

      Posted by Shahan Ayyub | January 24, 2015, 8:04 pm
      • I have put this logic in tine interval of 5 sec for getting 1000 records at a time and calling merge existing data table.. It is working fine up to fetching 6000 records less than 30 sec. After that it hangs I have total 13k recs.I am using gridview c# code. what might be the problem?

        Posted by Sridhar | April 27, 2015, 9:41 am
      • Did you try to load the data in background ? Which line is the cause of hang up ? The call to server for data OR the data to bind to the gridview ?

        Posted by Shahan Ayyub | April 28, 2015, 7:32 pm
  4. Filldatable Is Not declared. it may Be inaccessible due to its protection level how to solve this please reply

    Posted by kaushik | February 18, 2017, 8:03 am

Trackbacks/Pingbacks

  1. Pingback: .NET DataGridView slow to display after loading large DataTable | Brentec I.T. Solutions Blog - April 17, 2015

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

%d bloggers like this: