标杆

在使用APSW或其他访问SQLite的方式进行任何基准测试之前，您必须了解SQLite如何以及何时进行事务。看见 transaction control 。 APSW does not alter SQLite's behaviour with transactions.

一些访问层试图解释您的SQL并在您背后管理事务，这可能会也可能不会很好地与SQLite一起处理自己的事务。您应该始终自己管理您的交易。例如，要插入1,000行，则将其包装在单个事务中，否则将有1,000个事务，每行一个。一个旋转的硬盘每秒不能处理超过60个事务。

速测

APSW包括一个速度测试器，用于比较不同版本的SQLite、不同的主机系统(重要的硬盘驱动器和控制器)以及SQLite 3和APSW之间的SQLite性能。基础查询基于 SQLite's speed test 。

$ python3 -m apsw.speedtest --help
usage: apsw.speedtest [-h] [--apsw] [--sqlite3] [--correctness]
                      [--scale SCALE] [--database DATABASE] [--tests TESTS]
                      [--iterations N] [--tests-detail] [--dump-sql FILENAME]
                      [--sc-size N] [--unicode UNICODE] [--data-size SIZE]
                      [--hide-runs] [--vfs VFS]
                      [--sqlite-cache SQLITE_CACHE_MB]

Tests performance of apsw and sqlite3 packages

options:
  -h, --help            show this help message and exit
  --apsw                Include apsw in testing [False]
  --sqlite3             Include sqlite3 module in testing [False]
  --correctness         Do a correctness test
  --scale SCALE         How many statements to execute. Each 5 units takes
                        about 1 second per test on memory only databases. [10]
  --database DATABASE   The database file to use [:memory:]
  --tests TESTS         What tests to run
                        [bigstmt,statements,statements_nobindings]
  --iterations N        How many times to run the tests [4]
  --tests-detail        Print details of what the tests do. (Does not run the
                        tests)
  --dump-sql FILENAME   Name of file to dump SQL to. This is useful for
                        feeding into the SQLite command line shell.
  --sc-size N           Size of the statement cache. [128]
  --unicode UNICODE     Percentage of text that is non-ascii unicode
                        characters [0]
  --data-size SIZE      Duplicate the ~50 byte text column value up to this
                        many times (amount randomly selected per row)
  --hide-runs           Don't show the individual iteration timings, only
                        final summary
  --vfs VFS             Use the named vfs. 'passthru' creates a dummy APSW
                        vfs. You need to provide a real database filename
                        otherwise the memory vfs is used.
  --sqlite-cache SQLITE_CACHE_MB
                        Size of the SQLite in memory cache in megabytes.
                        Working data outside of this size causes disk I/O. [2]


$ python3 -m apsw.speedtest --tests-detail
bigstmt:

  Supplies the SQL as a single string consisting of multiple
  statements.  apsw handles this normally via cursor.execute while
  sqlite3 requires that cursor.executescript is called.  The string
  will be several kilobytes and with a scale of 50 will be in the
  megabyte range.  This is the kind of query you would run if you were
  restoring a database from a dump.  (Note that sqlite3 silently
  ignores returned data which also makes it execute faster).

statements:

  Runs the SQL queries but uses bindings (? parameters). eg::

    for i in range(3):
       cursor.execute("insert into table foo values(?)", (i,))

  This test has many hits of the statement cache.

statements_nobindings:

  Runs the SQL queries but doesn't use bindings. eg::

    cursor.execute("insert into table foo values(0)")
    cursor.execute("insert into table foo values(1)")
    cursor.execute("insert into table foo values(2)")

  This test has no statement cache hits and shows the overhead of
       having a statement cache.

  In theory all the tests above should run in almost identical time
  as well as when using the SQLite command line shell.  This tool
  shows you what happens in practise.