SQL programming / SQL performance |
Optimize prepared cursor statements by using the FROM clause of DECLARE CURSOR.
PREPARE s FROM "SELECT * FROM table WHERE ", condition DECLARE c CURSOR FOR s
While this has no performance impact with IBM® Informix® database drivers, it can become a bottleneck when using non-IBM Informix databases:
Statement preparation consumes a lot of memory and processor resources. Declaring a cursor with a prepared statement is a native IBM Informix feature, which consumes only one real statement preparation. Non-IBM Informix databases do not support this feature, so the statement is prepared twice (once for the PREPARE, and once for the DECLARE). When used in a big loop, this code can cause performance problems.
DECLARE c CURSOR FROM "SELECT * FROM table WHERE " || condition
By using this solution only one statement preparation will be done by the database server.