Slow Query Execution for Tables with Diverse Data

[linhttbk97]

I am experiencing a perplexing issue with the wa-sqlite regarding the execution speed of queries on tables with varying data. To provide context, when I run a query on a table containing 17284 rows, each with identical data, the query performs exceptionally fast. However, when the data in these rows differs from one another, the query execution becomes noticeably slow

For Test table that have 17284 rows, simple query Select Count(*) got 53ms
query done for SELECT Count(*) FROM Test took: 53 ms
But for Test2 table that have 15000 rows, but the values on each row are differs from each other
query done for SELECT Count(*) FROM Test2 took: 309 ms
example row for Test2:
id: 16,
name: "Student name 1704788636264",
email: "Student email 1704788636264",
phone "Student phone 1704788636264",
age: "Student age 1704788636264"

[rhashimoto]

If the results are slow but correct, then this is unlikely to be a wa-sqlite bug and will be more about general SQLite performance optimization. It may be more noticeable on a browser because the storage options are slower.

For most uses, SQLite performance will be highly correlated with the number (and type) of I/O operations, particularly reads, writes, and syncs - keeping I/O from happening is usually the best approach. Improvements typically involve adjusting the database page size and cache size, keeping temporary files in memory if possible, write fewer larger transactions (instead of lots of small transactions), and optimizing your queries.