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.