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

Lisa Spielman wrote:
> 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.

I am not going to comment on the performance under Oracle, but ...

For an INSERT statement, you are not going to have much complexity unless it is an INSERT ... SELECT; the INSERT ... VALUES statement is pretty much fixed in complexity. Nevertheless, under Informix, if you are going to repeatedly insert data with it, you should continue to prepare the statement once, and then execute it many times. This way, the DBMS only examines the SQL code once, rather than once for each row inserted. The chances are it comes up with the same plan each time, and if you have IDS.2000 or Foundation.2000 then the old plan should be reused, even if you do not use PREPARE and EXECUTE. But if you need to deal with multiple versions of the Informix databases, then stay as you are. Similar comments apply to UPDATE, DELETE and even SELECT statements, though the tradeoffs can be more complex because the statements can search the tables and sometime you need to reoptimize the statement because the search criteria are very different. In the more recent versions of ESQL/C, there is a way of opening a cursor WITH REOPTIMIZE (or REOPTIMIZATION; check the spelling in a manual).

Whatever his other failings, your consultant sounds as if he was correct about this.

--
Jonathan Leffler (jleffler_at_informix.com, jleffler_at_earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h> Received on Tue Sep 14 1999 - 00:34:31 CDT