Re: 4GL Database Feature Question

On 8 Jan 1998, Axel Granholm wrote: > We have a 4GL application that has been growing in size over the last 6 > years, and have a question about fragmentation aware SQL from within the > application. > > Many SQL statements in the 4GL application make reference to program > variables in order to return data from the database. Not very unique. > Our problem is that this application does not prepare many of these > statements, and therefore fragmentation elimination does not occur. That > is because the SQL is optimized at the prepare state, and then the > Dynamic SQL is opened with a USING clause. Too late for the optimizer to > know how to eliminate fragments. As Billy said, if you have to prepare the statements to avoid the USING clause so that the optimizer will do fragment elimination, then you have to revise the code to do the PREPARES. AFAIK (which is not as far as I'd like, in this case), there wouldn't be any way for an upgrade to do fragment elimination, unless an ESQL/C statement with variables listed in it would have fragment elimination done too -- and I don't think that happens. What I mean is: if the following ESQL/C code is optimized for fragment elimination: EXEC SQL SELECT value INTO :variable FROM ... WHERE somecolumn > :input_variable; then the following, equivalent I4GL code should be optimized for fragment elimination too: SELECT value INTO variable FROM ... WHERE somecolumn > input_variable If the ESQL/C cannot be optimized for fragment elimination, neither can the I4GL. > We could invest time to go back through the application to change the > "Important" SQL that is likely to use fragmentation such that they are > PREPARED statements. (Ouch) > > Does Informix have any plans to keep the 4GL tool current with the > features of the database? More or less; the 6.10 release due out at the end of 98Q1 should be using the 7.2x ESQL/C, so you will automatically get whatever benefits there are from that versino of ESQL/C. > Or, should I change my future development strategy to ALWAYS prepare my > SQL? If you have major time critical SQL statements, then yes, prepare them. If they are seldom executed statements, then any minor performance hit from not preparing them is probably outweighed by the more verbose coding necessary, which brings with it the possibility of errors. Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>