search

crawshaw / sqlite

Go SQLite3 driver
ISC License
561 stars 67 forks source link

Performance improvement: only reset and clear bindings when necessary #138

Open israel-lugo opened 1 year ago

israel-lugo commented 1 year ago

Prepare shouldn't call Reset and ClearBindings unconditionally. The underlying C functions sqlite3_reset and sqlite3_clear_bindings functions are non-trivial and will cause some slow down.

Neither of them is necessary if the user already called them manually (e.g. to avoid holding a transaction open, or to allow large parameters to be freed early).

Also, ClearBindings is not necessary for statements that never had parameters bound to them.

I've implemented some benchmarks and measured before/after:

name                                                                old time/op  new time/op  delta
PrepareTransientAndFinalize/Select_constant-4                       4.94µs ± 1%  4.91µs ± 1%   -0.67%  (p=0.014 n=10+10)
PrepareTransientAndFinalize/Insert_constants-4                      4.91µs ± 1%  4.81µs ± 0%   -2.03%  (p=0.000 n=9+10)
PrepareTransientAndFinalize/Insert_with_params-4                    5.86µs ± 0%  5.64µs ± 1%   -3.77%  (p=0.000 n=10+10)
PreparePrimed/Select_constant-4                                      373ns ± 0%    13ns ± 0%  -96.63%  (p=0.000 n=10+10)
PreparePrimed/Insert_constants-4                                     376ns ± 0%    13ns ± 0%  -96.58%  (p=0.000 n=10+10)
PreparePrimed/Insert_with_params-4                                   380ns ± 0%    13ns ± 0%  -96.62%  (p=0.000 n=9+8)
PrepareBindAndSelect/Select_constant-4                               775ns ± 0%   589ns ± 1%  -23.93%  (p=0.000 n=9+10)
PrepareBindAndSelect/Select_constant,_explicit_reset-4               953ns ± 0%   584ns ± 1%  -38.71%  (p=0.000 n=10+9)
PrepareBindAndSelect/Select_int_param_directly-4                    1.10µs ± 0%  1.11µs ± 0%   +1.16%  (p=0.000 n=10+8)
PrepareBindAndSelect/Select_int_param_directly,_explicit_reset-4    1.24µs ± 0%  1.10µs ± 0%  -11.04%  (p=0.000 n=10+10)
PrepareBindAndSelect/Select_from_table-4                            2.24µs ± 0%  2.29µs ± 0%   +1.97%  (p=0.000 n=10+10)
PrepareBindAndSelect/Select_from_table,_explicit_reset_and_clear-4  2.65µs ± 0%  2.44µs ± 0%   -8.08%  (p=0.000 n=10+10)

Note the massive improvement for Prepare when already primed. Also a significant improvement for Prepare + Bind + Select when the user is explicitly doing Reset and/or Reset+ClearBindings. These really add up after a few hundred thousand operations.

The +1% worsening on SELECT $f1 is likely just background variation.

israel-lugo commented 1 year ago

@AdamSLevy could you please take a look, at your convenience?