Grdb.swift: concurrentRead Question

[gloparco]

In my code, I have a read-only database and perform hundreds of synchronous reads using a DatabaseQueue. This is very slow given the number of reads I have. I have other approaches I may take to alleviate this problem, but while looking through the GRDB docs, I noticed that DatabasePools were described as allowing "concurrent database accesses", and that "several threads can perform reads in parallel". So that seemed promising. I thought I would configure the dbPool to allow a maximumReaderCount of 10, and I would be good to go.

However, I cannot for the life of me figure out if my use case is even appropriate for a DatabasePool & concurrentReads, and if so, how to go about it. All of the examples for concurrentRead show it inside of a writeWithoutTransaction block. Since my database is read-only, I don't need a writeWithoutTransaction block. But without it, my attempts to use concurrentRead have been unsuccessful.

Is there some sample code out there for this use case that I have missed?

Thanks for maintaining such a great library!

[groue]

Hello @gloparco,

Let's clear the confusion. You'll generally use a DatabasePool just like you use a DatabaseQueue, with database access methods such as write and read. Let's focus on read, since your database is readonly.

The read method is synchronous: it blocks the thread that calls it until it has finished using the database:

do {
    let value = try queueOrPool.read { db in
        try fetchValue(db)
    }
    // do something with value
} catch {
    // handle database error
}

So if your application always uses the database from the main thread, all read calls will run one after the other, and no concurrency will ever happen.

If, on the other side, your application uses the database from various threads, then database pool concurrency starts to kick in: read still blocks the thread that calls it, but reads can be performed in parallel (up to Configuration.maximumReaderCount). For example, if your app spawns several operations in an OperationQueue, they will be able to read in the database in parallel. The same if your app dispatches database reads in a global concurrent DispatchQueue.

That's read.

---

There is also asyncRead. Unlike read, this database access method does not block the thread that calls it. Instead, it eventually provides a database access, later, as soon as it is possible. asyncRead is to read what async is to sync for DispatchQueue:

queueOrPool.asyncRead { dbResult in
    try  {
        let db = try dbResult.get()
        let value = try fetchValue(db)
        DispatchQueue.main.async {
            // do something with value
        }
    } catch {
        // handle database error
    }
}

DatabasePool.asyncRead can perform parallel reads, because this is what database pools are about. DatabaseQueue.asyncRead can not perform parallel reads, but it can help your application dispatching database accesses off the main thread.

If you're into reactive programming, it is asyncRead that fuels the reading Combine publisher and RxSwift observable.

---

Finally, there's concurrentRead and asyncConcurrentRead. These ones allow a very specific optimization with database pools, which is the ability to read from a known database state that has just been written, without blocking other threads that want to write. These database access methods have no purpose in a readonly database.

---

I hope this little tour has clarified things. As you see, your app still has to decide how and when it wants to use the database. The read and asyncRead methods are there to support your decisions.

[gloparco]

What a fantastic writeup @groue (and such a quick response). What you have written has helped me understand better the GRDB options available. Per your advice, it seems that asyncRead with DispatchQueues are the way to go for read-only databases, such as mine.

One suggestion from my side - perhaps you could incorporate what you have written here into the documentation, as it is quite good and may help others in similar circumstances.

Thanks so much!

[groue]

Happy you found your solution!

One suggestion from my side - perhaps you could incorporate what you have written here into the documentation, as it is quite good and may help others in similar circumstances.

Sure. Most of the good parts of the documentation come from questions like yours :-) The bad ones are just waiting for a good question ;-)

[gloparco]

Sorry to follow up on this after I closed it, but this might be a quick answer for you.... is there any difference between using a global DispatchQueue & Combine, inside of a loop, to make individual synchronous reads to a DatabasePool vs. making multiple calls from inside a loop, to a DatabasePool's asyncRead? It seems like the end result might be the same, but I wanted to confirm with you.

Also, could you elaborate on your comment "If you're into reactive programming, it is asyncRead that fuels the reading Combine publisher and RxSwift observable."

I am planning to use Combine subscriptions to make calls to a Publisher that will make calls to the DatabasePool (via reads, per my first paragraph, above). I assume this is a valid approach, but then I wasn't sure what you meant by asyncRead fueling the Combine publisher. Does that mean that you are using Combine behind the scenes?

Thanks for clarifying.

[groue]

is there any difference between using a global DispatchQueue & Combine, inside of a loop, to make individual synchronous reads to a DatabasePool vs. making multiple calls from inside a loop, to a DatabasePool's asyncRead?

I'm sorry @gloparco, but I don't know how to answer this question, because I'm not sure I understand it (I get individual words, but I don't see the picture they paint together). I don't know what you're after, or what kind of problem you want to solve.

I have a read-only database and perform hundreds of reads using a DatabaseQueue.

My advice to you is to make your application work first. Then, if there are problems, divide and conquer, so that you do not have to deal with a huge unsolvable mess. And since you're after performance, run Instruments in order to see where your app spends time.

