csv - SQLite performance tuning on Windows IoT -

i have windows iot application capturing data data source , storing on persistent storage. here technology stack

1. windows 10 iot core
2. uwp background application (c#)
3. sqlite
4. sqlite universal windows platform 3.19.3
5. sqlite.net-pcl nuget https://github.com/oysteinkrog/sqlite.net-pcl

and using raspberry pi 3 testing solution. storing data have decided use sqlite. following schema of table

public class tbldatalog {     [primarykey]     [autoincrement]     public int recordid { get; set; }      public int? param1 { get; set; }      public int? param2 { get; set; }      public double? param2 { get; set; }      public double? param4 { get; set; }      public double? param5 { get; set; }      public int? param6 { get; set; }      public int? param7 { get; set; }      public double? param7 { get; set; }      public double? param8 { get; set; }     public double? param9 { get; set; }     public double? param10 { get; set; }     public double? param11 { get; set; }      [indexed]     public datetime recordedon { get; set; } } 

for testing have stored 2 million rows in table , there no insertion being performed. following code fetch data last 6 hours

var commandstr = $"select * tbldatalog recordedon >= " + datetime.now.addhours(-6) + " , recordedon <= " + datetime.now; var cmd = connection.createcommand(commandstr); var sensordataquery = cmd.executequery<tbldatalog>(); //this line takes around 90 seconds on average complete 

the problem 6 hours of data (roughly 216000 rows, data rate 10hz) taking around 90 seconds fetch records slow. @ end need convert data csv , provide file download, have tried store file flat csv file exponentially fast compared sqlite approach, above case taking 5 seconds on average.

i want know options have optimize sqlite performance (if any)? simple query, doubt due sd card storage, using class 10 storage card, , csv working on same card reasonable speed.

any suggestion highly appreciated in context.

you speed datetime search little bit making table without rowid table datetime primary key.

but reading , parsing tens of megabytes of data database , constructing hundreds of thousands of tbldatalog objects in memory take time. should not use objects when not need them; reading data different kind of api (e.g., sqlitepcl.raw) more efficient.