Prepare will send the SQL query to the database server, which will compile it, to check if the SQL query syntax is right or not. If it is not right, then the reason can be get from the $dbh->errstr.

It depends

For example, I am having a function which will select a row from a table, depending upon the argument passed to that function.

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.

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

To your questions there is only one answer. "It depends".

1. The database driver (DBD) is free to contact the server for compilation. For example if the client library is a very thin network layer, it doesn't know about the tables on the server or the query plan. It must then contact the DB server to compile the query and check it for correctness.
2. If you define the measure by which you want to measure "best", we can possibly help you better.
3. You will have to make that comparison with your database, your queries and your data. Most likely, prepare_cached will be faster or just as fast as prepare, as the overhead of looking in the cache will be negligible. But if you have lots of queries and don't use placeholders, the situation might be different. Again, with such a general question, it's hard to give specific answers.

Here's a simple rule: if you're using a persistent environment, like mod_perl or FastCGI, use prepare_cached(). The reason is that when you hit that page for the second time, it will pull the statement handle objects from cache, and that will be faster than making new ones.

Note that it doesn't matter if your database supports server-side statement handles -- prepare_cached() can save time just on the perl side.