Re: Prepared V Inline sql

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. When you do inline SQL, the client and the engine go through a process of parsing, error-checking, swapping data, and finally sending a command to the engine saying "execute this by-now-parsed-and-optimized" SQL using this data. A total of about 7-8 steps each time it's submitted, plus a lot of communication overhead. When you do a prepare/execute, you do all the parsing, error-checking, and optimizing at prepare time, and at run-time you just send it a command to execute using the included data, for a total of only 2-3 steps. The engine keeps the prepared statement cached until you free it and you miss out on a lot of the overhead. Actually, I'm surprised that you only saw a 3x difference. I've seen improvements of up to 10x in real-life code. The trick is, PREPARE everything that gets executed many times inside a loop. Just about all SQL can be prepared....and you'll get the benefits on all of them. -- --------------------------------------------------------------------------- Joe Lumbley(jlumbley@netcom.com) ---------------------------------------------------------------------------