DataGridView Sorting Slow.-VBForums
Results 1 to 8 of 8

Thread: DataGridView Sorting Slow.

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Posts
    121

    DataGridView Sorting Slow.

    When my program is being used by a real user, using filtering to reduce the number of rows to a sensible/useful number, mostly just a few dozen rows, sorting isn't really a problem in the real world.

    But during development, performance testing with larger sets of data, I notice some strange slowness in the sorting when clicking on the column header if there's, say, 10,000 rows.

    If the data in the column is mostly different in each cell then sorting even 10 or 20 thousand rows is pretty much instant. But if many of the cells contain the same data then sorting can sometimes take 5 or 10 seconds or more.

    Doesn't matter if sortmode is manual or automatic - so programmatically calling the .sort method doesn't make any different.

    Any tips?

    Thanks

  2. #2
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,711

    Re: DataGridView Sorting Slow.

    If it's only during the development stage eg when using larger than normal datasets, why does it bother you?
    Why don't you do the tests with real datasets, like the typical user would?

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Posts
    121

    Re: DataGridView Sorting Slow.

    Quote Originally Posted by Arnoutdv View Post
    If it's only during the development stage eg when using larger than normal datasets, why does it bother you?
    Why don't you do the tests with real datasets, like the typical user would?
    Sorry. Please, I don't mean to sound rude, but your reply doesn't really answer the question.

    I always stress test my applications with all possible size of datasets.

    Just because most sensible users would use filters to get a useful result set it doesn't stop occasional users simply pulling all records, clicking a column header to sort and then scrolling down the list - and complaining when sorting takes a while.

    If the user has the ability to display 10,000 rows of data then I might quietly think the user is a pieman but, if possible, I want to make that user's experience as smooth as possible.

    But, I'm also just curious.

  4. #4
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,711

    Re: DataGridView Sorting Slow.

    I understand your point.
    Maybe this article can help you further:
    https://10tec.com/articles/why-datagridview-slow.aspx

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,452

    Re: DataGridView Sorting Slow.

    That link makes a key point that you'll need to answer: Is this a DGV bound to a datatable, or loaded directly? If it is the latter, then switch to the former. If it is the former already, then it comes down to sorting the datatable, which is something you have multiple ways to do, so which one are you using? Some are notably slower than others, which will add up as the size of the table increases.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Posts
    121

    Re: DataGridView Sorting Slow.

    Quote Originally Posted by Shaggy Hiker View Post
    That link makes a key point that you'll need to answer: .......

    There is a very small amount of backend data. The rest is generated on-the-fly by the software and presented in the grid. Not because a grid can be bound to backend data but because a grid is the best way to present this kind of data.

    That link primarily talks about performance issues populating the grid and scrolling up down through the data.

    I have no performance problems populating the grid. In my experience, contrary to what many other people complain about, the grid is blindingly fast. I can populate (programmatically create and write every row individually) 30 columns with 20,000 rows in around 600ms.

    The data can be scrolled up and down without any performance problems.

    Those are the things (populating and scrolling) that that link refers to. I don't have those problems.

    So, in my grid I have 30 columns. Most of them will sort instantly. My curiosity is just peaked by the behavior of a couple of the columns. for example two columns of numbers. Both have similar ranges - for example one is a buy price and the other is a sell price so numbers are quite close.

    If I click the 'Buy Price' header it sorts instantly. if I click the 'Sell Price' header the first 'Ascending' sort is instant. Further clicks on the header sorting in descending and then back to ascending take over 5 seconds.

    If I populate the slow column with other data instead such as a copy of the other column or just populate it with dummy data (random doubles) it sorts instantly so I know there's nothing physically wrong with that column (ie problem with the format). Just sorting of the real data is slow.

    So. I believe it is not a performance problem with the grid. It has something to do with the types of numbers being sorted. If the sorting of that specific data is slow then sorting it outside the grid is probably going to be slow as well because sorting other data inside the grid is fast.

    So. My question is (should have been) what kind of data would be slow to sort. At first I thought it was if cells had the same data but that doesn't seem to be it.

    I just can't figure out why one column of 20,000 numbers sorts the grid instantly but another, very similar, column of number takes 5 seconds.

    What is it in those number I should be looking at.

    Sorry for the long explanation.
    Last edited by Axcontrols; Jul 18th, 2017 at 03:55 AM.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,452

    Re: DataGridView Sorting Slow.

    Well, that IS pretty interesting, because the answer to your question is: It really doesn't matter much what the data is, normally. If you have done any custom sorting, such as implementing a class with the IComparable interface, you know that sorting always comes down to comparing just two items. The rest of the sort is always the same, so all you have to supply is a method such that for any two items A and B, return an integer (or a Boolean in some cases) that indicates whether A > B, B > A, or A = B. Usually, it's just A > B true or false? This is true for EVERY data type, as the rest of the sort is the same for all types. So, your question comes down to:

    For what data type is it harder to determine A > B?

    There are some data types that this can be somewhat more complicated for. Naturally, numeric types would be super fast. Strings wouldn't be quite as fast, but would still be pretty fast. Dates are about as fast as numeric types. Custom classes could have very complicated means to determine A > B, but in the vast majority of cases, they are no worse than strings.

    So, based on that, you can see that this shouldn't be your problem. It shouldn't make a bit of difference whether two numbers are close together or far apart, or even if they are the same. The sort just depends on being able to answer A > B, which can be answered with the same amount of effort for any two values.

    The sort routine itself is QuickSort, which is the fastest sorting routine known, in general. There are some specific patterns of starting data that can make it much less efficient, so you can look into the performance of QuickSort to see whether your particular situation is one that would cause it to be less efficient, but I'm not sure that the impact could be as dramatic as you are reporting. Take a look at this for more information on it:

    https://en.wikipedia.org/wiki/Quicksort

    Note that, in some implementations, the worst case scenario can be reached for already sorted data, which is what you have once you sort it the first time. However, the article also notes that this can be avoided fairly easily, so I'd be surprised if this was the issue.

    I'd say that you are running into a conversion issue, except that you said that sorting Ascending was fast, but subsequent sorts were slow. I think that a DGV generally holds either strings or Objects, so if those were all being converted to numbers, and some elements couldn't be, that would slow the sort to a crawl....but it would do it EVERY time, not just in one direction.

    By the way, what you were saying about bound data makes it sound like you are missing a point. I almost never put data into a DGV directly, and only do with a tiny amount of data. The reason is that it's so much easier working with datatables than DGVs directly that I usually just create a datatable and bind it to the DGV (which takes one line), then do all work on the datatable. This isn't in any way more complicated than working with a DGV directly, and is generally a bit easier, plus you get all the methods of the datatable. This could neatly avoid some of the conversion errors that might be impacting the DGV, if that really is the problem....though it seems like it can't be.
    My usual boring signature: Nothing

  8. #8

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Posts
    121

    Re: DataGridView Sorting Slow.

    Thanks Shaggy.

    Near the top I suggested the slowness occurs when many of the cells in the column contain the same numbers. I didn't really think that was it but, looking at the data, that is the only thing that makes sense. My 'slow' column does have a lot of the same numbers.

    The very first time the column is sorted the numbers are scattered everywhere in the column so your description of the sort just testing A>B or A<B will often indicate immediately that one is bigger or smaller than the other

    But after the first (fast) sort there's blocks of identical numbers all nicely arranged one after the other. So, subsequent sorts, just changing direction on the same column, will be comparing identical numbers which now appears to take (MUCH MUCH) longer.

    Does that make sense?

    To test that I just added a random 0.00000n to each value (just a tiny decimal value to each cell) to reduce the number of same values. and now sorting is instantaneous.

    But I can't go adding 0.00000n to the real values so I guess I'll have to live with it. As I said above, in the real world only a pieman would try to work with such a large dataset anyway and with sensible number of rows the sorting isn't a problem.

    But it is strange isn't it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.