I have a few customers who still have a classical hard disk. They don’t have an SSD. They have in their SQLiteDatabase a table of around 40.000 records. My app, while searching in that table, does some LIKE operations. The computer gets then stuck for 20 seconds sometimes, until the item was found.
The solution which I found is this: Create a SQLiteDatabase in RAM memory and copy the needed information of that table from the database of the hard disk to that database in the memory. Then do your query with the LIKE operator. The query takes now 1 second on very old computers.
3 Likes
Thanks for the tip ! At first I thought about a SELECT * FROM TheTable that would bring everything in a ‘record set’ and then applying the query with LIKE to the resulting record set. But your method is better. For sure, the client computer needs to have enough memory !
If you just increase the cache, it will act like an in-memory database with persistence.
See
https://www.mbs-plugins.de/archive/2017-10-16/Big_SQLite_Cache_for_64-bit_in
1 Like
Your code could simply be:
Var db As New SQLiteDatabase
dbh = db.Connect ()
result = dbh.ExecuteSQL ("attach database '/path/to/your/db' as dsk")
result = dbh.ExecuteSQL ("create table main.mytable as select * from dsk.mytable")
result = dbh.ExecuteSQL ("insert into main.mytable select * from dsk.mytable")
result = dbh.ExecuteSQL ("detach database dsk")