Subject Re: [firebird-support] Execution speed with update query
Author Sonya Blade
>The above query cannot possibly use any index for the nodes table (well,
>I'm a bit uncertain about indexes defined with COMPUTED BY, but I doubt

Yes the query was not using the indices even if they are defined,
lately I learned from SO that indices are not used with expressions. An the malfunctioning
function was abs, It directly affects the execution speed, it was never been supossed to be
used, another workaround works much better

>How many rows are in table "nodes"?

>What is the relationship between elements and nodes?

Both tables have only XI, YI, ZI fields in common.

>What is the PLAN for the statement?

I didn't setup any plan for that, literally I really don't know how to.

> Exeucuted Query:
> update elements E set E.END_I = (select n.node_num from nodes N '+
> 'where abs(E.X_I -N.XI)<0.001 and abs(E.Y_I - N.YI)<0.001 and abs(E.Z_I-
> N.ZI)<0.001 )

>I don't see any FK in use between elements and nodes, so a NATURAL scan of nodes is required for each element row. This naturally will be slow.

Do I need to provide FK?

> The result of Flamerobin is as follow:
> Executing...
> Done.
> 78935476 fetches, 15862 marks, 411 reads, 1 writes.
>The fact that the ratio of reads vs. fetches is so low is a very good thing, it shows that cache is being very actively used. Otherwise, your >query performance would be much worse.

I really don't know how to interpret those values but at the first glance, fetch values are intimidating.