Reading from several open databases at once

[spicymatt]

( First of all, congrats on GRDB and the amazing work you put into it !)

What did you do?

I am loading data in memory that comes from several GRDB DatabasePools that I need to maintain open at once.
I wanted to double check on the correct approach and am concerned that I might miss something important while doing this.

Currently my code looks something like this

// initialization code for DabasePools database1 , database 2 not shown
...
do {
          try database1.read { db1 in
                try database2.read { db2 in

                // fetch rows in db1 to create obj
                let obj = self.populate(from: db1)
                // enrich object from db2
                self.enrich(obj, db2)

                ...
} catch {
}

Is this nesting of database reads the correct way of doing this ?

I moved to this approach because my initial approach - that was somewhat nicer to read - was hitting performance :

do {
         try database1.read { db1 in
               // build some object from db1
               let obj = self.populate(from: db1)
               // call enrich : this will open database2, perform the reads in db2, modify the object and return (and I guess, close db2)
               self.enrich(obj)
               ...
} catch {
}

Can you make recommendation on the proper way to work with several databases at the same time and avoiding the costs of opening/closing connections.

Thanks

Matthieu

Environment

GRDB flavor(s): GRDB
GRDB version: latest
Installation method: manual
Xcode version: 10.2.1
Swift version: 4.2
Platform(s) running GRDB: macOS
macOS version running Xcode: Mojave

[groue]

Hello @spicymatt

It's difficult to answer your question, because it contains an inconsistency:

First you describe your initial approach by telling you modify db2:

I moved to this approach because my initial approach - that was somewhat nicer to read - was hitting performance [..] call enrich : this will open database2, perform the reads, modify db2 and return (and I guess, close db2)

Then you describe your second approach, which only reads.

This is important, because in order to address performance issues with SQLite, one need to know exactly when reads and write happens (when and how often the DatabasePool.read and DatabasePool.write methods are called).

Can you please clarify?

[spicymatt]

Hello @spicymatt

It's difficult to answer your question, because it contains an inconsistency:
...
Can you please clarify?

Ooops. Sorry for the inconsistency. I modified the original post.
I only perform reads in the 2 databases. The initial approach had a wrong comment that I now modified to clearly reflect what enrich(...) was doing

// call enrich : this will open database2, perform the reads in db2, modify the object and return (and I guess, close db2)

So, the difference between the 2 approaches is :

• initial : let enrich() open the database2 each time it is called through try database2.read { db2 in ... , read the data in db2, enrich the object and return
• second : pass db2 to enrich(), since I do already have db2 via my nested approach

Does it make sense ?

[spicymatt]

hmmm, it seems that a somewhat similar question was answered here : access to 2 databases queues at the same time.

[groue]

All right @spicymatt, thanks for the clarification.

Accessing two databases at the same time is supported: #55 has been solved three years ago). Your first code snippet is correct:

try databasePool1.read { db1 in
    try databasePool2.read { db2 in
        ...
    }
)

I do not know where your performance issue is. I suggest you profile your app with Instruments in order to find out.

Now I want to address this concern of yours:

avoiding the costs of opening/closing connections

GRDB generally keeps SQLite connections alive, for the lifetime of a DatabaseQueue or DatabasePool object. Precisely speaking, GRDB may close some connections when you call the releaseMemory() or setupMemoryManagement(in:) methods (see Memory Management).

But it's best to assume that GRDB connections are long-lived.

DatabasePool, in particular, maintains a pool of several SQLite connections, and uses or reuses them each time you invoke the DatabasePool.read method. The documentation of DatabasePool says:

Reads are generally non-blocking, unless the maximum number of concurrent reads has been reached. In this case, a read has to wait for another read to complete. That maximum number can be configured.

Now, if your app creates many instances of DatabasePool, GRDB will have to close and create at least as many SQLite connections. Creating several instances of DatabasePool or DatabaseQueue, for accessing a single database file, is a very bad practice. It goes against the Rule number 1 of GRDB concurrency. The Rule number 1 says:

Rule 1: Have a unique instance of DatabaseQueue or DatabasePool connected to any database file.

See the Concurrency Guide for more information.

It's OK to have two DatabasePools, but if and only if they do not access the same file. If you have a single database file, use one and only one DatabasePool for the whole duration of your database usage (usually, create it when your app starts, and forget it).

[spicymatt]

hello again, and thanks for the detailed answer regarding GRDB connections.

I am not breaking rule #1 : I do access 2 different database files using 2 DatabasePools. ;-)