Porsche Posted September 27, 2007 Share Posted September 27, 2007 This is my query: select my_fieldfrom my_table wwhere substr(unique_key,5,16) = 1234567891011121and w.kod_row = to_number(kod_row)and w.kod_col = to_number(kod_col) it takes 2,5 seconds to exetute query, when without "substr" it takes 0,016-0,032 of second."lpad" does not work.please, why it takes so much time and what is the solution? Link to comment Share on other sites More sharing options...
justsomeguy Posted September 27, 2007 Share Posted September 27, 2007 Which database system are you using? How many rows are in the table? Use the AS keyword so that you don't clone the table:from my_table AS wAlso move the substr after the other two conditions. Link to comment Share on other sites More sharing options...
Porsche Posted September 28, 2007 Author Share Posted September 28, 2007 I`m using Oracle DB and there are nearly 4 millions rows in the table.Thanks, but it was no effect from your advice. May be others?p.s. neiser no effect from "to_number(to_char(my_field),'999999999999999999')", mod(my_field, power(10,15)) .. Link to comment Share on other sites More sharing options...
Synook Posted September 29, 2007 Share Posted September 29, 2007 Well with 4 million records in the table i'm not too suprised, because the WHERE clause makes it search through every record, and even if each iteration of the substr() function only takes 0.000000005 seconds then the entire query will still take 2 seconds. Simple equality tests are much faster. Have you tried benchmarking a single substr() call? Link to comment Share on other sites More sharing options...
justsomeguy Posted October 1, 2007 Share Posted October 1, 2007 OK, that's easy then. please, why it takes so much time and what is the solution?It takes so much time because you are executing a function 4 million times. It takes a little time to run it each time. The solution is to either not call the function or reduce the number of rows, but if you're going to execute a function 4 million times you have to expect it to take some time to run. The only thing you can do to reduce the time without deleting rows or changing the SQL statement is to upgrade the hardware on the server. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.