You can improve performance by preparing statements that are expected to be executed multiple times.
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 types of overhead associated with the SQL prepare operation can be avoided:
– allocating memory for the statement,
– checking access rights,
– 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.
To prepare and execute an SQL statement complete the following 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 that are represented by a question mark (?).
3 Execute the prepared statement by calling the execute function, SQLExecute().
To re-execute the prepared statement, call the execute function again.