Avoid Unnecessary Prepare Operations

Performance impact: Large

Because preparing SQL statements is an expensive operation, your application should minimize the number of calls to the prepare API. Most applications prepare a set of statements at the beginning of a connection and use that set for the duration of the connection. This is a good strategy when connections are long, consisting of hundreds or thousands of transactions.

But if connections are relatively short, a better strategy is to establish a long-duration connection that prepares the statements and runs them on behalf of all threads or processes. The trade-off here is between communication overhead and prepare overhead, and can be examined for each application. Prepared statements are invalidated when a connection is closed.

See ttSQLCmdCacheInfoGet in the Oracle TimesTen In-Memory Database Reference.