Hello Svein,

thank you for your answer.

Svein Erling Tysvaer ha scritto:

> 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);
>

Because I am not very expert at writing complex queries. Thank you for
the suggestions.

> 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;
>

That is what I have done, and it works well. I wanted to try a subquery
for the first time, but probably it was too complex for the optimizer.

Fulvio Senore

> 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
>>
>
>
>