Just thinking for myself if it’s …
a) possible for XOJO to put a virtual volume completly into RAM and use it like a a RAMDISK (something common in former DOS times).
b) Will the speed improve dramatically?
No data is written, just iterating/ reading though millions of recordsets. Size of DB is around 200 Megs.
An SQLite database can be in memory only AFAIR. If you read something from disk and keep it in a variable then it’s automatically in memory.
Well not sure about this, yes there is a mode:memory if you’d like to store or look up something temproarliy but I am not sure if existing db are put completly into memory when accessed.
I remember using a couple of years ago. You omit a folderitem for the database so there is nothing that can be written to.
Still not sure about this but you brought me to consider the sqlite.backup function… you can backup any given db to somewhere else… on start I open my existing disk based one and backup it into an second in-memory db conducting all read-queries with this one… I’ll give it a try…
if you use SQLite and set the cache big enough all your database is kept in memory.
Quick for searches.
http://www.monkeybreadsoftware.net/faq-howtosetcachesizeforsqliteorrealsqldatabase.shtml
Thank you Christian, your cache settings could slightly improve a DB-Run from 2:37 to 2:18… checking the backup-to-memory plan now…
did you use a 200 MB cache?
Because I got best speed when cache is nearly as big as database file.
SQLite can be created in memory and you can save/load by the backup function
When you have to elaborate / transform / rebuild you can gain incredible speed
Well somehow I got stuck in the in-memory DB and .Backup stuff. Maybe somebody else see the light…
To maintain flexibility I am getting my DB Provider with function CreateDatabaseProvider. As result it returns the database provider. This could be mySQL, ODBC or in this case an Sqlite Database. With sqlite-files it worked rock-solid for years but now with an in-memory DB it starts to behave kind of strange.
To keep the database open I am saving the database provider in a property named CurrentDatabaseProvider. Before any query is commited I am asking, if there is an already opened database. This is done with these two lines:
Let’s dig deeper: Here we are inside the CreateDatabaseProvider function where the sqlite file is opended and copied/ backuped into the in-memory DB. In the debugger everything works fine, both databases (SQLLiteDB for the file-based DB and InMemoryDB for the in-memory one) are connected without an Errorcode. Also .Backup Method is commited without any error obviously. But when passing it back to the caller of this function it suddenly becomes nil. Even when trying to put it directly info property CurrentDaatabaseProvider. What I am missing?
Backup takes 3 parameters.
the second can be nil but the last one should be -1
I have a window that conforms to SQLiteBackupInterface and shows the loading process.
Probably your db is not small…