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

2017-09-11 Thread 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: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

2017-09-11 Thread 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
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

2017-09-10 Thread 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  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


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

2017-09-10 Thread 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
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

2017-09-07 Thread Hick Gunter
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