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

问题描述

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

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

和

由AUTOINCREMENT关键字实现的行为是微妙的 与默认行为不同.使用AUTOINCREMENT，行包含 自动选择的ROWID确保具有的ROWID具有 以前从未由同一数据库中的同一表使用过.和 自动生成的ROWID保证是单调的 增加.这些是某些应用程序中的重要属性. 但是，如果您的应用程序不需要这些属性，则应 由于使用了AUTOINCREMENT，因此可能保持默认行为 每行插入时都需要完成其他工作，因此 导致INSERT的运行速度变慢.

以上引用来自 SQLite自动增量

那么，预计会产生什么样的影响，而AUTOINCREMENT的影响会减慢多少呢?

解决方案

我的估计是，我不是统计学家，因此开销要慢 8-12％./p>

我使用结构上相似且简单的 3 表和具有两个TEXT列的表获得了结果，每个表运行 10,000 个插入，重复这5时间在4个设备上.

表1(Dflt列)是仅使用两个TEXT列创建的(因此使用了默认的ROWID).

除了两个TEXT列之外，还使用_id INTEGER PRIMARY KEY AUTOINCREMENT创建了

表2(AI列).

除了两个TEXT列之外，还使用_id INTEGER PRIMARY KEY创建了

表3(无AI列).

因此表2 对插入内容使用略有不同的ROWID选择算法.

使用的四个设备是:-

• (1)Genymotion模拟设备(自定义平板电脑-5.1.0-API 22-1536x2048 )

• (2)一个Onix 10英寸平板电脑(AT101-1116 )

• (3)一个HTC 1 M8(HTC_0PKV1 )

• (4)Lenevo A10-30片剂(Lenovo TB2-X30F )

  我获得的结果是:-

当所有内容仅在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屋！