[fixed] Sorting Order is not "natural" (1 Viewer)
- Thread starter Lehmden
- Start date
- Thread starter
- #12
Hi.
I will run the import tonite. It's a drawback that we store the thumbs in DB as this is 90% of the import time. If they were on file system I only need maybe half an hour instead of five or six hours to run the import...
I will run the import tonite. It's a drawback that we store the thumbs in DB as this is 90% of the import time. If they were on file system I only need maybe half an hour instead of five or six hours to run the import...
- March 24, 2007
- 12,073
- 7,459
- Home Country
-
Germany
- Moderator
- #13
Hmm, would be better to move thumbnail posts into the linked thread, fits better in there 
Edit: Moved post here https://forum.team-mediaportal.com/threads/reworking-mp2s-thumbnail-building.122807/ and moved thread into public forum.
Edit: Moved post here https://forum.team-mediaportal.com/threads/reworking-mp2s-thumbnail-building.122807/ and moved thread into public forum.
- Thread starter
- #14
Hi.
Import of first half of my series collection with new SQLite Plugin lasts from [2013-11-18 00:12:12,925] to [2013-11-18 05:36:45,644]. Thats some minutes faster than before, but... at first some more thumbs are not generated as on last run (has nothing to do with the DB Provider) and as all Metadata and Fanart are downloaded already the huge amount of internet activity (more than 1 GB was stored first run) was not necessary. So the overall speed is a bit slower, I think, but not that much as the main amount are the thumbs generation, not the DB queries or internet downloads. So we should keep it this way.
Import of first half of my series collection with new SQLite Plugin lasts from [2013-11-18 00:12:12,925] to [2013-11-18 05:36:45,644]. Thats some minutes faster than before, but... at first some more thumbs are not generated as on last run (has nothing to do with the DB Provider) and as all Metadata and Fanart are downloaded already the huge amount of internet activity (more than 1 GB was stored first run) was not necessary. So the overall speed is a bit slower, I think, but not that much as the main amount are the thumbs generation, not the DB queries or internet downloads. So we should keep it this way.
- March 24, 2007
- 12,073
- 7,459
- Home Country
-
Germany
- Moderator
- #15
One more question:
if we are already providing an own sorting method, does it already sort numbers naturally? I mean:
not 1->10->100->2->20
but 1->2->10->20->100
I think there was a MP1 related thread about natural sorting. IIRC there was one implemenation was using P/Invoke to use Explorer's comparision and an C# extended version. I would expect the native Explorer one to be faster.
It would be good to provide this kind of sorting, if we are already taking care about custom collations.
if we are already providing an own sorting method, does it already sort numbers naturally? I mean:
not 1->10->100->2->20
but 1->2->10->20->100
I think there was a MP1 related thread about natural sorting. IIRC there was one implemenation was using P/Invoke to use Explorer's comparision and an C# extended version. I would expect the native Explorer one to be faster.
It would be good to provide this kind of sorting, if we are already taking care about custom collations.
Yep, now that you mention it, I remember the discussion for MP1. I would expect that it currently does not sort numbers naturally. Will try to dig a bit deeper and see what we can do. We have to bear in mind, however, that our collation method is called hundred thousands if not a million times during an import so the performance is really critical. By using the current implementation my ImportTest already suffered a drop in performance from below 20 minutes to more than 25 minutes - so more than 25% performance drop.
If the natural number sorting leads to a further material performance drop, I would propose that we make this one configurable via setting because there may be people (like me) who don't consider natural number sorting important enough to take another 25% performance hit...
If the natural number sorting leads to a further material performance drop, I would propose that we make this one configurable via setting because there may be people (like me
) who don't consider natural number sorting important enough to take another 25% performance hit...Forum-Thread is here: https://forum.team-mediaportal.com/threads/use-natural-sorting.115151/#post-949537
But the link provided in there for the PInvoke doesn't work for us, because we cannot provide a culture for the sort so that it may use the thread's culture - which leads to database consistency problems. However, I found another PInvoke method that really seems to support anything we might think of: http://msdn.microsoft.com/en-us/library/windows/desktop/dd317761(v=vs.85).aspx
In particular we can pass LOCALE_NAME_INVARIANT to get stable results independent from the user-chosen culture (necessary for the database to be consistent) and of course SORT_DIGITSASNUMBERS
There are so many possible tweaks for this sort method... One of the community comments even explicitly mentions that this takes care of the "turkish i" problem than Unoparator mentioned...
Let's see how fast it is...
But the link provided in there for the PInvoke doesn't work for us, because we cannot provide a culture for the sort so that it may use the thread's culture - which leads to database consistency problems. However, I found another PInvoke method that really seems to support anything we might think of: http://msdn.microsoft.com/en-us/library/windows/desktop/dd317761(v=vs.85).aspx
In particular we can pass LOCALE_NAME_INVARIANT to get stable results independent from the user-chosen culture (necessary for the database to be consistent) and of course SORT_DIGITSASNUMBERS
There are so many possible tweaks for this sort method... One of the community comments even explicitly mentions that this takes care of the "turkish i" problem than Unoparator mentioned...
Let's see how fast it is...
Test is just running in the background. In the meantime some more thoughts on this:
Morpheus had a comment at GitHub where he asked why we use InvariantCulture, not the Culture selected by the user, for sorting. There is a reason for this: The sort function is used by SQLite to build the indexes. So if the user first selects German culture (where e.g. 'ä' comes after 'a') and does an import, the index in the database (the one stored on harddisk) is sorted taking into account German culture. If the user later on switches MP2 to English culture (where maybe 'ä' comes after 'z') and adds some MediaItems, the indexes become inconsistent because the sort method provides different results than before although the index in the database file expects the sort method to return the same results for the same input. That is why I used InvariantCulture.
Then I thought: Why do we use the sorting from the database? In the database sorting should be consistent - ok. But we transfer the data to the client anyway and when the user chooses a different sort order in the client, why do we go back to the database, let it do the sorting and potentially transfer the same items over a network to the client just to get another sort order? Well, currently this doesn't make sense because the data is already at the client and it would be much faster if the client would just resort the objects based on the new selection. If we did this, we could easily use the culture selected by the user to do this sorting on the client side.
But then I remembered what @Valk is currently implementing: virtualization of the data access. This means that when the user wants to display 2.000 music tracks we would not fetch all those 2.000 MediaItems from the database anymore. We only fetch as many as are displayed in MP2 client (plus some more for pre-caching). This means that as soon as data virtualization is implemented, the client cannot do the sorting anymore - because it does not know all the data in the selection. So back to square one: Sorting has to be done by the database to enable us to use data virtualization.
Nevertheless I thought about further possibilities how we could take into account the culture selected by the user. There is a possibility: We have to recreate the indexes in the database every time a user changes the culture selection. This is not a big deal and should be easy to implement (just one addition to the ISQLDatabase interface like "void RecreateIndexes();" which is called after every change in culture). But this on the other hand requires that we only have ONE culture selection for a whole MP2 system (i.e. Server and all the clients need to have the same culture). @morpheus Is that the case? Do we only have one culture for the whole system or can we choose different cultures for different clients? I the latter is the case (and we want to keep it like that) I fear that it is impossible to have both: (a) data virtualization and (b) sorting taking into account the user culture settings. We only have one database in the system.
There is technically only one possibility to achieve this: We don't have indexes in the database. But we don't really want that as it would slow down the whole system dramatically..
Any thoughts on this?
Michael
Morpheus had a comment at GitHub where he asked why we use InvariantCulture, not the Culture selected by the user, for sorting. There is a reason for this: The sort function is used by SQLite to build the indexes. So if the user first selects German culture (where e.g. 'ä' comes after 'a') and does an import, the index in the database (the one stored on harddisk) is sorted taking into account German culture. If the user later on switches MP2 to English culture (where maybe 'ä' comes after 'z') and adds some MediaItems, the indexes become inconsistent because the sort method provides different results than before although the index in the database file expects the sort method to return the same results for the same input. That is why I used InvariantCulture.
Then I thought: Why do we use the sorting from the database? In the database sorting should be consistent - ok. But we transfer the data to the client anyway and when the user chooses a different sort order in the client, why do we go back to the database, let it do the sorting and potentially transfer the same items over a network to the client just to get another sort order? Well, currently this doesn't make sense because the data is already at the client and it would be much faster if the client would just resort the objects based on the new selection. If we did this, we could easily use the culture selected by the user to do this sorting on the client side.
But then I remembered what @Valk is currently implementing: virtualization of the data access. This means that when the user wants to display 2.000 music tracks we would not fetch all those 2.000 MediaItems from the database anymore. We only fetch as many as are displayed in MP2 client (plus some more for pre-caching). This means that as soon as data virtualization is implemented, the client cannot do the sorting anymore - because it does not know all the data in the selection. So back to square one: Sorting has to be done by the database to enable us to use data virtualization.
Nevertheless I thought about further possibilities how we could take into account the culture selected by the user. There is a possibility: We have to recreate the indexes in the database every time a user changes the culture selection. This is not a big deal and should be easy to implement (just one addition to the ISQLDatabase interface like "void RecreateIndexes();" which is called after every change in culture). But this on the other hand requires that we only have ONE culture selection for a whole MP2 system (i.e. Server and all the clients need to have the same culture). @morpheus Is that the case? Do we only have one culture for the whole system or can we choose different cultures for different clients? I the latter is the case (and we want to keep it like that) I fear that it is impossible to have both: (a) data virtualization and (b) sorting taking into account the user culture settings. We only have one database in the system.
There is technically only one possibility to achieve this: We don't have indexes in the database. But we don't really want that as it would slow down the whole system dramatically..
Any thoughts on this?
Michael