(Newbie Question)
I’m trying to speed up read access to my Sqlite database in my desktop app by following the example in the blog.
link text
I understand how to create a database in memory and copy a table across but the example doesn’t elaborate further about how to access the table from elsewhere in the program.
My current app has a global variable for the database and after opening the file is available whenever I need data. Can someone tell me how to access the RAM data?
you access just like you would a file based database…
you should have a Database class object for which you made a “connection”
the only difference between a file based database and a memory based one, is there is no filename used.
[quote=424889:@Dave S]you access just like you would a file based database…
you should have a Database class object for which you made a “connection”
the only difference between a file based database and a memory based one, is there is no filename used.[/quote]
From the Blog:
db.SQLExecute("attach database ':memory:' as 'RAMDB'")
db.SQLExecute("create table RAMDB.product_table as select * from main.product_table")
I still don’t understand how to do it if there is no variable containing the database in memory. Can you please give me an example eg. I want to use a .SQLExcecute statement on RAMDB?
That attaches an in memory database to a database connection you already made…
In this case DB is the object you use, but all your SQL must refer to RAMDB
DB.SQLSelect("Select * from ramdb.mytable")But if you are not using an disk based file… just make a normal connection and leave out the filename part
ramDB=New SQLiteDatabase
if ramDB.Connect then
do stuff
end ifnormally you would have addtional lines like there for a DISK BASED database
dim path as FolderItem'path=SpecialFolder.Desktop.child("mydatabase.SQLite")
'
ramDB.DatabaseFile=path
call ramDB.CreateDatabaseFile[quote=424897:@Dave S]That attaches an in memory database to a database connection you already made…
In this case DB is the object you use, but all your SQL must refer to RAMDB
DB.SQLSelect("Select * from ramdb.mytable")Ok I understand now. So I select from ramdb to read and select from file based db to write.
Do I need to refresh ramdb after each write so it is up to date for the next time I read?
sigh… No
you create ramDB (forget about the DB object you keep referring to…) if you want only a in-memory database you have ONE database reference/connection
It works EXACTLY like a file based database… as in EXACTLY… not only that… but they work the same
All sighs … aside, if it works exactly the same, you mean db.executeSQL ("Update ramdb.myTable Set field1 = …
will leave me with changes to the database file on my harddisk.
Is your intention to have a single IN-MEMORY database… period?
If so, please look at the example I posted above…
Because NO it won’t affect any database on DISK, a DISK BASED database updates a file on disk, an IN-MEMORY database updates a database existing 100% in memory… changes to that would be lost when the app quits unless you copied (use SQLite BACKUP command) to a DISK Based database file.
The ATTACH command (which you referenced in your first post) allows you to manipulate multiple physical databases in a single SQL statement … Those multiple databases can be any combination of DISK BASE and/or IN-Memory
I am trying my best to explain this concept… but highly suggest you do some reading on this subject so you can asked more directed questions, as I feel you do not have a firm grasp on the concepts of database architecture.
[quote=424935:@Dave S]Is your intention to have a single IN-MEMORY database… period?
If so, please look at the example I posted above…
Because NO it won’t affect any database on DISK, a DISK BASED database updates a file on disk, an IN-MEMORY database updates a database existing 100% in memory… changes to that would be lost when the app quits unless you copied (use SQLite BACKUP command) to a DISK Based database file.
The ATTACH command (which you referenced in your first post) allows you to manipulate multiple physical databases in a single SQL statement … Those multiple databases can be any combination of DISK BASE and/or IN-Memory
I am trying my best to explain this concept… but highly suggest you do some reading on this subject so you can asked more directed questions, as I feel you do not have a firm grasp on the concepts of database architecture.[/quote]
Dave, I believe the goal is to load a database from disk into memory for performance reasons.
The SQLiteDatabase class has a Backup method that would do exactly what you like. Just connect to the on-disk as normal, create a new SQLiteDatabase instance and connect without specifying a file, and all OnDiskDB.Backup(InMemoryDB) and done.
However, this is probably unnecessary. Default cache size is 8MB, if I recall correctly, which is a good amount for most SQLite databases. The cache_size pragma can be used to boost the cache size. This is generally better because you dont need to read anything until you actually need it, and future reads will be faster once the cache is populated.
I’m not one to assume, and I asked multiple time if the intention was only an in-memory based database.
I have a project where I do exactly what you described (I mentioned the backup command earlier). There is an on-disk database which is read into an in-memory one using the Backup command, the user then manipulates as required, but if they decide to trash the changes, I simple erase the in-memory version. Rollback isn’t an option, as there could be who knows what transpiring, and some data must be commited before other things can be done.
But I concur as to performance… yes an in-memory would be faster, but the cost of loading and saving might out weigh the benefits. In my case, the whole database is only a few meg if that much. and the benefit of being able to do Save, SaveAs and Revert make it necessary in my case.
Dave my intention was to use the example of speeding up reads using an attached database in RAM. I think we are on the same wavelength there, thank you for the syntax to access the attached table.
I was asking also how to go about writes. I don’t think that’s a silly question really.
As per my previous reply - access the memory table to read but use the regular disk based table to write.
My point is that after I update the table on disk, the attached memory table is no longer an up to date copy so further reads from it will not be accurate.
How do I get around this? Or is it simply not practical if you need to regularly save to the database file.
As for further reading on Sqlite in ram perhaps you can point me in the right direction, I could only find this example of attaching a database.
The only database that will be up to date is the one you have written new data too.
Attaching a database is usually to allow a single SQL transaction to operate on TWO physical databases at the same time
As I mentioned, and as did Thom, you are not going to gain anything, as the cost of loading and saving the data will out weigh any other savings. And the larger the database , the more time it will take, and perhaps even exceed the amount of available memory. These are the advantages of an on-disk database… You READ only what you need to read, and Write only what you need to write.
If you have a 1gig database (for example), and the user ended up only updating a few records, you would be needing to READ 1gig into memory, update a few records, and write 1gig back to disk. Where if you just left it as an on-disk database, you would end up reading a few records and writing a few records, NOT the entire thing.
So, again, I suggest you do some reading on how databases work, as I still beleive you do not have a solid grasp on the concepts involved.
When you have done that, you should be in a position to ask more pointed questions. Until then, I will leave you to that task, as I have explained as much as I can.
SQLiteDatabase in RAM memory is identical in use to SQLiteDatabase, except you don’t define a FolderItem location when you create/connect to it.
This is what I would do:
- Open two SQLiteDatabase databases on launch (one RAM and the other disk). Keep them open
- Copy the disk tables to the RAM database
- Every time you need to READ, only perform the SELECT on the RAM database
- Every time you need to WRITE, perform an identical INSERT INTO/UPDATE/DELETE on BOTH the RAM and disk databases (keeping them in sync.
- Rinse and repeat
[quote=424947:@Craig Grech]Dave my intention was to use the example of speeding up reads using an attached database in RAM. I think we are on the same wavelength there, thank you for the syntax to access the attached table.
I was asking also how to go about writes. I don’t think that’s a silly question really.
As per my previous reply - access the memory table to read but use the regular disk based table to write.
My point is that after I update the table on disk, the attached memory table is no longer an up to date copy so further reads from it will not be accurate.
How do I get around this? Or is it simply not practical if you need to regularly save to the database file.
[/quote]
If you attach an in-memory database and populate it with a select as you showed at the top of the thread, you now have two entirely separate databases (one on disk, one in memory). Initially they happen to have the same content. I think the points above about overhead of doing that are well taken.
Try http://www.sqlite.org for good documentation, especially the FAQ page.
[quote=424978:@David Cox]This is what I would do:
Open two SQLiteDatabase databases on launch (one RAM and the other disk). Keep them open
Copy the disk tables to the RAM database
Every time you need to READ, only perform the SELECT on the RAM database
Every time you need to WRITE, perform an identical INSERT INTO/UPDATE/DELETE on BOTH the RAM and disk databases (keeping them in sync.
Rinse and repeat[/quote]
Thanks, that works fine and copying just the most used tables to memory has definitely sped up data retrieval for reading without causing any noticeable delay at startup.
Writing doesn’t seem to be any slower even though I’ve duplicated the function. I only update one record at a time so really can’t tell the difference.
Sorry I was being quite inefficient by doing separate update statements for disk and memory tables.
The advantage of attaching the memory table to the disk database is you can perform a single operation to update memory and disk data whenever you save something.
I guess that’s why the example attaches tables to the existing database rather than creating a new separate database.
not quite sure how you figure that to be a true statement
[quote=425045:@Dave S]Dave S 2 hours ago San Diego, California USA
@Craig Grech you can perform a single operation to update memory and disk data whenever you save something.
not quite sure how you figure that to be a true statement[/quote]
Can’t you do something like this? -
UPDATE db.mytable, ramdb.mytable SET ....WHERE ...etc [quote=425051:@Craig Grech]Can’t you do something like this? -
UPDATE db.mytable, ramdb.mytable SET ....WHERE ...etc No. Not in SQLite. Is there a database where that is valid?
Really, just use the caching already built in. Youre over complicating the problem.
I really thank everyone for their help the performance improvement has been very noticeable.