1

I have been using c++ and work with sqlite. In python, I have an executemany operation in the library but the c++ library I am using does not have that operation.

I was wondering how the executemany operation optimizes queries to make them faster.

I was looking at the sqlite c/c++ api and saw that there were two commands, sqlite3_reset and sqlite3_clear_bindings, that can be used to clear and reuse prepared statements.

Is this what python does to batch and speedup executemany queries (at least for inserts)? Thanks for your time.

ThinkBonobo
  • 15,487
  • 9
  • 65
  • 80
  • Wrapping a series of insert statements in a transaction will speed up performance more than clearing bindings. If you want to know how `executemany` works, look at its source code. – Colonel Thirty Two Aug 21 '14 at 13:38

2 Answers2

2

executemany just binds the parameters, executes the statements, and calls sqlite3_reset, in a loop.

Python does not give you direct access to the statement after it has been prepared, so this is the only way to reuse it. However, SQLite does not take much time for preparing statements, so this is unlikely to have much of an effect on performance.

The most important thing for performance is to batch statements in a transaction; Python tries to be clever and to do this automatically (independently from executemany).

CL.
  • 173,858
  • 17
  • 217
  • 259
1

I looked into some of the related posts and found the folowing which was very detailed on ways to improve sqlite batch insert performace. These principles could effectively be used to create an executemany function.

Improve INSERT-per-second performance of SQLite?

The biggest improvement changes were indeed as @CL. said, turning it all into one transaction. The author of the other post also found significant improvement by using and reusing prepared statements and playing with some pragma settings.

Community
  • 1
  • 1
ThinkBonobo
  • 15,487
  • 9
  • 65
  • 80