classification
Title: Migrate sqlite3 module to _v2 API to enhance performance
Type: enhancement Stage: resolved
Components: Extension Modules Versions: Python 3.7
process
Status: closed Resolution: fixed
Dependencies: Superseder:
Assigned To: ghaering Nosy List: Arfrever, Dima.Tisnek, Jim.Jewett, Robin.Schreiber, berker.peksag, feuermurmel, ghaering, haypo, larry, palaviv, petri.lehtinen, pitrou, serhiy.storchaka
Priority: normal Keywords: patch

Created on 2010-07-19 12:08 by feuermurmel, last changed 2017-04-09 09:12 by berker.peksag. This issue is now closed.

Files
File name Uploaded Description Edit
sqlite.patch Robin.Schreiber, 2012-04-14 15:40 review
9303.patch palaviv, 2016-04-30 10:35 review
Pull Requests
URL Status Linked Edit
PR 359 merged palaviv, 2017-02-28 20:39
PR 552 closed dstufft, 2017-03-31 16:36
Messages (20)
msg110739 - (view) Author: Michael Schwarz (feuermurmel) Date: 2010-07-19 12:08
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
msg110741 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2010-07-19 12:24
Yes, the sqlite module uses the old API, and is written to work with older SQLite releases and their respective bugs as well.

Using the new API will mean requiring newer SQLite releases.

If we do this, then this is the chance to remove all the obscure backwards compatibility code with older SQLite releases.
msg158169 - (view) Author: Robin Schreiber (Robin.Schreiber) * Date: 2012-04-12 21:04
Apparently this issue has not been dealt with for quite some time now. As a prospective GSoC student, I still need to submit a patch to pass final screening and I thought, that the needed patch here would be quite suitable for a beginner. I plan to submit a patch, which simply replaces the deprecated method calls with the new ones. 
Maybe we can also remove some parts of the module code, because of the new semantics of prepare_v2(), however I would first like to hear Gerhards opinion on that :-)
msg158228 - (view) Author: Jim Jewett (Jim.Jewett) Date: 2012-04-13 19:20
I can't speak for GSoC or Gerhard, but it strikes me as a reasonable first step.  An alternatives woube be writing it with fallbacks (so older sqlite can still be used, though less efficiently).  It would also be nice to clean up at least one call with compatibility cruft.  That said, don't hesitate to submit a patch that does *something*, and then replace it with more comprehensive patches later.
msg158261 - (view) Author: Robin Schreiber (Robin.Schreiber) * Date: 2012-04-14 12:14
I have now submitted a patch, which swapped out all the necessary calls. Tests are all running as expected. I will now try to remove some backwards compatibility code.
msg161183 - (view) Author: Petri Lehtinen (petri.lehtinen) * (Python committer) Date: 2012-05-20 04:25
What Sqlite version are you targeting? Some systems use quite old Sqlite versions, see #14572 for example.
msg180026 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2013-01-15 16:01
This would be nice to have in 3.4. When were the _v2 APIs introduced?

