sharing the info on how to use sqlite in memory databases from within
Revolution. Just tested in on my Rev, it works, so it may be useful for
you too. Say we have my.db database file. We wish to load it as an in
memory database to increase query performance. Here is the algorithm in SQL:
#first creating an in memory db
sqlite3 :memory:
ATTACH 'my.db' AS cpDb
#now repeat the sql string below for each table in the attached cpDb to
copy all of them into memory
CREATE TABLE myTable AS SELECT * FROM cpDb.myTable
...
DETTACH cpDb
Ready to use. Now the former my.db file is loaded as in-memory database.
This provides a big performance gain for complex queries!
So in Rev you would do:
put revOpenDatabase("sqlite",":memory:",,,,) into dbID
revExecuteSQL dbID, "ATTACH 'C:/my.db' AS cpDb"
repeat for each line myTable in dbTables #assuming dbTables contains a
list of tables in the database
revExecuteSQL dbID, "CREATE TABLE :1 AS SELECT * FROM cpDb.:1", myTable
end repeat
Do not forget to close the database (revCloseDatabase dbID) before
creating a new one. As far as I understand SQLite permits multiple in
memory databases, so if you do not close the previous database and
create new one you are likely to waste your RAM.
Best wishes
Viktoras
_______________________________________________
use-revolution mailing list
[email protected]
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
