Re: Dynamic SQL vs. ESQL/C

Hi, >Date: Thu, 18 Jul 96 09:11:26 -0700 >From: Joan Armstrong <joana@hpvcpja.vcd.hp.com> > >In message <199607181555.IAA07648@anubis.informix.com> you write: >>(1) How do you propose to call dynamic SQL from C? > > ESQL/C. > >>(2) ESQL/C is a thin layer which translates SQL code into C. > > Yes. I did not express my original question well. The two > options I am looking at are: > 1. ESQL/C in which the sql is known at compile time and hard > coded. > 2. ESQL/C in which the sql is constructed and 'prepare'd at runtime. Aah... The correct magic incantation mentions 'static SQL' as well as 'dynamic SQL', and if you'd done that, all would have been clear on pass 1. > I am not going to call the ESQL/C library functions directly or > anything fancy/stupid like that! Phew! > I expected option 1 to be faster but that is not what my initial > testing is showing. Why? If you look at the code which is generated, you'll see that even static SQL statements are prepared -- the difference is that they are prepared automatically (and prepared every time they are used), whereas with dynamic SQL statements, they can be prepared once and used many times. So, with Informix ESQL/C, you will often get better performance from carefully coded dynamic SQL than from static SQL. This contrasts with systems such as DB2 which actually pre-compile the SQL statements. In those environments, what is left in your code is a reference to the precompiled statement, which is stored in the database itself (similar to a stored procedure in some ways, but DB2 at least actually generates machine code to execute the query, whereas Informix uses an interpretive scheme). If the pre-determined query plan is still valid, it is used at run-time; if not, then the statement is automatically recompiled and the new version is used. Note that a query plan is not recompiled just because there may be a better query plan now than when the compilation was done; it is only recompiled because something that was being relied on (eg an index) has been changed. In such systems, the static SQL statements have less overhead at runtime and dynamic SQL does work more slowly. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: If you aren't sure about all this, use a monitor to track the statements going back and forth between the ESQL/C application and the Engine and see what is happening...