Make quick-sqlite 2x quicker?

[mjmasn]

DISCLAIMER 1: This is not a PR intended to be merged, use at your own risk DISCLAIMER 2: I don't really know anything about C++

---

Intro

This PR is intended to spark a discussion about improving the performance of this package. Sure it's like 5-8x faster than non-JSI packages but it's still not perfect. For background, we deal with some fairly large (for mobile anyway) data sets with up to 300000 records per table, maybe with 15-30 text/number/boolean columns on average. Selecting all these rows might take 20-30 seconds or more depending on the device. Obviously selecting 30 columns across 300k records at once is a worst case scenario but if we can improve the worst case performance, we'll probably be improving the best cases too, helping our apps feel a lot more responsive.

Potential Performance Bottlenecks

I've identified two major bottlenecks but there could be others.

QuickValue

Digging into the package code, I can see that there is an intermediate QuickValue state created for every bit of data passing from JSI to SQLite and back again. It feels like this isn't necessary, and indeed for query results I have been able to make it work without. I know @ospfranco has handed this package over but maybe he can shed some light onto the original reasons for this?

JSI Objects

That gets us a bit more perf, but converting the SQLite results to JSI objects is still pretty slow. I had an inkling that using arrays would be more efficient here. Adding this feature in gets us a full 2x performance increase vs the current intermediate values + JSI objects.

Other???

I think the biggest issue is likely to be that creating JSI strings is slow, but that may be outside the scope of this package to solve. But maybe there are other issues that a more knowledgable eye could spot!

The PR

This PR is what I've essentially hacked together so far to improve the two main issues. I've duplicated the execute and executeAsync methods as execute2 and executeAsync2 to allow performance to be compared.

Both of these new methods can take an additional returnArrays boolean parameter to determine whether to return results as (false) an array of objects, and (true) an array of arrays.

What I mean by this is:

[
    { my_field: 'hello', my_field_2: 8 },
    { my_field: 'goodbye', my_field_2: 45 }
]

vs

[
    ['hello', 8],
    ['goodbye', 45]
]

All seems well but it's probably quite likely I've done bad things given I hadn't touched C++ at all before looking into this.

I guess my hope is that this can get some eyes and feedback and hopefully form a basis for an actual PR into the project that will give users of the package a nice performance boost.

Benchmarks

Based on setting up the benchmark by inserting 200000 rows into the database, each with 30 text columns containing random 64-character strings, then running each test 3 times to ensure the results were accurate.

These specific tests were performed on a Google Pixel 6 Pro with Android 14 but I've seen similar performance gains on iOS simulator, iPhone 7 Plus, iPad 2019, Samsung Galaxy Tab A 2019.

Selecting 5000 records

Method	Limit	Sync (ms)	Async (ms)
Current	5000	382	372
Current	5000	354	445
Current	5000	394	493
New (Objects)	5000	276	246
New (Objects)	5000	272	232
New (Objects)	5000	276	232
New (Arrays)	5000	204	161
New (Arrays)	5000	225	162
New (Arrays)	5000	204	184

Selecting 50000 records

Method	Limit	Sync (ms)	Async (ms)
Current	50000	3094	3480
Current	50000	3239	3594
Current	50000	3259	3719
New (Objects)	50000	2319	2589
New (Objects)	50000	2470	2659
New (Objects)	50000	2384	2660
New (Arrays)	50000	1702	1866
New (Arrays)	50000	1632	1862
New (Arrays)	50000	1656	1868

Selecting 200000 records

Method	Limit	Sync (ms)	Async (ms)
Current	200000	14108	n/a (OOM)
Current	200000	13747	n/a (OOM)
Current	200000	14229	n/a (OOM)
New (Objects)	200000	11918	13395
New (Objects)	200000	12685	13638
New (Objects)	200000	12780	14277
New (Arrays)	200000	6932	7872
New (Arrays)	200000	7448	8254
New (Arrays)	200000	7479	8443