sqlite-on-the-ui-thread

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQLite on the UI Thread

在UI线程上使用SQLite

SQLite is a lot faster than you may realize. In Flutter for example there is drift, sqlite_async and sqflite which allow for async access of data. But with sqlite3 you can query with sync functions! 🤯

Here is a list view where there are 10000 items and each item is retrieved with a select statement 👀

Source: https://gist.github.com/rodydavis/4a6dca4a2e1afc530ac93e94a76a594a

SQLite, when used effectively, can be a powerful asset for UI-driven applications. Operating within the same process and thread as the UI, it offers a seamless integration that can significantly improve component building.

Async/await does not mean you will be building the most performant applications, and in some cases will incur a performance penalty.

Even with extensive datasets, SQLite demonstrates remarkable efficiency. Its ability to handle millions of rows without compromising speed is a testament to its robust architecture. Contrary to the misconception of being solely a background-thread database, SQLite functions as a process-level library, akin to any other C-based library.

By strategically employing indexes and queries, developers can achieve nanosecond response times and mitigate N+1 query issues. The judicious use of views, indexes, and virtual tables is paramount in optimizing performance.

Complex join operations and the ability to retrieve only essential data for display further underscore SQLite's versatility. For example, when presenting a list view or cards, SQLite can efficiently fetch the required 30 items without undue overhead.

SQLite's flexibility extends beyond single-database scenarios. The ATTACH feature enables the management of multiple databases within a single application. Additionally, the concept of isolates or workers allows for parallel processing, further enhancing performance and responsiveness.

From simple key-value stores to intricate data modeling, SQLite's capabilities are vast. By applying appropriate PRAGMAs, such as WAL mode, developers can tailor SQLite's behavior to meet specific application requirements.

Example PRAGMA:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA journal_size_limit = 67108864;
PRAGMA mmap_size = 134217728;
PRAGMA cache_size = 2000;
PRAGMA busy_timeout = 5000;

SQLite 的速度可能远超你的认知。比如在Flutter 中，有drift、sqlite_async和sqflite这些支持异步数据访问的库。但使用sqlite3，你可以通过同步函数进行查询！ 🤯

这里有一个包含10000条数据的列表视图，每条数据都通过select语句获取 👀

来源: https://gist.github.com/rodydavis/4a6dca4a2e1afc530ac93e94a76a594a

如果使用得当，SQLite可以成为UI驱动型应用的强大助力。它在与UI相同的进程和线程中运行，提供无缝集成，可显著提升组件构建效率。

Async/await并不意味着你能构建出性能最优的应用，在某些情况下还会导致性能损耗。

即使面对海量数据集，SQLite也展现出卓越的效率。它能够在不降低速度的前提下处理数百万行数据，这充分证明了其架构的稳健性。与“SQLite只能在后台线程使用”的误解相反，它是一个进程级库，和其他基于C的库类似。

通过合理使用索引和查询语句，开发者可以实现纳秒级的响应时间，并缓解N+1查询问题。明智地使用视图、索引和虚拟表对优化性能至关重要。

复杂的连接操作以及仅检索显示所需必要数据的能力，进一步凸显了SQLite的多功能性。例如，在展示列表视图或卡片时，SQLite可以高效获取所需的30条数据，不会产生过多开销。

SQLite的灵活性不仅限于单数据库场景。ATTACH功能支持在单个应用中管理多个数据库。此外，isolate或worker的概念可实现并行处理，进一步提升性能和响应速度。

从简单的键值对存储到复杂的数据建模，SQLite的功能十分广泛。通过应用合适的PRAGMA（如WAL模式），开发者可以调整SQLite的行为以满足特定应用需求。

PRAGMA示例:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA journal_size_limit = 67108864;
PRAGMA mmap_size = 134217728;
PRAGMA cache_size = 2000;
PRAGMA busy_timeout = 5000;