[New-bugs-announce] [issue9303] Migrate sqlite3 module to _v2 API to enhance performance

[Michael Schwarz]

New submission from Michael Schwarz <michi.schwarz at gmail.com>:

The Python sqlite module currently uses some deprecated API [0] of SQLite. These are functions that have a counterpart with _v2 appended to their name.

The SQLite query planner will not use certain optimizations when using the old API. For example, as documented in [1], using a statement with a GLOB or LIKE operator where the pattern is parametrized will only use an appropriate index if sqlite3_prepare_v2() is used instead of sqlite3_prepare().

Following is an example of such a query. When executed, table 'test' is scanned row by row, which requires all data in the table to be loaded from disk.

cursor.execute('create table test(a text)')
cursor.execute('create index test_index on test(a)')
# insert some data here
cursor.execute('select * from test where a glob ?', ['prefix*'])

When the same query is executed in the sqlite3 command line utility, the index 'test_index' is used instead.

sqlite> create table test(a text);
sqlite> create index test_index on test(a);
sqlite> explain query plan select * from test where a glob 'prefix*';
order	from	detail
0	0	TABLE test WITH INDEX test_index

The query in this example is not parametrized as parameters can't be filled in when using the sqlite3 command line utility. This is just to show that the schema and the query allow the index to be used with certain pattern strings.

[0]: http://www.sqlite.org/c3ref/funclist.html
[1]: http://www.sqlite.org/optoverview.html#like_opt

Michael

----------
components: Extension Modules
messages: 110739
nosy: feuermurmel
priority: normal
severity: normal
status: open
title: Migrate sqlite3 module to _v2 API to enhance performance
type: performance
versions: Python 3.1

_______________________________________
Python tracker <report at bugs.python.org>
<http://bugs.python.org/issue9303>
_______________________________________