Limit Prepares, Re-prepares, and Connects

Performance impact: Variable

Prepares are resource-intensive operations that do not scale well compared to SQL execution operations. Ensure that you use parameterized SQL statements wherever possible and always pre-prepare statements that run more than once.

The stmt.prepares.count column of the SYS.SYSTEMSTATS table indicates how often statements were prepared directly by an application. If the stmt.prepares.count column has a high value, ensure the following:

• Your application uses parametrized statements.

• Your application only prepares any given SQL statement once and not for every single execution.

• Your application does not connect and disconnect frequently from the database. If that is the case, consider using long active connections.

Connect and disconnect operations are resource-intensive operations that do not scale well. Ensure that your application minimizes the number of connect and disconnect operations. Use long active connections with parameterized and re-prepared SQL statements to improve performance.

If your application cannot avoid frequent connect and disconnect operations or you are unable to modify the application to use long-lived connections, consider using connection pooling. Connection pooling can reduce the frequency of connect and disconnect operations and improve the utilization of prepared statements compared to the case where an application frequently connects and disconnects from the database.