New submission from Michael Schwarz <michi.schw...@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 <rep...@bugs.python.org>
<http://bugs.python.org/issue9303>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
http://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
