Repository navigation
sqlite3 400x-600x slower depending on formatting of an UPDATE statement in a string #76396
Description
Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.
Show more details
GitHub fields:
assignee = None
closed_at = <Date 2021-06-25.22:41:09.025>
created_at = <Date 2017-12-05.00:40:52.911>
labels = ['3.8', '3.7', 'library', 'performance']
title = 'sqlite3 400x-600x slower depending on formatting of an UPDATE statement in a string'
updated_at = <Date 2021-06-25.22:41:09.025>
user = 'https://github.com/bforst'bugs.python.org fields:
activity = <Date 2021-06-25.22:41:09.025>
actor = 'iritkatriel'
assignee = 'none'
closed = True
closed_date = <Date 2021-06-25.22:41:09.025>
closer = 'iritkatriel'
components = ['Library (Lib)']
creation = <Date 2017-12-05.00:40:52.911>
creator = 'bforst'
dependencies = []
files = ['47315']
hgrepos = []
issue_num = 32215
keywords = ['patch']
message_count = 11.0
messages = ['307609', '307611', '307612', '307683', '307689', '307696', '322530', '325854', '325892', '325912', '378756']
nosy_count = 5.0
nosy_names = ['pitrou', 'r.david.murray', 'berker.peksag', 'bforst', 'iritkatriel']
pr_nums = ['8511', '9441', '9442', '9449', '9452']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'performance'
url = 'https://bugs.python.org/issue32215'
versions = ['Python 3.6', 'Python 3.7', 'Python 3.8']
Activity
bforst commentedon Dec 5, 2017
We're moving some code from Python 2.7 to 3.6 and found a weird performance issue using SQLite in-memory and on-disk DBs with the built-in sqlite3 library. In Python 2.7, the two update statements below (excerpted from the attached file) run in the same amount of time. In Python 3.6 the update statement with the table name on a separate line runs 400x-600x slower with the example data provided in the file.
"""
UPDATE tbl
SET col2 = NULL
WHERE col1 = ?
"""
"""
UPDATE
tbl
SET col2 = NULL
WHERE col1 = ?
"""
We have verified this using Python installs from python.org on macOS Sierra and Windows 7 for Python 2.7 and 3.6.
We have tried formatting the SQL strings in different ways and it appears that the speed change only occurs when the table name is on a different line than the "UPDATE".
This also appears to be hitting some type of quadratic behaviour as with 10x less records, it only takes 10-15x as long. With the demo in the file we are seeing it take 1.6s on the fast string and ~1000s on the slow string.
bitdancer commentedon Dec 5, 2017
I can confirm that there is a difference on linux as well, using the sqlite version for both 2.7 and 3.7:
rdmurray@pydev:~/python/p27[2.7]>./python sqlite3_27_36_performance_bug.py
First step: 3.22849011421
Second step: 3.2167429924
rdmurray@pydev:~/python/p37[master]>./python ../p27/sqlite3_27_36_performance_bug.py
First step: 3.2722721099853516
Second step: 4.094221353530884
(I changed time.clock() to time.time()).
bitdancer commentedon Dec 5, 2017
...using the *same* sqlite version...
pitrou commentedon Dec 5, 2017
Brian, does the speed difference disappear when you add a space character just after "UPDATE"?
We may be hitting this path: https://github.com/python/cpython/blob/master/Modules/_sqlite/statement.c#L76-L93
bitdancer commentedon Dec 5, 2017
It disappears for me running it on linux with the blank added.
bforst commentedon Dec 5, 2017
Hi Antoine, yup, adding a space after the UPDATE makes the speed difference disappear on macOS Sierra and Windows 7.
berkerpeksag commentedon Jul 28, 2018
ab994ed wasn't merged in the 2.7 branch, so this should only be reproduced in Python 3.6+.
berkerpeksag commentedon Sep 20, 2018
New changeset 8d1e190 by Berker Peksag in branch 'master':
bpo-32215: Fix performance regression in sqlite3 (GH-8511)
8d1e190
berkerpeksag commentedon Sep 20, 2018
New changeset 015cd0f by Berker Peksag (Miss Islington (bot)) in branch '3.7':
bpo-32215: Fix performance regression in sqlite3 (GH-8511)
015cd0f
berkerpeksag commentedon Sep 20, 2018
New changeset 4fb672f by Berker Peksag (Miss Islington (bot)) in branch '3.6':
bpo-32215: Fix performance regression in sqlite3 (GH-8511)
4fb672f
iritkatriel commentedon Oct 16, 2020
This seems complete, can it be closed?