Need for Speed (databases)

viktoras didziulis viktoras at ekoinf.net
Tue Jul 17 07:42:51 CDT 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
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






More information about the use-livecode mailing list