Re: Prepared statement vs Exec sql insert/update/select

Lisa >>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 proces That would probably mean that your SQL is not very complex, since most of optimization has to do with figuring out the best sequence in which to join tables. However, with a PREPARE, even with a simple SQL (no joins), because you are executing the same SQL statement repeatedly, you will still gain performance because the PREPARE creates the optimization plan once and uses it throughout the session. as opposed to creating the optimization plan each time you call the same unprepared SQL statement., thereby saving you the the time for creating the optimization plan each time. Dont know much about Oracle, but would suppose the same holds there too. HTH Sujit Lisa Spielman <lisa.spielman@compaq.com> on 09/13/99 07:34:57 AM Please respond to Lisa Spielman <lisa.spielman@compaq.com> To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: 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