How do I improve DataGridView Performance?

My problem lies in the DataGridView.RowCount. If RowCount was originally 1000, then I change it to 300, I experience major sluggishness. But please read my scenario below first to know when it happens exactly.

SCENARIO:

I have a DataGridView that displays data in Virtual mode. I used the Virtual mode example from MSDN (http://msdn2.microsoft.com/en-us/library/2b177d6d.aspx). I use the same DataGridView to display a list of cars filtered by their color.

e.g. Filtering by the color Red returns 500 cars;
Blue returns 500 cars;
Yellow returns 500 cars;
All Colors returns 1500 cars
* when I say "returns 500 cars", that's only the virtual row count, not the actual number of rows returned.

The filtering is done thru my SQL statement (which is already as optimized and as fast as possible).

All is working great and response time was negligible.  That is until I tried getting All Colors, scrolled all the way down to the bottom of the DataGridView, and then switched to view only a particular color of a car. Suddenly, my response time look quite a bit longer.

NOTE, I experience this if the number of rows started at a large number before switching to fewer rows. Scrolling down increases the wait time even more.

MY OBSERVATIONS
This slowdown occurs the moment I change the value of DataGridView.RowCount (e.g. 1000) to a smaller value (e.g 300). I noticed that the excess 700 rows all become unshared (I watch the RowUnshared event to check when this happens) one by one in reverse order, that is, row number 1000 becomes unshared, then 999, etc, as if the DataGridView was removing each row one by one. I dunno if that's what the DataGridView really does or that unshared rows has anything to do with my particular problem but I did read in MSDN (http://msdn2.microsoft.com/en-us/library/ha5xt0d9.aspx) that unshared rows can affect performance.

MY ATTEMPTS TO SOLVE IT
I've tried only one thing so far... I detach all the event handlers for my DataGridView (e.g. CellValueNeeded, SelectionChanged, etc.) before changing the data (before setting RowCount), and reattach them again once my data is all set (after setting RowCount). Only a slight improvement.

Hope you can help me.... Thanks :)
tomatausAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobbPCommented:
Hi,

Are you using datasets as the source ie disconnected, seems to me your getting lag because your connecting to the db every time you query, if you use a disconnected datasate as your source for the datagridview - you can also filter the dataset.
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.aspx
0
tomatausAuthor Commented:
Hi,

Thanks for the comment.

I am using disconnected datasets, but my datagrid does not bind to the dataset itself. The data is only shown on the datagrid when the event CellValueNeeded is called. In fact, I have no problems with the speed when connecting to the database. In fact, it is faster when my code retrieves data from the db every time I need a new page or when changing filters compared to setting the RowCount to a smaller value.

My code looks something like this:

// Refer to the article from msdn for the cache 
// class in my first post under "SCENARIO"
 
// I've also modified the cache so it has the RowCount 
// property which is the total records found that meet 
// the filter criteria. It is not the actual row's retrieved.
 
// I've also modified the DataPageRetrieved to be able
// to get more than just 2 pages.
private Cache _cache;
 
 
// Event I need to handle in order to display 
// data in Virtual mode
private void carDataGridView_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
{
  // Get the value from the cache that will be 
  // displayed in the cell
  e.Value = _cache.RetrieveElement(e.RowIndex, e.ColumnIndex);
}
 
 
// Function called to get a new list of cars based 
// on color or all colors
public void GetCarList(string color)
{
  DetachAllDataGridEventHandlers(); // just to help improve performance
 
  // Get new cache of car list with 50 records per page. 
  // There are 3 pages worth of data retrieved.
  _cache = CreateCarListCache(color, 50); // EXECUTES FAST
 
  // This next line of code executes slowly if orginal 
  // value of RowCount was way bigger than the new RowCount,
  // e.g. from 1000 to 300
  carDataGridView.RowCount = _cache.RowCount; // EXECUTES SLOWLY
  // Remember, CellValueNeeded is never called at this
  // point because it's detached
 
  AttachAllDataGridEventHandlers();
  carDataGridView.Refresh();
  // All data is now refreshed to reflect new data
  // because CellValueNeeded is now attached
}

Open in new window

0
tomatausAuthor Commented:
Ooops. My comment in line 8 of the code should be DataPageRetriever, not DataPageRetrieved.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

SwissKnifeCommented:
Hello tomataus

The property carDataGridView.RowCount only sets the number of rows to display. If you change from 1000 to 300 rows, 700 rows are deleted one by one, and 300 old rows are kept.
If you add another 300 rows, the kept roes are deleted also.
I guess you should clear the rows collection with carDataGridView.Rows.Clear() to get rid of all rows at once.

Here is a link to the RowCount property:
http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridview.rowcount.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomatausAuthor Commented:
Oh my god! Why didn't I think of that? It's perfect.
0
tomatausAuthor Commented:
As it shows, sometimes the solution is so obscure, yet the best solution is really the simplest one.
This one works great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.