[Onyx Audit] Performance improvements to SQLiteProvider

[fabioh8010]

Parent issue.

Coming from here.

There are some performance improvements in SQLiteProvider I discovered a while ago that we could prioritize now to ship them faster. We kind have the code ready, we just need to double-check the improments/logic and spin up a proposal.

Issue Owner

Current Issue Owner: @fabioh8010

[fabioh8010]

I will work on this one starting tomorrow.

[fabioh8010]

Updates:

• Started checking my old commit, and will begin measurements again with baseline vs my changes but without the small batch condition logic in multiGet.

[fabioh8010]

Updates:

• Started to get the baseline measurements and ended up taking a closer look at a old PR of mine that attempts to fix the performance metrics. I think I was finally able to understand it and get something workable (at least for mobile), so I might be able to ship this improvement too.
• With the performance metrics fixed it will be easier to get these metrics, which I use a complicated way currently.
• I'm using my playground branch to measure.

[fabioh8010]

Updates:

I was able to finish measurements today on both baseline (main) and solution branch.

These are the improvements:

• multiGet optimization (chunked / temp-table join): Reduce query parsing and variable binding overhead from very long IN (?, ?, ...) clauses, and improve scalability for large batches. The initial solution was to perform the current IN clauses logic on datasets below 100 records, and the new temp-table solution on datasets above it.
• Removing json() wrappers from SQL statements: Eliminate redundant json() calls around parameters, which parse and re-encode JSON strings inside SQLite.

Operation	Records	Before (ms)	After (ms)	Difference (%)
multiGet	100	14.24	18.66	+31.02% (worse than current logic)
	500	32.24	32.53	+0.90% (break-even point)
	1000	46.40	41.20	−11.20%
	10000	407.03	308.08	−24.31%
multiMerge	100	12.37	11.39	−7.92%
	1000	78.80	68.45	−13.13%
	10000	1884.70	1006.36	−46.60%
multiSet	100	7.51	7.25	−3.46%
	1000	59.13	52.58	−11.09%
	10000	553.57	460.33	−16.84%

For multiGet it looks like the temp-table solution is worse than current logic below 500 records, but it start to be more performant above 500 records, so I adjusted my solution to use the 500 threshold.

I will perform some testing with real scenarios around the app in a heavy account to see if I catch bugs, if everything is okay we can move with a proposal.

[melvin-bot]

@fabioh8010 Eep! 4 days overdue now. Issues have feelings too...