Here’s an issue that stumped me for a few hours, and finally, the problem has been resolved!
The end goal was to display the entire contents of the SQL agent log in a datagridview (dgv) control. While getting the data into this control and displaying it was not hard, upon rendering the form with the dgv, it would take forever to redraw causing the remainder of the application to appear as “Not Responding”, and depending on the size of the log, could take near 1 minute for the application to be responsive again, clearly undesirable.
So I took to “the Google” and found that many others have had the same issue; good, this would be a quick fix. At least that is what I thought initially.
Almost everything that I came across mentioned that I should turn on the property VirtualMode=True, which depending on how one wants to present the data, in “chunks”, could be a viable option, however, I wanted to show all of the log entries at once, so that the end-user could have a better and more complete search experience. While I did try this, the end result was the same, an unresponsive UI, so I changed the virtual mode back to false.
Continuing my search, other solutions involved loading the data in chunks and presenting the chunks in smaller datasets and merging them after. When I came across this method, I was thinking that the solution presented was pretty good and could solve the problem. While this method could work for you, I still wanted to have a search experience that would search all log entries, not just a subset of the log, which would be the experience if I relied on the user to manually load more data.
Next on the hit-list, again from searching, was to invoke a BackgroundWorker thread to load and present the data. Actually, this was the most suggested solution that I could find on the net. While this was implemented and it did reduce the time whereby the application was unresponsive, when it came to presenting the data into the dgv, it continued to bog down the application. So at this point I knew the problem wasn’t from loading the log into a datatable control, but upon presentation of the data that was causing the UI to freeze.
At this point, my frustration was growing, so I took a break for the day and decided to look into it at another time, but it was sitting in the back of my head that there had to be a viable solution. How do other applications showing 100’s of thousands of rows in dgv’s not have this issue? Loading the data into a DataTable and binding it to the DataSource of the dgv from what I’ve researched was the quickest way of doing this instead of cycling through row-by-row in a while loop and adding each row individually to the dgv. So I was firm on keeping this method of gathering and presenting the data to the application, but it still froze the UI.
So my conundrum of a slow/freezing UI went on, and I continued to search for many topics and clicked on many, many links to no avail. I eventually decided to search for “slow performance datagridview large data” and came to this page (which I must give credit). Usually for StackOverflow results, I scroll down to the answer that has been crowd-sourced as the “right” solution for the problem at-hand and has the noticeable green check beside it. The community is typically correct in their assessment of the solutions posted to these types of sites, and in most cases the marked solution works for the majority. In my case, the solution didn’t solve the problem, but for some reason on this article I continued reading, and the answer was right there directly below the marked “solution”…”make sure u dont autosize columns, it improves performance.”
Bingo! That was it. I immediately commented my code whereby I was dynamically setting my column widths with AutoSizeMode = Fill, and ensured that the column properties in the dgv were configured such that AutoSizeMode was NotSet. I recompiled, brought up the form, and bam, the log with 40K rows which was taking near 1 minute originally, now was displaying back to the user in 8 seconds, a considerable improvement, and all without causing my application to appear frozen or not responding; a win in my books. This change coupled with the BackgroundWorker saved the day.
Hopefully my experience can help you in the performance of your dgv’s when presenting 1000’s, 10’s of thousands or 100’s of thousands of rows.