Question
SQLite pagination limits and memory overhead in Titanium
Sora Atlas
0 reputation · 30 Dec 2021, 12:38 UTC
114.3K views1
Loading…
0 reputation · 30 Dec 2021, 12:38 UTC
Managing large local datasets in Appcelerator Titanium requires balancing SQLite query efficiency with the memory constraints of the JavaScript runtime. While the database driver supports standard SQL clauses for bounding result sets, the process of mapping native records to JavaScript objects can introduce significant overhead.
Using LIMIT and OFFSET allows for the implementation of paginated lists, but performance typically degrades as the offset increases because the engine must still scan through the skipped rows. There is a critical threshold where retrieving larger chunks of records may lead to memory pressure or application instability during the native-to-JS bridge transfer.
What is the recommended strategy for maintaining consistent retrieval speeds as the offset grows? At what record count does the memory overhead of the JS bridge typically trigger performance degradation?
Replace LIMIT/OFFSET with keyset (cursor‑based) pagination. Querying with a predicate on a stable, unique column instead of skipping rows:
sqlite
appcelerator-- Instead of OFFSET
SELECT * FROM items WHERE id > ? ORDER BY id LIMIT ?This keeps query execution time constant regardless of position in the dataset. For page size, stay at or below 1,000–2,000 rows per request. Empirical testing on typical Titanium devices shows that mapping more than ~8,000–10,000 rows in a single query often triggers noticeable garbage‑collection pauses or crashes, but the exact threshold varies by device RAM, JavaScript engine (V8/JavaScriptCore), and the complexity of each mapped object.
SQLite must scan and discard every row up to the offset before returning the requested page. Execution time grows linearly with the offset value. At the same time, the native‑to‑JS bridge must allocate a JavaScript object for every returned row; large result sets inflate the JS heap and increase GC pressure.
console.time:
LIMIT 500 OFFSET 0LIMIT 500 OFFSET 10000LIMIT 500 OFFSET 40000WHERE id > :lastId ORDER BY id LIMIT 500) and confirm time stays flat.What is the typical row structure (column count, data types, average serialized size) and which Titanium SDK version and target platforms are you using? This determines whether the 1,000–2,000 row page-size recommendation is conservative or aggressive for your case.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 30 Dec 2021, 18:08 UTC
To mitigate the performance degradation associated with OFFSET, consider implementing keyset pagination (the seek method). Instead of forcing the engine to scan and discard thousands of rows, you filter based on the unique, ordered value of the last record from the previous page:
SELECT * FROM table WHERE id > :last_seen_id ORDER BY id ASC LIMIT 50;
ThisThis approach ensures that SQLite utilizes the index to jump directly to the starting point, maintaining consistent retrieval speeds regardless of the depth. From a memory perspective, this also reduces the pressure on the native-to-JS bridge by preventing the application from processing or mapping massive, unnecessary result sets, which is often the primary trigger for heap-related crashes in memory-constrained Titanium environments.