Performance impact of UPDATEing multiple columns vs few columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance impact of UPDATEing multiple columns vs few columns

ghalwasi
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Performance impact of UPDATEing multiple columns vs few columns

Clemens Ladisch
ghalwasi wrote:
> what will be the difference (in context of CPU cycles & performance)
> if i UPDATE multiple columns or only few columns.

SQLite always rewrites the entire row, so there is no practical
difference.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

Hick Gunter
In reply to this post by ghalwasi
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:[hidden email]] Im Auftrag von ghalwasi
Gesendet: Mittwoch, 06. September 2017 17:57
An: [hidden email]
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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

ghalwasi
>>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

Darko Volaric
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

ghalwasi
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

Hick Gunter
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:[hidden email]] Im Auftrag von ghalwasi
Gesendet: Montag, 11. September 2017 15:02
An: [hidden email]
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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users