Threaded Push Operations

[adrianhall]

Hey folks,

v5.0.6 will have the possibility of threaded push operations. You can define the number of threads you want to use for pushing and the sync context will do the work for you. By default, we use the same logic as before. However, you can define up to 8 threads, depending on your platform. I wrote a small sample app to test this out.

The app takes a number of items and a number of threads, then simulates both inserts and deletes of that number of items. First things first - these are raw numbers taken on an Android emulator using an App Service (S1 - 1 core) and an Azure SQL backend hosted in West US (which is the closest data center to me).

10 items

# Threads	Insert	Push-Inserts	Deletes	Push-Deletes
1	26.4ms	134.8ms	155.1ms	98.4ms
2	33.3ms	89.3ms	115.8ms	56.0ms
4	29.8ms	100.9ms	122.0ms	48.9ms

So, first things first - each number is "average time for one item" so we can compare across # items and # threads. The insert and delete columns operate only against the SQLite database, so you are seeing a number that I can't do much about. The push-insert and push-deletes, however, show the timing to the remote server. Remember that a push is also a read and a write to the SQLite database, so it's not all remote data. With 10 items, you are looking at no contention for resources, so this should be relatively sane.

100 items

# Threads	Insert	Push-Inserts	Deletes	Push-Deletes
1	22.2ms	115.7ms	138.8ms	102.1ms
2	24.3ms	69.6ms	92.5ms	59.1ms
4	23.5ms	53.5ms	75.6ms	35.1ms

With 100 items, you get a distinguishing characteristic - the pushes get faster with more threads. This is actually the sweet spot. Above about 150 items in queue, the garbage collector got quite busy, resulting in variance that wasn't due to the remote server changes.

1000 items

# Threads	Insert	Push-Inserts	Deletes	Push-Deletes
1	22.6ms	110.2ms	131.9ms	100.5ms
2	22.7ms	62.0ms	84.7ms	51.6ms
4	23.1ms	41.3ms	64.7ms	30.3ms

Remember the variance in the Insert and Deletes columns is purely SQLite - also, note how bad SQLite is at deleting things. There was a lot of garbage collection in the 1000 test, resulting in a significant delay in processing - something to keep in mind when thinking about pushing large amounts of data.

Final notes

Firstly, let's state the obvious. You lose a couple of things by going "multi-threaded". Specifically, you no longer can guarantee the order of the updates. I saw this with the 1000 item runs - the ordering was "close" but definitely not in order for the majority of the run. Secondly, you no longer can "abort" a batch - the batch runs to completion. This means you no longer have the ability to deal with conflicts on a per-conflict basis - you have to deal with them en-masse.

A note about "8 threads" - even though it's possible, I don't recommend it on mobile devices (feel free on WinUI3, UWP, etc.) That's because those devices have a HTTP pool of just 4 threads. If you specify 8 threads, the times actually go up from the 4 threads numbers (probably because the extra 4 threads are just queued waiting for a HTTP pool thread to be available).

v5.0.6 will be out soon - the code for the parallel queue runner (issue #359) is being checked in today (will be merged once the PR tests pass).

... and if anyone can fix my app so that it shows the "LogMessages" and updates the UI while the test is running, I'd appreciate it!