Re: [firebird-support] Execution speed with update query
@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/
[firebird-support] Execution speed with update query
Dear all, 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? 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 ) The result of Flamerobin is as follow: Executing... Done. 78935476 fetches, 15862 marks, 411 reads, 1 writes. 0 inserts, 6677 updates, 0 deletes, 0 index, 14549183 seq. Delta memory: 19956 bytes. ELEMENTS: 6677 updates. 6677 rows affected directly. Total execution time: 11.848s Script execution finished. ++ 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/
[firebird-support] Update table column from another table
Dear All, I have difficulty with updating column records of one table from another table columns, which have common columns for relation. Executed query is as follow : update elements E set E.END_I = (select n.node_num from nodes N where (E.X_I =N.XI and E.Y_I = N.YI and E.Z_I=N.ZI) ) where exists (select 1 from nodes N where (E.X_I =N.XI and E.Y_I = N.YI and E.Z_I=N.ZI)) The problem with that is , not all the columns of Elements are updated, only one portion is correctly updated and rest remains intact. I tried to follow instructions here http://www.firebirdfaq.org/faq323/ but without success. I use old version of Firebird which is ver 2.1. What is rong with the code above, Regards,
Ynt: [firebird-support] Update table column from another table
Yes I tried that it wasn't selecting at all, it was due to the floating point comparison. It seems that hit the wall about that floating point precision I eliminate that with subtracting the coumns and taking the absolute value if it is less than 0.01 epsilon reference value. It worked for now, but I'd like to know what is the decent way of solving such issues. Regards, hi, did you try select instead of update to see if select return all records which you try to update? regards, Karol Bieniaszewski Oryginalna wiadomosc Od: "Sonya Blade sonyablade2...@hotmail.com [firebird-support]" <firebird-support@yahoogroups.com> Data: 02.01.2016 08:24 (GMT+01:00) Do: firebird-support@yahoogroups.com Temat: [firebird-support] Update table column from another table Dear All, I have difficulty with updating column records of one table from another table columns, which have common columns for relation. Executed query is as follow : update elements E set E.END_I = (select n.node_num from nodes N where (E.X_I =N.XI and E.Y_I = N.YI and E.Z_I=N.ZI) ) where exists (select 1 from nodes N where (E.X_I =N.XI and E.Y_I = N.YI and E.Z_I=N.ZI)) The problem with that is , not all the columns of Elements are updated, only one portion is correctly updated and rest remains intact. I tried to follow instructions here http://www.firebirdfaq.org/faq323/ but without success. I use old version of Firebird which is ver 2.1. What is rong with the code above, Regards,
