My usual operations are very simple: given one big table with about 50 cols and 30k rows (can be up to 1m), make queries like SELECT AVG(col1) FROM table WHERE col2 IN (?) AND col3 IN (?) AND col4 < ?, SELECT col1 FROM table WHERE [same], etc. Columns are strings or floats.
I've started with simple .net DataTables + LINQ which are quite fast but obviously size-limited (it crashes long before there is no ram left). Also there are no indexes which I thought should increase performance.
Then I've tried SQLite (devart adapter) with SQL queries, which is like 10 times slower than in-memory datatables. I've made indexes on all columns in this table but this didn't help. I thought SQLite is considered one of the fastest embedded DBs so it's quite disappointing.
Are there some switches to pull all tables into memory?
Should I load SQLiteDataTable fully and then use filters instead of SQLiteCommands?
What is limit for SQLiteDataTable size? e.g. will it handle table with 50m cells?
sqlite performance
-
StanislavK
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
Working with in-memory objects is certainly substantially faster than accessing a file, thus it is the expected behaviour that retrieving data from a SQLite database file is slower than using LINQ to Objects.
However, you can improve select performance of SQLiteDataTable. For example, you can set the FetchAll property to true so that whole result set is loaded into memory at once. Also, you can use the NonBlocking property to make fetch asynchronous.
As for the filters, do you mean the Select method of SQLiteDataTable? Using this method should increase the access speed (as long as whole result set can be stored in the data table), as filters are applied at the client side.
SQLiteDataTable shares the common DataTable limitation of approximately 16.8 million rows:
http://msdn.microsoft.com/en-us/library ... S.80).aspx
Please tell us if this helps.
However, you can improve select performance of SQLiteDataTable. For example, you can set the FetchAll property to true so that whole result set is loaded into memory at once. Also, you can use the NonBlocking property to make fetch asynchronous.
As for the filters, do you mean the Select method of SQLiteDataTable? Using this method should increase the access speed (as long as whole result set can be stored in the data table), as filters are applied at the client side.
SQLiteDataTable shares the common DataTable limitation of approximately 16.8 million rows:
http://msdn.microsoft.com/en-us/library ... S.80).aspx
Please tell us if this helps.