So If I use the prepare, it will give the query to the database server to compile it and then it will return the handle. If the function is called in a loop, then it will be in-efficient. In those case we can use prepare_cached, because we are using the same query and no need to compile it again.

I think this is a poor example since if you were executing the same SQL repeatedly in a loop you would prepare it once and execute it many times. The point about prepare_cached is that DBI holds on to the statement handle prepare_cached returns in the connection so even once you've finished your loop (and perhaps even the sub it is in) the statement handle is still available to use for the exact same SQL somewhere else in your program.

I think this is a poor example since if you were executing the same SQL repeatedly in a loop you would prepare it once and execute it many times.

I am not executing the same SQL. I said "For example, I am having a function which will select a row from a table, depending upon the argument passed to that function."

It depends upon the arguments to the fucntion

If you are not executing the same SQL with the same attributes passed to prepare each time then each call to prepare_cached will give you a new statement handle and your SQL will be parsed each time. You only get an advantage from prepare_cached when the SQL and attributes to prepare are the same as a previous call to prepare_cached.

You said If the function is called in a loop, then it will be in-efficient and all I was attempting to say was if you are working with SQL in a loop you can prepare the SQL once and execute it multiple times in the loop but that does not necessarily suggest using prepare_cached instead of prepare. Where prepare_cached might speed things up (although with risks) would be if you called the sub containing the loop repeatedly. The second and subsequent times the sub was called the SQL would not be parsed.

I hope see the distinction I was trying to make.

prepare_cached is a useful tool but also dangerous unless used properly. I've see code where someone used prepare_cached everywhere just because they thought it was quicker but it a) created hundreds of statement handles and b) used loads of resource in the database and c) did not work reliably because the same statement was used in multiple places when one of the previous instances was still active.