See also issue13773 which uses sqlite3_open_v2() to pass some flags.
msg233463 - (view) Author: Dima Tisnek (Dima.Tisnek) Date: 2015-01-05 13:51
Is there any hope?
Surely sqlite backwards compatibility is not an issue any longer...
msg233468 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2015-01-05 17:18
ok, i will have to look into this
msg248821 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2015-08-19 09:26
The externally maintained version of the sqlite3 module has now been switched to the v2 statement API. pysqlite is for Python 2.7 only. I'd suggest to revisit this for Python 3.6 and then try to port most fixes from pysqlite to the sqlite3 module.
msg264548 - (view) Author: Aviv Palivoda (palaviv) * Date: 2016-04-30 10:35
I made a new patch to fix this issue. I left a fallback to the old API as Jim suggested. In addition to the changes in Robin`s patch I changed sqlite3_close to sqlite3_close_v2 if available.

This solves issue 26187 as well.
msg271826 - (view) Author: Dima Tisnek (Dima.Tisnek) Date: 2016-08-02 15:28
Can someone review Aviv's patch?
msg288726 - (view) Author: Aviv Palivoda (palaviv) * Date: 2017-02-28 20:51
I opened a PR. This actually is a bugfix in addition to an enhancement as it solves issue 26187 as well.
msg288754 - (view) Author: Serhiy Storchaka (serhiy.storchaka) * (Python committer) Date: 2017-03-01 11:44
Is detection in the configure script needed? What are SQLite versions in which _v2 APIs were added? What is minimal supported SQLite version?

Don't forget about Windows where autotools are not used.
msg288769 - (view) Author: Aviv Palivoda (palaviv) * Date: 2017-03-01 17:44
from https://www.sqlite.org/changes.html:
sqlite3_open_v2 - 2007-09-04 (3.5.0) alpha
sqlite3_prepare_v2 - 2007-01-04 (3.3.9)
sqlite3_close_v2 - 2012-09-03 (3.7.14)

In issue 29355 Ma Lin says that RHEL6 comes with SQLite 3.6.x. I think that removing that removing the check for sqlite3_prepare_v2 and sqlite3_open_v2 is fine but sqlite3_close_v2 need to stay.

> Don't forget about Windows where autotools are not used.

What should I do for this to work on windows?
msg288772 - (view) Author: Serhiy Storchaka (serhiy.storchaka) * (Python committer) Date: 2017-03-01 18:33
> sqlite3_open_v2 - 2007-09-04 (3.5.0) alpha
> sqlite3_prepare_v2 - 2007-01-04 (3.3.9)
> sqlite3_close_v2 - 2012-09-03 (3.7.14)

There are compile-time checks for supporting SQLite older than 3.2.2.

I think it is better to use the preprocessor rather of autotools for 
conformance with the rest of code.

> What should I do for this to work on windows?

On Windows manually written PC/pyconfig.h is used rather of generated by 
configure pyconfig.h. If you change pyconfig.h, you should update PC/pyconfig.h 
too. But I think this is not needed in this case. Just use 
SQLITE_VERSION_NUMBER.
msg288776 - (view) Author: Aviv Palivoda (palaviv) * Date: 2017-03-01 19:44
I changed the patch to use SQLITE_VERSION_NUMBER and it looks way better. Thanks Serhiy
msg288877 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2017-03-03 11:11
I will add commit information manually: https://github.com/python/cpython/commit/86a670543ff97d52fd9b8ca0477f8b6d27ee946d

Thanks!
msg290329 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2017-03-24 23:05
New changeset 86a670543ff97d52fd9b8ca0477f8b6d27ee946d by Berker Peksag (Aviv Palivoda) in branch 'master':
bpo-9303: Migrate sqlite3 module to _v2 API to enhance performance (#359)
https://github.com/python/cpython/commit/86a670543ff97d52fd9b8ca0477f8b6d27ee946d
msg291360 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2017-04-09 09:12
New changeset 0e6cb2ea624570ed08c354f1ed1f595dab4192d6 by Berker Peksag (Aviv Palivoda) in branch 'master':
bpo-26187: Test that set_trace_callback() is not called multiple times (GH-461)
https://github.com/python/cpython/commit/0e6cb2ea624570ed08c354f1ed1f595dab4192d6
History
Date User Action Args
2017-04-09 09:12:02berker.peksagsetmessages: + msg291360
2017-03-31 16:36:18dstufftsetpull_requests: + pull_request920
2017-03-24 23:05:40berker.peksagsetmessages: + msg290329
2017-03-17 21:05:43larrysetpull_requests: - pull_request583
2017-03-17 21:00:32larrysetpull_requests: + pull_request583
2017-03-03 11:11:58berker.peksagsetmessages: + msg288877
2017-03-03 10:59:13berker.peksagsetstatus: open -> closed
resolution: postponed -> fixed
stage: patch review -> resolved
2017-03-01 19:44:01palavivsetmessages: + msg288776
2017-03-01 18:33:55serhiy.storchakasetmessages: + msg288772
2017-03-01 17:44:57palavivsetmessages: + msg288769
2017-03-01 11:44:24serhiy.storchakasetnosy: + serhiy.storchaka

messages: + msg288754
versions: + Python 3.7, - Python 3.6
2017-02-28 20:51:17palavivsetmessages: + msg288726
2017-02-28 20:39:43palavivsetpull_requests: + pull_request306
2016-08-02 15:28:11Dima.Tisneksetmessages: + msg271826
2016-04-30 10:35:10palavivsetfiles: + 9303.patch
nosy: + palaviv
messages: + msg264548

2016-03-27 18:57:28BreamoreBoysetnosy: - BreamoreBoy
2016-03-27 14:13:53berker.peksagsetnosy: + berker.peksag
type: performance -> enhancement
2016-03-27 14:11:36berker.peksagunlinkissue24887 dependencies
2016-03-23 10:28:16berker.peksaglinkissue24887 dependencies
2015-08-19 09:26:18ghaeringsetresolution: postponed
messages: + msg248821
versions: + Python 3.6, - Python 3.5
2015-01-05 17:18:17ghaeringsetmessages: + msg233468
2015-01-05 16:45:07BreamoreBoysetnosy: + BreamoreBoy
2015-01-05 14:21:02hayposetnosy: + haypo
2015-01-05 13:51:19Dima.Tisneksetnosy: + Dima.Tisnek
messages: + msg233463
2013-12-22 17:45:33pitrousetversions: + Python 3.5, - Python 3.4
2013-01-15 16:01:52pitrousetnosy: + larry, pitrou

messages: + msg180026
versions: + Python 3.4, - Python 3.3
2012-08-22 14:57:49loewislinkissue15764 superseder
2012-05-20 04:25:28petri.lehtinensetnosy: + petri.lehtinen
messages: + msg161183
2012-04-15 18:56:51Arfreversetnosy: + Arfrever
2012-04-15 12:33:08pitrousetstage: needs patch -> patch review
versions: + Python 3.3, - Python 3.2
2012-04-14 15:40:02Robin.Schreibersetfiles: + sqlite.patch
2012-04-14 15:39:38Robin.Schreibersetfiles: - sqlite.patch
2012-04-14 12:14:37Robin.Schreibersetfiles: + sqlite.patch
keywords: + patch
messages: + msg158261
2012-04-13 19:20:39Jim.Jewettsetnosy: + Jim.Jewett
messages: + msg158228
2012-04-12 21:04:12Robin.Schreibersetnosy: + Robin.Schreiber
messages: + msg158169
2010-07-19 12:24:14ghaeringsetmessages: + msg110741
2010-07-19 12:13:58pitrousetassignee: ghaering
stage: needs patch

nosy: + ghaering
versions: + Python 3.2, - Python 3.1
2010-07-19 12:08:04feuermurmelcreate