If you're preparing that statement more than once then you are wasting time, 
there's no reason whatsoever to do it. You're also wasting time if you make a 
bind call to set any column that hasn't changed since the last time you 
executed the statement.

The entire row is rewritten when updating so the most efficient way to do it is 
to call sqlite3_prepare_v2 once, then bind any columns that have changed, call 
sqlite3_step to execute the statement, then call sqlite3_reset to reuse the 
statement, then go back to binding any columns that have changed and repeat the 
other steps. sqlite3_reset does not clear any column bindings.



> On Sep 10, 2017, at 7:08 AM, ghalwasi <ghalw...@cisco.com> wrote:
> 
>>>> Are you using a single prepared statement and binding values (in which
> case, how do you know what values to bind for the "non-updated" columns?) or
> are you creating query strings? 
> 
> I am not too sure, if i get it completely. My current code has a lot of
> update statements like.
> 
> "update records set name=:name, type=:type, class=:class, ttl=:ttl where
> rr_id=:rr_id;"
> every time we do prepare the statetment again (sqlite3_prepare_v2) and call
> sqlite_bind_* for each of these columns (name, type, class, ttl) and then
> execute.
> 
> Now here intent was/is to just update "ttl" column but i see that we are
> unnecessarily updating 4 fields. And my original question was in this
> context where i want to figure out whether it could make some performance
> improvement if we change the above statement to 
> "update records set ttl=:ttl where rr_id=:rr_id;"
> 
> 
> 
> 
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to