Re: Prepared V Inline sql

Peter Jennings wrote: > > The environment is Online V5 under AIX. > > We have been running some simlple tests to compare the > performance of prepared sql to inline coded sql in an > esql-C program. Our intuition and experience of other databases > tells us that prepared (dynamic) sql _should_ be slower than > inline coded (pre-compiled) sql since the prepared sql > will require parsing each time it is called whilst the inline > code will have all references and pointers resolved at > compile & link time. > > Not so! > > The prepared sql loop takes 13 seconds to execute. > The inline sql loop takes 30 seconds. > > On examining the intermediate C file generated by esql, > it appears that, rather than converting the sql into > some form of compiled sql, it just passes the strings > virtualy unchanged to the database engine. This requires > the engine to completely re-interpret the string 10000 times. > On the other hand the prepared sql would appear to only have > to substitute the value of the host variable. > > Am I missing something here? Please tell me that there is > a compiler switch I should have thrown! Is V7 any better? > > Peter Jennings. It's really pretty clear in the manuals. You obviously come from a DB2 backround which precompiles SQL in the engine during program compilation. Informix doesn't do this the same way. All SQL in programs is effectively dynamic but you have two choices with it. Prepare it in the program which case the engine will parse and optimise it and provide you with a pointer to it. This means that this work doesn't have to be done each time you call it but must be done once per program run. It lasts as long as you do not free the prepared statement. True dynamic which just takes the string and passes it to the engine for handling each time. All good Informix programmers use prepared SQL as much as they can. Inline code of the type you refer to - where the compiler takes the sql gives it to the engine which stores, parses and optimisers it, returning a pointer to it which is embedded in the executable, requires that the database supplier also provides a tightly coupled compiler. That isn't possible in an open environment. Look into the use of stored procedures which creates the same effect, almost. These have to written separately and can be called via either prepared or dynamic execute statements but the code within them is all pre-compiled, optimised and often cached in the engine. Down side for Informix is that SP's are interpreted and have only a limited language capability. Cheers - Jim -- ---------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ---------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!