You can improve performance by preparing statements that are expected to be executed multiple times.
About this task
In your application design, identify statements that are executed multiple times. Ensure that the application prepares each SQL statement only once and uses the same prepared SQL statement for executes. Additionally, use dynamic parameter binding to ensure that the same SQL statement can be executed with different parameter values.
Using prepared SQL statements improves performance for the following reasons:
▪An extra call to the server to prepare the SQL statement is omitted.
▪The following type of overhead associated with the SQL prepare operation can be avoided: allocating memory for the statement, checking access rights, and validating column and table names.
▪The SQL interpreter and optimizer can creates an optimized access plan for data retrieval and reuse it when the statement is executed again.
Procedure
To prepare and execute an SQL statement in separate steps:
1 Prepare the SQL statement expression by calling the prepare function (for example, SQLPrepare()) and passing the statement or expression as the statement argument.
2 Call the bind parameter function (SQLBindParameter()) to bind any parameter markers you have in the SQL statement. The solidDB® server supports dynamic parameter markers represented by a question mark (?).
3 Execute the prepared statement by calling the execute function (SQLExecute()).
What to do next
To re-execute the prepared statement, call the execute function again.