Re: Dynamic SQL and speed

In article <37u5to$afp@www.interramp.com>, Rob Switala & Bill Edgin <pp000207@pop3.interramp.com> wrote: > >My systems admins at a development site just started using dynamic SQL. >This seems to have speeded the transactions (posting to the database) >tremendously. The Audit files and transaction log sizes were also reduced >considerably. Does anyone have experience with this? Is there a downside >to this apparent windfall? > >Thanks, > >Bill The reason for a performance gain is that the PARSE step in the execution of each SQL command only happens once during program execution. With static SQL this occurs every time. Other RDBMS vendors use a shared SQL cache to avoid this behavior. I do not know of any downside, other than additional testing I had to do after retrofitting many KLOCs of code with dynamic SQL. I found that if you need to retrofit code, a good technique is to use two static variables, one a T/F boolean flag and a static character array for placing your SQL into. Set the boolean on the 1st time thru and do the parsing then. This way you need not modify your application structure and the only overhead is to check whether the boolean is set indicating that the Dynamic SQL Prepare has already executed. Hope this helps. -Greg