Subject »Ø¸´£º [firebird-support] Re: About update clause's time.
Author Svein Erling Tysvær
Hmm, using 'where Bfield > Bvalue' means that the primary key cannot
be used in the plan. Possibly a descending index could be used (I
don't know how much Arno has improved this bit since 1.0), but the
primary key creates an ascending index, so without the descending
index the query has to go natural...

I guess that if you turned everything around, things could be a bit
First, do

SET Bfield = -Bfield

After that, I would expect

update Table1
set Afield = Avalue where Bfield < Bvalue'

to execute quicker.

Still, your table is small (300000 is peanuts) and using two minutes
on a query updating an integer field for 27000 records based on the
primary key sounds excessively long. Is there something you haven't
told us or is it just me not being used to updates (more often I use


--- In, Yang Jin wrote:
> Firstly,Thank you reply.
> The way is the simplest way.I wrote'update Table1
> set Afield = Avalue where Bfield > Bvalue' in IBExpert's 'SQL
> Editor'. The Afield is a integer type,Avalue is a const integer;
> Bfield is the PrimaryKey, Bvalue is a const integer.
> Svein Erling Tysvær wrote:
> Hello!
> --- In, Yang Jin wrote:
> > hello,
> > I update 27,000 records in a table with 300,000 records.I use
> > primary key as only where condition, the whole execute time almost
> > 2mins.If I update all records, the time is almost 6m.
> > The database is dialect 3,the page size is 16K,ODS is 10.1,
> >Execute update clause in IBExpert, Firebird1.52 as service.
> >I want to know if the time can short.I hoep result is less 30 sec.
> How and how much do you update? If you prepare the update query
> once, then in a loop set the parameters and execute, 27000 records
> in 2 minutes sounds slow - that is only 225 records per second. I
> would expect it to be possible to improve this considerably.
> Set