>Hi.
>in firebird 2.5
>
>Simple descrip tables
>
>table_ud
>ud_id integer not null primary key
>field_ud varchar(6)
>
>row count: 383322
>
>table_tmp
>ud_id  integer 
>cnd    integer 
>
>index (cnd)
>
>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)
>
>stadistic 
>table_ud
>read no index: 383322
>
>Why?
>
>This table can grow to millions of rows
>
>How do I optimize it ?

Hi Germán!

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;
BEGIN
FOR SELECT UD_ID FROM TMP
WHERE CND = 2
INTO :UDID DO
UPDATE TABLE_UD
SET UD.FIELD_UD = '201401'
WHERE UD_ID = :UDID;
END

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.

HTH,
Set