Why not simply

update VIRTUAL_FILES
set VIRTUAL_PATH_FILE_ID = ?
where PHYSICAL_FILE_ID = (
select FILES.FILE_ID
from FILES
where FILES.PATH_FILE_ID = ?);

or (for your "new" query)

update VIRTUAL_FILES
set VIRTUAL_PATH_FILE_ID = ?
where PHYSICAL_FILE_ID IN (
select FILES.FILE_ID
from FILES
where FILES.PATH_FILE_ID = ?);

or

update VIRTUAL_FILES
set VIRTUAL_PATH_FILE_ID = ?
where exists(select * from FILES
where FILES.PATH_FILE_ID = ?
and VIRTUAL_FILES.PHYSICAL_FILE_ID = FILES.FILE_ID);

I'm actually impressed that Fb 2 managed to use the PK_VIRTUAL_FILES
index (unless it is an error), I doubt Fb 1.5 (that I use) would do the
same (though I don't normally write UPDATE statements with '=
<subselect>', so I may be wrong). Actually, unless Hence, I have no
explanation as to why your old query is faster than your new.

Another option (since you use a stored procedure) would be something like:

for select FILE_ID from FILES
where PATH_FILE_ID = ?
into :Temp_File_ID
do begin
update VIRTUAL_FILES
set VIRTUAL_PATH_FILE_ID = ?
where PHYSICAL_FILE_ID = :TEMP_FILE_ID
end;

Doing things this way has always been quick (though watch out for
spelling mistakes on my part).

HTH,
Set

Fulvio Senore wrote:

> I have just changed a query that is part of a stored procedure. The
> query used an "=" predicate and I had to change it to the "in" predicate.
>
> This is the "old" query:
>
> update VIRTUAL_FILES set VIRTUAL_PATH_FILE_ID = ?
> where FILE_ID = (select VIRTUAL_FILES.FILE_ID
> from VIRTUAL_FILES inner join FILES on
> VIRTUAL_FILES.PHYSICAL_FILE_ID = FILES.FILE_ID
> where FILES.PATH_FILE_ID = ?);
>
>
> and this is the "new" query:
>
> update VIRTUAL_FILES set VIRTUAL_PATH_FILE_ID = ?
> where FILE_ID IN (select VIRTUAL_FILES.FILE_ID
> from VIRTUAL_FILES inner join FILES on
> VIRTUAL_FILES.PHYSICAL_FILE_ID = FILES.FILE_ID
> where FILES.PATH_FILE_ID = ?);
>
> I had to change this because I thought that the query would need to
> update only a single row, but now I discovered that the rows might be a
> few (but really few).
> The problem is that the old query was fast, while the new one is deadly
> slow.
> The query references two tables, but the database contains more of them.
> Here is the DDL for the two tables:
>
> CREATE TABLE FILES(
> FILE_ID Numeric(18,0) NOT NULL,
> FILE_NAME Varchar(500) CHARACTER SET ISO8859_1,
> FILE_SIZE Numeric(18,0),
> FILE_EXT Varchar(50) CHARACTER SET ISO8859_1,
> FILE_DATETIME Timestamp,
> PATH_ID Numeric(18,0),
> PATH_FILE_ID Numeric(18,0),
> CONSTRAINT PK_FILES PRIMARY KEY (FILE_ID)
> );
> ALTER TABLE FILES ADD CONSTRAINT FK_FILES_PATHS
> FOREIGN KEY (PATH_ID) REFERENCES PATHS (PATH_ID);
> ALTER TABLE FILES ADD CONSTRAINT FK_FILES_PATHS2
> FOREIGN KEY (PATH_FILE_ID) REFERENCES PATHS (PATH_ID);
>
>
> CREATE TABLE VIRTUAL_FILES(
> FILE_ID Numeric(18,0) NOT NULL,
> VIRTUAL_PATH_ID Numeric(18,0) NOT NULL,
> PHYSICAL_FILE_ID Numeric(18,0) NOT NULL,
> VIRTUAL_PATH_FILE_ID Numeric(18,0),
> CONSTRAINT PK_VIRTUAL_FILES PRIMARY KEY (FILE_ID)
> );
> ALTER TABLE VIRTUAL_FILES ADD CONSTRAINT FK_VIRTUAL_FILES_FILES
> FOREIGN KEY (PHYSICAL_FILE_ID) REFERENCES FILES (FILE_ID);
> ALTER TABLE VIRTUAL_FILES ADD CONSTRAINT FK_VIRTUAL_FILES_VIRTUAL_PATHS
> FOREIGN KEY (VIRTUAL_PATH_ID) REFERENCES VIRTUAL_PATHS (PATH_ID);
> ALTER TABLE VIRTUAL_FILES ADD CONSTRAINT FK_VIRTUAL_FILES_VIRTUAL_PATHS2
> FOREIGN KEY (VIRTUAL_PATH_FILE_ID) REFERENCES VIRTUAL_PATHS (PATH_ID);
>
>
> The plan for the old query is the following:
>
> PLAN JOIN (FILES INDEX (FK_FILES_PATHS2), VIRTUAL_FILES INDEX
> (FK_VIRTUAL_FILES_FILES))
> PLAN (VIRTUAL_FILES INDEX (PK_VIRTUAL_FILES))
>
> and the plan for the new query is the following:
>
> PLAN JOIN (VIRTUAL_FILES INDEX (PK_VIRTUAL_FILES), FILES INDEX (PK_FILES))
> PLAN (VIRTUAL_FILES NATURAL)
>
>
> I suppose that the problem is the "natural" word that I see in the plan:
> the optimizer chooses not to use any index.
>
> I am using Firebird 2.01 SS on Windows XP.
>
> Does anybody know how to make fast again the query with the "in"
> predicate? I know that I can rewrite the query in a different way, but I
> am also curious about the reason of this huge difference.
>
> Thanks in advance.
>
> Fulvio Senore