Jump to content

need to cut a string in query, but it takes much time


Porsche

Recommended Posts

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

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...