C # UWP Windows 10 project.

The application synchronizes with the server, gets 14 tables (9 of them are almost static + -200 lines, another 4 very often change but they have about 20-100 lines and one that changes constantly, both by the user and the server, there are 1000+ lines in it) .

The application should display a list from the main (the largest table), most of the data in it refers to other tables, by itself, when displaying, you need to display information from them.

Now I use SQLite, save each table, and when I save the main one, I add load information from other tables to it that is needed when displaying, then in the model I simply load the entire table into ObservableCollection and hook it to the ListView . The downside is that:

  1. Synchronization on the phone lasts a very long time (5-10 minutes with 2800 rows in the main table).
  2. When changing records in related tables, you have to overwrite all the load information in the main (which is also very long)

The question is, am I doing the right thing, or is it better to pull up the fields from the load tables via get { } ? How will this affect performance?

Or maybe there are better and faster ways to do this?

  • one
    And tried to profile? 5 minutes sounds too wild. - Monk
  • @Monk No, I have not tried. Well, on a PC it is no more than 30 seconds, and the phone is certainly bent. And this is really wild ... (( - SYL
  • There is no “best approach”. Try it. What is appropriate in one case will not be appropriate in another. - VladD
  • @VladD I agree with you, but I would like to know the opinion, some approaches are so unprofitable that you shouldn't even try ... - SYL
  • one
    @SYL: For example, if the database grows, it will no longer make sense for the client to overtake the entire database. Rather, it will simply execute the query on the server and send the results to the client. - VladD

1 answer 1

If I understand correctly, you only see a list from the main table, and when you click on an item, data from a heap of others begins to appear.

If so, you can already optimize a little:

  1. For the list, we load only the main table (not more). And create a Task<Details> LoadDetails() method that loads the rest.
  2. It is necessary to check that it is faster: load the entire main table at once or perform the count (*) + operation to load only the first N rows. If the second is faster (theoretically, with small N, the load should be instantaneous), then:
    1. We divide the entire list into packs by N elements, create a large list of stubs.
    2. If the user is looking at an element from pack M, then we force the elements from packs M-1, M, M + 1 to be loaded. Thus, with smooth scrolling, it creates the effect that everything is loaded.
    3. All loading is only in the background thread. UI and so busy.
    4. Instead of ObservableCollection, we simply use a List of objects, each of which has a Target and Id property (position in the list). If null, then show "load", if not - then show the element. If the Target appeared - then we call the function that causes the pack to load, where our element will be (see the Id property). And do not forget about INotifyPropertyChanged.
  3. If the user did click on the item, then we show the "loading" stub, we quickly join the database and load the rest of the data.

Cons compared to the current solution:

  1. Details are shown with a delay
  2. The user still has a chance to notice the underload, if he scrolls quickly, etc.

Pros compared to the current solution:

  1. Less data is transmitted for a cold start.
  2. Less data is transferred for the list, it loads faster.
  3. Visually, the user is always loaded (due to small preloads)
  • Yes, during operation, a list is displayed only from the main table. List specifically replaced by ObservableCollection . when adding new entries, it is expensive to reload the entire list, just add it to the list (to the required position) and then by itself INotifyCollectionChanged does its job. The 2nd problem is that at the synchronization stage (when writing data to the main table), the records need to be supplemented with load information from other tables (so that only one table is used during operation). - SYL