@Set >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 @Sean >How many rows are in table "nodes"? 6677 >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. >Sean I really don't know how to interpret those values but at the first glance, fetch values are intimidating. Regards, ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
