Prepared statement vs Exec sql insert/update/select

Is there any benefit to preparing and executing sql statements rather than just having them in an exec sql insert/update/select....

The code runs under Oracle and Informix which is why I am posting this in both newsgroups. Hopefully I will get responses for both databases.

I have read that statements should be prepared when the statement is complex and called repeatedly. What is meant by complex? Many columns? many joins?

It used to be that we needed to prepare/execute the sql because we didn't know some of the the table names at compile time. But now we have moved to using 1 table and partitioning it, so now the table names are known. (We have also had prepares for stmts where we have known the table name). A few years ago, an Informix consultant who turned out to be not very good, told us to use prepares everywhere. (This is when we only ran under Informix.) When we added Oracle to the code stream, we kept the prepares.

Most of our sql statments have many columns and no joins. They are executed repeatedly. The system runs 7 x 24 with high volumes of transactions to process.

    thanks for any help, Lisa Received on Mon Sep 13 1999 - 09:34:57 CDT