Fast Database Query

1

This was suggested on TOF

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")

I submit the for SQLite using the BACKUP command will be considerablly faster
especially if the table(s) are indexed… Insert has to reindex as it goes,

3 Likes
2

@DaveS, Agreed.

From memory, in order of speed:

1st Place. backup
ATTACH DATABASE 'bak/inventory_backup.db' AS inventory2;
2nd Place. restore
ATTACH DATABASE ‘inventory2.db’ AS Inventory;
.restore Inventory bak/inventory_backup.db
3rd Place (Still waiting for it to finish). TOF post - a distant last in every aspect.

Whilst backup is quicker, being a little cautious of screwing with my backups I tend to use restore. But my caution may be misplaced.

Kind regards, Andrew