-
-
Notifications
You must be signed in to change notification settings - Fork 31.8k
sqlite3 400x-600x slower depending on formatting of an UPDATE statement in a string #76396
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Comments
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. """ """ 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. |
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 rdmurray@pydev:~/python/p37[master]>./python ../p27/sqlite3_27_36_performance_bug.py (I changed time.clock() to time.time()). |
...using the *same* sqlite version... |
Brian, does the speed difference disappear when you add a space character just after "UPDATE"? |
It disappears for me running it on linux with the blank added. |
Hi Antoine, yup, adding a space after the UPDATE makes the speed difference disappear on macOS Sierra and Windows 7. |
ab994ed wasn't merged in the 2.7 branch, so this should only be reproduced in Python 3.6+. |
This seems complete, can it be closed? |
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:
bugs.python.org fields:
The text was updated successfully, but these errors were encountered: