Strange idea: SQLite's RAM memory clone

[peacemaker]

Hi, All

I have found that my app speed is lower than needed.
It uses rather complex data structure and SQLite helps very much: several static dictionary tables and the online-realtime working data table.
But SQLite file db depends on the drive, SSD or HDD speed., as i can understand.

So, strange idea: maybe is there SQLite-like engine working ... only in RAM ? Reading from drive the initial data, but some tables are marked as existing only in RAM...
Crazy ?

 

[JohnJ]

Hi, All

I have found that my app speed is lower than needed.
It uses rather complex data structure and SQLite helps very much: several static dictionary tables and the online-realtime working data table.
But SQLite file db depends on the drive, SSD or HDD speed., as i can understand.

So, strange idea: maybe is there SQLite-like engine working ... only in RAM ? Reading from drive the initial data, but some tables are marked as existing only in RAM...
Crazy ?

Yes, google sqlite in-memory tables

 

[peacemaker]

sqlite in-memory tables

sqlite3_open(":memory:", &db);

Wow ! But how to use in B4J ?

SOLVED:

SQL.InitializeSQLite("", ":memory:", True)

Seems, the topic can be moved into Question forum.

Does this "memory" SQLite work in Android also ?

 

[Erel]

Does this "memory" SQLite work in Android also ?

Yes, though it is unlikely to provide a significant boost.

A better solution:
1. Analyze your queries and add indices when needed.
2. Use the async methods.

 

[peacemaker]

though it is unlikely to provide a significant boost.

In Android only, Erel ? Or any place ?

 

[Erel]

For Android.

Proper indices and async methods will make it work good in all platforms.

 

[peacemaker]

A better solution:
1. Analyze your queries and add indices when needed.
2. Use the async methods.

It's measuring app, reading data from COM-ports, serial sequencies can be very fast, so, max saving speed is needed anyway.

 

[Erel]

There isn't enough information for me to say anything. Which part is slow? How is the data structured?

Don't get me wrong, if you don't need the db to be persisted and it is unlikely to grow too much and it is fast enough as a memory db then this is a good solution.

 

[incendio]

Been using sqlite in memory since use B4A for the first time.

Used it for temporary file so don't need to do clean up when app closed.

Never compared speed with db in file, but sometimes need thousand of records to process, db in memory served well for this purpose.

Also with db in memory, you have global data that can be accessed from all activities / pages.

 

[tchart]

Wow ! But how to use in B4J ?

SOLVED:

Seems, the topic can be moved into Question forum.

Does this "memory" SQLite work in Android also ?

I wrote some code somewhere that will copy an entire SQLite db into an in memory a SQLite database. Will see if I can dig it up. From my experiments it did not give me any significant performance gains.

However as Erel said you probably need to give your DB some TLC. I find with larger databases you need to optimize, flush the WAL and rebuild indexes. I do this after large operations and also on start up of my app. This will greatly improves performance.

 

[tchart]

Ive posted my project that clones a database to "in memory" here;

Clone SQLite Database to SQLite Memory Database

This is a prototype I wrote a while back that takes a source SQLite database and clones it to another SQLite database - in this case an in memory SQLite database. The code is prety straight forward, initialize the source (from) and destination (to) SQLite databases and call the CopySQLiteDB...

www.b4x.com

 

[peacemaker]

Ive posted my project that clones a database to "in memory" here;

Clone SQLite Database to SQLite Memory Database

This is a prototype I wrote a while back that takes a source SQLite database and clones it to another SQLite database - in this case an in memory SQLite database. The code is prety straight forward, initialize the source (from) and destination (to) SQLite databases and call the CopySQLiteDB...

www.b4x.com

Thanks. Actually, now i'm using 2 dbs - one for realtime processing in RAM only, an second for storing constant records in a file.
All is OK, including Android, as Windows. But not sure yet how will be if the app is to be working for looooong time. I guess, soon some error about OutOfMemory may occur...

 

[KMatle]

Mostly a design "issue". Direct access with a good structure and good indexes should be fine. Usage of in memory db's is fine (I use it more to spare my drive a bit when the data is static and used a lot). The rest depends on the data. Big data is a server task. Apps should only show some data (the results) and not do big data jobs.