Preparing Cached Statements
Cached statements can improve performance for queries that are likely to be used frequently across multiple instances of the same program. Like statements with placeholders, cached statements relieve the database from having to generate execution plans for similar queries.
The example in Listing 4 uses prepare_cached to indicate that the execution plan generated for the statement being prepared is applicable to all instances of this statement. This is only true if the statement as prepared is identical each time; if any aspect of the statement changes from one instance of the program to the next, the statement is not reused after it is cached. For example, the statement in Listing 2 should not be cached because the value of $msgid changes from one instance of the application to the next as different messages are requested. The statement in Listing 4, although it returns the same result, can be cached because it uses a placeholder to represent the value that changes. Because the statement as prepared does not change, the plan created for that statement can be cached and reused.
Thought should be given to instances in which cached statements would be useful. In most cases, a statement should be prepared using prepare_cached only if it can be represented as a single-quoted string, that is, only if no values are interpolated by Perl at runtime. However, some interpolated values can be cached if the number of possibilities is severely limited; for example, a query that is chosen from two or three possibilities through an if statement also could be cached because the total number of statements cached overall is small and fixed.