1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
|
<!DOCTYPE html>
<html class="writer-html5" lang="en" data-content_root="./">
<head>
<meta charset="utf-8" /><meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Benchmarking — APSW 3.46.0.1 documentation</title>
<link rel="stylesheet" type="text/css" href="_static/pygments.css?v=72bcf2f2" />
<link rel="stylesheet" type="text/css" href="_static/css/theme.css?v=19f00094" />
<link rel="stylesheet" type="text/css" href="_static/apsw.css?v=3c7e2631" />
<link rel="shortcut icon" href="_static/favicon.ico"/>
<!--[if lt IE 9]>
<script src="_static/js/html5shiv.min.js"></script>
<![endif]-->
<script src="_static/jquery.js?v=5d32c60e"></script>
<script src="_static/_sphinx_javascript_frameworks_compat.js?v=2cd50e6c"></script>
<script src="_static/documentation_options.js?v=d98f5d2b"></script>
<script src="_static/doctools.js?v=9a2dae69"></script>
<script src="_static/sphinx_highlight.js?v=dc90522c"></script>
<script src="_static/js/theme.js"></script>
<link rel="author" title="About these documents" href="about.html" />
<link rel="index" title="Index" href="genindex.html" />
<link rel="search" title="Search" href="search.html" />
<link rel="copyright" title="Copyright" href="copyright.html" />
<link rel="next" title="Copyright and License" href="copyright.html" />
<link rel="prev" title="sqlite3 module differences" href="pysqlite.html" />
</head>
<body class="wy-body-for-nav">
<div class="wy-grid-for-nav">
<nav data-toggle="wy-nav-shift" class="wy-nav-side">
<div class="wy-side-scroll">
<div class="wy-side-nav-search" >
<a href="index.html" class="icon icon-home">
APSW
<img src="_static/apswlogo.png" class="logo" alt="Logo"/>
</a>
<div class="version">
3.46.0.1
</div>
<div role="search">
<form id="rtd-search-form" class="wy-form" action="search.html" method="get">
<input type="text" name="q" placeholder="Search docs" aria-label="Search docs" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div><div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="Navigation menu">
<ul class="current">
<li class="toctree-l1"><a class="reference internal" href="about.html">About</a></li>
<li class="toctree-l1"><a class="reference internal" href="tips.html">Tips</a></li>
<li class="toctree-l1"><a class="reference internal" href="example.html">Example/Tour</a></li>
<li class="toctree-l1"><a class="reference internal" href="install.html">Installation and customization</a></li>
<li class="toctree-l1"><a class="reference internal" href="extensions.html">Extensions</a></li>
<li class="toctree-l1"><a class="reference internal" href="apsw.html">APSW Module</a></li>
<li class="toctree-l1"><a class="reference internal" href="connection.html">Connections to a database</a></li>
<li class="toctree-l1"><a class="reference internal" href="cursor.html">Cursors (executing SQL)</a></li>
<li class="toctree-l1"><a class="reference internal" href="blob.html">Blob Input/Output</a></li>
<li class="toctree-l1"><a class="reference internal" href="backup.html">Backup</a></li>
<li class="toctree-l1"><a class="reference internal" href="vtable.html">Virtual Tables</a></li>
<li class="toctree-l1"><a class="reference internal" href="vfs.html">Virtual File System (VFS)</a></li>
<li class="toctree-l1"><a class="reference internal" href="shell.html">Shell</a></li>
<li class="toctree-l1"><a class="reference internal" href="bestpractice.html">Best Practice</a></li>
<li class="toctree-l1"><a class="reference internal" href="ext.html">Various interesting and useful bits of functionality</a></li>
<li class="toctree-l1"><a class="reference internal" href="exceptions.html">Exceptions and Errors</a></li>
<li class="toctree-l1"><a class="reference internal" href="execution.html">Execution and tracing</a></li>
<li class="toctree-l1"><a class="reference internal" href="dbapi.html">DBAPI notes</a></li>
<li class="toctree-l1"><a class="reference internal" href="pysqlite.html">sqlite3 module differences</a></li>
<li class="toctree-l1 current"><a class="current reference internal" href="#">Benchmarking</a><ul>
<li class="toctree-l2"><a class="reference internal" href="#speedtest">speedtest</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="copyright.html">Copyright and License</a></li>
<li class="toctree-l1"><a class="reference internal" href="changes.html">Change History</a></li>
<li class="toctree-l1"><a class="reference internal" href="py-modindex.html">Module Index</a></li>
<li class="toctree-l1"><a class="reference internal" href="genindex.html">Index</a></li>
</ul>
</div>
</div>
</nav>
<section data-toggle="wy-nav-shift" class="wy-nav-content-wrap"><nav class="wy-nav-top" aria-label="Mobile navigation menu" >
<i data-toggle="wy-nav-top" class="fa fa-bars"></i>
<a href="index.html">APSW</a>
</nav>
<div class="wy-nav-content">
<div class="rst-content style-external-links">
<div role="navigation" aria-label="Page navigation">
<ul class="wy-breadcrumbs">
<li><a href="index.html" class="icon icon-home" aria-label="Home"></a></li>
<li class="breadcrumb-item active">Benchmarking</li>
<li class="wy-breadcrumbs-aside">
<a href="_sources/benchmarking.rst.txt" rel="nofollow"> View page source</a>
</li>
</ul><div class="rst-breadcrumbs-buttons" role="navigation" aria-label="Sequential page navigation">
<a href="pysqlite.html" class="btn btn-neutral float-left" title="sqlite3 module differences" accesskey="p"><span class="fa fa-arrow-circle-left" aria-hidden="true"></span> Previous</a>
<a href="copyright.html" class="btn btn-neutral float-right" title="Copyright and License" accesskey="n">Next <span class="fa fa-arrow-circle-right" aria-hidden="true"></span></a>
</div>
<hr/>
</div>
<div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article">
<div itemprop="articleBody">
<section id="benchmarking">
<span id="id1"></span><h1>Benchmarking<a class="headerlink" href="#benchmarking" title="Link to this heading"></a></h1>
<p>Before you do any benchmarking with APSW or other ways of accessing
SQLite, you must understand how and when SQLite does transactions. See
<a class="reference external" href="https://sqlite.org/lockingv3.html#transaction_control">transaction control</a>. <strong>APSW does
not alter SQLite’s behaviour with transactions.</strong></p>
<p>Some access layers try to interpret your SQL and manage transactions
behind your back, which may or may not work well with SQLite also
doing its own transactions. You should always manage your transactions
yourself. For example to insert 1,000 rows wrap it in a single
transaction, otherwise you will have 1,000 transactions, one per row.
A spinning hard drive can’t do more than 60 transactions per second.</p>
<section id="speedtest">
<span id="id2"></span><h2>speedtest<a class="headerlink" href="#speedtest" title="Link to this heading"></a></h2>
<p>APSW includes a speed tester to compare SQLite performance across
different versions of SQLite, different host systems (hard drives and
controllers matter) as well as between sqlite3 and APSW. The
underlying queries are based on <a class="reference external" href="https://sqlite.org/src/file?name=tool/mkspeedsql.tcl">SQLite’s speed test</a>.</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>$ 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.
</pre></div>
</div>
</section>
</section>
</div>
</div>
<footer><div class="rst-footer-buttons" role="navigation" aria-label="Footer">
<a href="pysqlite.html" class="btn btn-neutral float-left" title="sqlite3 module differences" accesskey="p" rel="prev"><span class="fa fa-arrow-circle-left" aria-hidden="true"></span> Previous</a>
<a href="copyright.html" class="btn btn-neutral float-right" title="Copyright and License" accesskey="n" rel="next">Next <span class="fa fa-arrow-circle-right" aria-hidden="true"></span></a>
</div>
<hr/>
<div role="contentinfo">
<p>© <a href="copyright.html">Copyright</a> 2004-2024, Roger Binns <rogerb@rogerbinns.com>.
<span class="lastupdated">Last updated on Jun 16, 2024.
</span></p>
</div>
Built with <a href="https://www.sphinx-doc.org/">Sphinx</a> using a
<a href="https://github.com/readthedocs/sphinx_rtd_theme">theme</a>
provided by <a href="https://readthedocs.org">Read the Docs</a>.
</footer>
</div>
</div>
</section>
</div>
<script>
jQuery(function () {
SphinxRtdTheme.Navigation.enable(true);
});
</script>
</body>
</html>
|
