Re: Prepared V Inline sql

Joe Lumbley wrote: > > Peter Jennings (peter_jennings@uk.ibm.com) 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! > > <snip> > > Peter, > > This is sort of a RTFM question. If you'll look at any of the performance > tuning sections of the manuals, you'll get the whole story, but here's the > abridged version. > <snip> > --------------------------------------------------------------------------- > Joe Lumbley(jlumbley@netcom.com) > --------------------------------------------------------------------------- Joe, I agreee with you , and I can't deny the evidence of my own eyes. As Jim Gordon suggests in his reply, we come from a DB2 background. However it depends on which manual you read. We took over this project 2 or 3 years in, and not all the manuals are still available, paritcularly the ones about optimising. What misslead us was that, in the ESQL/C course notes, it states that dynamic sql should be used where something is not known about the number or type of passed data or about the structure of the sql statement. It goes on to say that non-dynamic sql is used where the text of the sql statement is known at compile time with the possible exception of the contents of host variables. It does not explicitly state that prepared sql is also much faster in the example I gave. There is no argument here, we just read the wrong part of the wrong FM! Thanks for clearing up this missunderstanding. Peter Jennings.