When you try to understand the runtime behavior of your app, it may help you compare database queues and pools to serial and concurrent dispatch queues. Database accesses are to database queues and pools what work items are to dispatch queues:

someDispatchQueue.sync { /* work item */ }
dbPool.read { db in /* database access */ }

someDispatchQueue.async { /* work item */ }
dbPool.asyncRead { dbResult in /* database access */ }

Use your knowledge of DispatchQueue.

GRBD uses one serial DispatchQueue per SQLite connection.

One DatabaseQueue opens a single SQLite connection, and runs database accesses in a single serial dispatch queue.

One DatabasePool opens several SQLite connections (up to a maximum number), and runs each distinct database access within one of those connections, in its attached serial dispatch queue. By "database access", I mean the invocation of the function argument to dbPool.read, regardless of the number of SQL queries that are run during this database access. When the maximum number of reader connections is used, a read has to wait until one reader turns free. So a pool is a little bit like a concurrent dispatch queue, with a limit to the allowed parallelism.

Does that mean that you are using Combine behind the scenes?

No. But GRDB exposes Combine publishers that are quite handy: GRDB ❤️ Combine.

Note that Combine publishers are built-in GRDB 5 (currently in beta). For GRDB 4, have a look at https://github.com/groue/GRDBCombine.

[gloparco]

That was helpful, despite you not understanding my context (or more precisely, my words failing to effectively communicate my context). To help you better understand, let me describe it this way:

In your examples, you use Players, so let's go with that. If I want to fetch multiple rows for a player, I can do so with a single FetchAll within a DatabasePool read. Now, what if I want to loop through an array of players, fetching all rows for each player? In my original solution, I used Combine to asynchronously call Publisher, which used a DatabaseQueue and read each player's rows sequentially. Because of the number of reads, this was taking too long. To improve this, I now want to use a DatabasePool so that my reads can be concurrent. I would like to be able to loop over the array of Players, fetching all rows for each one, but I want to wait until all rows are fetched for all players before proceeding. So, I am looking for a "best practice" to accomplish this.

So, currently:

main thread -> send an array of Players using Combine using global DispatchQueue -> Custom Publisher that uses DatabaseQueue to read/fetchall rows for every player in the array of Players -> send results back to main thread using receive on

Here is an example code snippet for what I am currently doing. The QueryManager is a custom Publisher that loops over the array of Players, doing a fetchAll using DatabaseQueue reads, returning the result when all Players are populated with the row data:

                QueryManager(players: [Player])
                    .subscribe(on: DispatchQueue.global())
                    .receive(on: DispatchQueue.main)
                    .sink
                        { result in
                        self.populatedPlayers = result
                        }
                    .cancel()

Now, I want to improve performance by utilizing a DatabasePool instead of a DatabaseQueue, so that the reads are no longer sequential.

Thank you greatly for your time! I wouldn't be asking this here unless I thought that others in a similar situation could also benefit.

Cheers!

[groue]

If I want to fetch multiple rows for a player, I can do so with a single FetchAll within a DatabasePool read. Now, what if I want to loop through an array of players, fetching all rows for each player? In my original solution, I used Combine to asynchronously call Publisher, which used a DatabaseQueue and read each player's rows sequentially. Because of the number of reads, this was taking too long.

Please help me understand a little more, and especially "Now, what if I want to loop through an array of players, fetching all rows for each player?"

Maybe "player" is just obscuring the context. Please describe how many tables are involved here, and their eventual relationship. I'm asking because it looks like your application is trying to perform N queries faster. Very often, the solution is to perform a single query instead.

[gloparco]

Oh, I appreciate you thinking outside of the box on this, but let me assure you that I know a thing or two about SQL optimization. Each query itself is quick, but it's the sheer number of queries that is taking too long. And the database is highly normalized and optimized so that a single query cannot replace each of the individual queries. This is a byproduct of having a very large database on a phone and having to weigh the size of the database vs performance. I could easily make this a single query by introducing a lookup table, but it would increase the size of my database, which I am trying to avoid.

Anyway, I won't waste your time with this anymore. I appreciate your responsiveness to my questions. I don't want this to become an architecture discussion on my specific problem. I was just wondering, generally speaking, if there is a best practice in how to go about using a DatabasePool to perform a large number of reads concurrently. It seems to me that you either need to call a DatabasePool with regular reads, using async DispatchQueue, so that each read is on a different thread, or somehow use a single thread and use DatabasePool's asyncRead to accomplish the concurrency. I just can't figure out the mechanics on how to best accomplish this, nor what the best practice is in such a situation.

Thanks again for your time, clarity, and assistance!

[groue]

I was just wondering, generally speaking, if there is a best practice in how to go about using a DatabasePool to perform a large number of reads concurrently.

I admit I don't have any ready-made answer to this question. Such a problem hasn't occured to me yet 😅

[gloparco]

Fair enough! Thanks again, and keep up the good work!