C # UWP Windows 10 project

There are two SQLite tables.

When sampling from the main table, you need to fill in the missing data from the child table (these fields in the class of the main table are marked with the [Ignore] attribute).

Sample code:

 using (var db = new SQLiteConnection(SQLITE_PLATFORM, DB_PATH)) { var res = db.Table<Table1>().Where(i => i.TimeStamp >= Stamp).Select(i => { i.Param1 = db.Get<Table2>(i.table2ref).Title; i.Param2 = db.Get<Table2>(i.table2ref).Type; i.Param3 = db.Get<Table2>(i.table2ref).Symbol; return i; }); return res.ToList(); }

The problem is that the .select operator (judging by the performance of the query) fills not only the result values ​​of .Where , but the records of the entire database (in some cases, you also need to select them). With 10K + records in the database, performance, to say the least, so-so ...

Now in my main table all the required fields are filled in when Insert This gives me the opportunity to make selections very quickly, but if one of the records in the child table changes (which happens very often), I need to change all the fields associated with it in the main table and overload the interface, which is also very long.

Actually the question is how to make such samples without load on the base? Tell me the solution to this problem ...

  • How did you count the performance? - Pavel Mayorov
  • @PavelMayorov By eye. )) - SYL

2 answers 2

The only way to fulfill your query without an index on the TimeStamp field is to cycle through all the records in the table. Of course, it will work for a long time, and Select has nothing to do with it - without it everything will work just as long.

Make an index on the field TimeStamp and you will be happy.

  • I made an index across the TimeStamp field, the sample itself happens quickly, but the select fields fill in the missing fields (there are about 10 of 3 different tables by index), there are a lot of records in the main table, and hence the load time subsidence. What to do in this case? Is it possible to use join instead of Get to fill in the required fields with values? Especially if the TimeStamp filter captures several thousand entries - SYL
  • @SYL makes no sense - JOIN will essentially use all the same gethes. The database is local, so the number of requests for it does not matter, only the number of reads from the disk is important. - Pavel Mayorov
  • @SYL reduce the number of requests, cache already loaded records, etc. - Pavel Mayorov
  • pastebin.com/FqXnbnuE is an example of a filtered query. Depending on user settings, it can return from several records to 10K records. In this case, the more entries fall under the filter - the longer it takes to load. And the load time does not increase proportionally - SYL
  • Well, it just begs the local cache of accounts, merchants, tools, and especially tags! - Pavel Mayorov

You make three calls to each line for db.Get<Table2>(i.table2ref) . Linq2SQLite is a fairly simple wrapper over a database, and it does not cache the results.

If you make a call 1 time per line - you will get acceleration 3 times.

Also, check the data type used for the primary key. If System.Int64 (long) is used as the primary key, then sampling on the primary key works twice as fast.

By the way, are duplicate table2ref allowed? If so, caching the results of the subquery in the dictionary can shorten the time even more.

PS Well, and a stupid question - and in the field TimeStamp do you have an index in the database?

  • table2ref is a GUID . Repetitions can of course be. By TimeStamp is no index. - SYL
  • Well, damn, with what in this case here generally select? - Pavel Mayorov