Need for Speed (databases)
viktoras at ekoinf.net
Tue Jul 17 08:42:51 EDT 2007
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
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
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
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.
More information about the Use-livecode