Firebird , SQL 'update'
lazarus / 16-Databases

----------------------------------------------------------------------
asdf · 2010-11-16 05:22

SQLQuery1.sql.text:=update table1 set fld2 ='ln1' where fld1 = '001'
// Just look like this.
SQLQuery1.execSQL
SQLQuery1.sql.text:=update table1 set fld3 ='ln2' where fld1 = '001'
// Just look like this.
SQLQuery1.execSQL
SQLQuery1.sql.text:=update table1 set fld4 ='ln3' where fld1 = '001'
// Just look like this.
SQLQuery1.execSQL

I have tested. If 3,000 times, it took only 5 seconds to finish.
But actually, I have to do it 900,000 times.
It was not (5x900,000/3,000=25 minutes), but many many hours.
I thought 'where fld1= '001' ' would make this clumsy.

What technique should be needed?
Or how can I lock the record first and then 'update' to waive 'where
fld1 = '001'.

----------------------------------------------------------------------
JohnvdWaeter · 2010-11-16 07:44

You may want to set the values all at once:

update table1 set fld2 ='ln1', fld2='ln2', fld3='ln3' where fld1 =
'001'

if fld1 is rather unique, consider putting an index on it.

consider using prepared updates with parameters.

If you use Firebird, consider testing and experimenting with a tool
like FlameRobin or IBExpert or alike. And studying Firebird, as
mentioned before.

John

----------------------------------------------------------------------
José Mejuto · 2010-11-16 11:20

Quote from: asdf on November 16, 2010, 06:22:47 am[...]
I have tested. If 3,000 times, it took only 5 seconds to finish.
But actually, I have to do it 900,000 times.
It was not (5x900,000/3,000=25 minutes), but many many hours.
I thought 'where fld1= '001' ' would make this clumsy.
[...]

Hello,

In transactional databases each change to any data before a commit is
temporally stored in an area like a "limbo", checking this area for
integrity is much slower than regular checks so as much registers you
add/change without a commit more slower the database is becoming.

If you can consider each update as an atomic operation, this means
that after it has finished it can be considered definitive, commit it,
but keep in mind that you will not be able to rollback those changes
if something fails later.

There is no exactly rule on how many registers must be added/changed
before a commit to get better results in speed, but usually a number
betweem 1000 and 3000 is quite good.

If your code updates many registers and there is no need to rollback
them, commit after each execsql.

Other databases have modes for massive data import which disconnect
reference checking and only allow one user in the database, but I'm
quite sure firebird does not have such kind of mode.