Significant performance difference between SQLDelight and C++ on iOS

[schmidt9]

I replicate here issue from SQLDelight repo as per advise of @AlecStrong, he assumes the issue may have something to do with SQLiter

First I faced this notable speed difference on iOS on simple select queries - C++ was about 4 times faster than KMM implementation using SQLDelight.

So I decided to prepare a test including bulk inserts and selects for both C++ and KMM with 100 000 records, results are: for inserts C++ is ~5 times faster than KMM, for selects C++ is ~7 times faster than KMM.

I prepared a test project with both implementations here

Is there something to do to improve SQLDelight performance? Maybe some tuning which I'm not aware of? Or is it generally a Kotlin issue?

My test repo

[benasher44]

I'd be curious to know how your benchmarks change with the latest sqliter (present in the 1.5 SQLDelight release), since now it's using K/N cinterop to interop with sqlite3 directly, instead of going through a c++ layer

[kpgalligan]

i'd also be curious to see this with direct sqliter vs "sqldelight". The last round of changes changed both sqligher's communication (cinterop vs direct C++), and includes significant performance improvements to the sqldelight driver.

[kpgalligan]

Tried bumping sqldelight and getting this to run, but haven't been able to get it to build, so will have to get to it later. Currently having a weird linker issue.

[kpgalligan]

Numbers running sqldelight 1.5.0.

=== Kotlin ===

start createProjects()
createProjects() elapsed time 0.5796810388565063
start fetchProjects()
fetchProjects() elapsed time 0.21331489086151123, count: 100000

=== CPP ===

start createProjects()
createProjects() elapsed time 0.2074589729309082
start fetchProjects()
fetchProjects() elapsed time 0.044654011726379395, count: 100000

So, C++ doing direct sqlite calls compared to Kotlin Native, which has GC, etc, and using SQLDelight, which is sitting some layers above direct sqlite, isn't going to be a great comparison. I think this really either needs to be direct sqliter calls compared to C++ sqlite, or CoreData (or similar) compared to SQLDelight. Also, you probably want to do a few loops and average.

[kpgalligan]

I added direct kotlin calls for the inserts, just to get a sense: https://github.com/kpgalligan/SQLDelightPerformanceTest

Results:

=== Kotlin ===

start createProjects()
createProjects() elapsed time 0.5448390245437622
start fetchProjects()
fetchProjects() elapsed time 0.20031392574310303, count: 100000
start createProjectsDirect()
createProjectsDirect() elapsed time 0.26966798305511475
direct total (just checking) 100000

=== CPP ===

start createProjects()
createProjects() elapsed time 0.19979500770568848
start fetchProjects()
fetchProjects() elapsed time 0.04803192615509033, count: 100000

Sqliter vs C++ is 0.26966798305511475 vs 0.19979500770568848, so like 0.270 vs 0.200. That's like 35% slower for Kotlin. That also includes potential time differences in other things. For example, each project name gets created dynamically, and I would imagine Kotlin string interpolation performs worse than C++.

I'll probably check those numbers again in the morning, as I expected the sqliter to C++ comparison to be a bit worse.

[andersio]

Worth mentioning that constant folding for indentation stripping of the string literals is turned on only since Kotlin 1.5+. So if this is benched on Kotlin 1.4, stripping identiation from SQL strings will contribute some visible differences, especially with repeated DML statements.

[kpgalligan]

Working 1.5 updates this week. SQLDelight should also be going out pretty soon. Will revisit when those release to see if there's any measurable difference, although we probably should have a more comprehensive benchmark.

[schmidt9]

Tried bumping sqldelight and getting this to run, but haven't been able to get it to build, so will have to get to it later. Currently having a weird linker issue.

@kpgalligan how did you solve iOS build issue? Have failing build after bumping SQLDelight to 1.5.0

[schmidt9]

@kpgalligan Have successfully run your fork on Release config on iPhone SE, getting about 2x speed improvement for createProjects() (~4.7 -> ~2.62 sec), other metrics stay the same. May it be due to SQLDelight 1.5.0 compared to 1.4.4?

[kpgalligan]

SQLDelight 1.5.0 has significant native driver improvements. The next version should have a few more (largely coming from @andersio, thanks!), although I would guess not full multiples (but we'll see).

As for iOS build issues, I had to hack around for a while to get it to run. I removed Android entirely (not needed), then reran cocoapods, Xcode build, etc. There was no clear "change this" to get it running.