|Subject||Re: [firebird-support] update optimization problem|
Yes. in fb 1.5 is very disastrous
El Miércoles, 29 de octubre, 2014 10:39:34, "Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]" <firstname.lastname@example.org> escribió:
>in firebird 2.5
>Simple descrip tables
>ud_id integer not null primary key
>row count: 383322
>row count: 617
>UPDATE table_ud ud
>SET ud.field_ud = '201401'
>WHERE ud.ud_id in (SELECT t.ud_id FROM table_tmp t where t.cnd = 2)
>read no index: 383322
>This table can grow to millions of rows
>How do I optimize it ?
Normally, I would use
UPDATE table_ud ud
SET ud.field_ud = '201401'
WHERE EXISTS(SELECT * FROM table_tmp t where ud.ud_id = t.ud_id AND t.cnd = 2)
But I think that Firebird 2.5 have fixed the general problem with IN(<subselect>) already (a common problem with Fb 1.5 was that the subselect would be executed for each potential row, but I think that would mean that if there were 25 rows with cnd 2, then the count would be 9583050, and not merely 383322). It could well be that Firebird is still fairly slow on UPDATE queries where the only delimiting factor is an EXISTS.
If you want less rows read, I would suggest using
EXECUTE BLOCK AS
DECLARE VARIABLE UDID INTEGER;
FOR SELECT UD_ID FROM TMP
WHERE CND = 2
INTO :UDID DO
SET UD.FIELD_UD = '201401'
WHERE UD_ID = :UDID;
This ought to reduce your number of reads, but it is of course more tedious to write and probably less similar to how it would be done in other databases.