Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns
If you have 4 set clauses, the those 4 fields will be updated with the same values for all the rows matching the where clause with just a single call to sqlite3_step(). If you actually want to update only 1 of the fields in 1 record, then you must bind the current values (which are unknown and need to be determined first) of the other fields and make sure that your where clause matches only the 1 intended record. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von ghalwasi Gesendet: Montag, 11. September 2017 15:02 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns hi Darko, >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. Thanks for the explanation. It makes sense. Thanks Gaurav -- 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns
hi Darko, >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. Thanks for the explanation. It makes sense. Thanks Gaurav -- 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
Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns
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, ghalwasiwrote: > 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
Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns
>>> 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
Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns
SQLite currently implements UPDATE by pretending it is SELECTing all the fields, except a SET clause causes the expression(s) to be evaluated instead of the current field value(s). 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? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von ghalwasi Gesendet: Mittwoch, 06. September 2017 17:57 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Performance impact of UPDATEing multiple columns vs few columns I am using SQLite C library in my application and I have a question regarding updating "mutiple" columns using UPDATE statement. Lets suppose, my Database table has 10 columns (c1, c2 ... c10). My question is that what will be the difference (in context of CPU cycles & performance) if i UPDATE multiple columns or only few columns. Lets say if the requirement is to just update c2 & c3 but if we are updating c2, c3,c4,c5,c6,c7 UPDATE db SET c2,c3,c4,c5,c6,c7 WHERE c1=x (c1 is a primary key) or UPDATE db SET c2,c3 WHERE c1=x (c1 is a primary key) -- 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 ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users