在 Android 上为 SQLite 使用 AUTOINCREMENT 的开销是多少? [英] What are the overheads of using AUTOINCREMENT for SQLite on Android?

问题描述

在 SQLite 文档中，它包括以下内容:-

<块引用>

AUTOINCREMENT 关键字强加了额外的 CPU、内存、磁盘空间和磁盘 I/O 开销，如果不是严格需要，应该避免.这是通常不需要.

和

<块引用>

AUTOINCREMENT 关键字实现的行为是巧妙的与默认行为不同.使用 AUTOINCREMENT，行自动选择的 ROWID 保证具有具有以前从未被同一个数据库中的同一个表使用过.而自动生成的 ROWID 保证是单调的增加.这些是某些应用中的重要特性.但是如果你的应用程序不需要这些属性，你应该自使用 AUTOINCREMENT 以来，可能会保持默认行为需要在插入每一行时完成额外的工作，因此导致 INSERT 运行速度变慢.

以上引述来自

当一切都只在 1 个事务中运行时，结果更有利(即 beginTransaction(); 在任何插入之前，使用 setTransactionSuccessful(); 和 endTransaction(); 在所有插入之后(对于所有表，即整个 150,000 次插入)，例如:-

两个表的比较，突出了使用事务对性能的好处.

In the SQLite Documentation, it includes the following :-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

and

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing. These are important properties in certain applications. But if your application does not need these properties, you should probably stay with the default behavior since the use of AUTOINCREMENT requires additional work to be done as each row is inserted and thus causes INSERTs to run a little slower.

The above quotes are from SQLite Autoincrement

So what sort of impact is to be expected and by how much is AUTOINCREMENT a little slower?

解决方案

My estimate, noting that I'm not a statistician, is that the overhead is something like 8-12% slower.

I obtained the results using 3 structurally similar and simple tables with two TEXT columns, running 10,000 inserts per each table, repeating this 5 times on 4 devices.

Table 1 (Dflt column) was created with just the two TEXT Columns (thus utilising the default ROWID).

Table 2 (AI column) was created using _id INTEGER PRIMARY KEY AUTOINCREMENT in addition to the two TEXT columns.

Table 3 (No AI column) was created using _id INTEGER PRIMARY KEY in addition to the two TEXT columns.

So Table 2 uses the slightly different ROWID selection algorithm for inserts.

The four devices used were :-

• (1) A Genymotion emulated device (Custom Tablet - 5.1.0 - API 22 - 1536x2048 )

• (2) An Onix 10" tablet (AT101-1116 )

• (3) An HTC 1 M8 (HTC_0PKV1 )

• (4) A Lenevo A10-30 tablet (Lenovo TB2-X30F )

  The results I obtained were :-

The results are more favourable when everything is run in just 1 transaction (i.e beginTransaction(); before any inserts, with setTransactionSuccessful(); and endTransaction(); after all inserts (for all tables i.e. the entire 150,000 inserts), e.g. :-

Comparison of the two tables, highlights the benefits that using transactions can have upon performance.

这篇关于在 Android 上为 SQLite 使用 AUTOINCREMENT 的开销是多少?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！