Subject RE: Execution speed with update query
Author Leyne, Sean

> I experience slowdown in execution of following statement. It almost takes
> 11.5 sec to complete, total rows in table is 6677 is it expected execution
> speed or is there any workaround that I can increase the execution of
> speed?

How many rows are in table "nodes"?

What is the relationship between elements and nodes?

What is the PLAN for the statement?

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

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