Jump to content

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


Porsche
 Share

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...