Optimizer plan for a running 4ge
Jason Harrington asked how to make a running 4GL program use indexes on a table that starts empty and grows, since the optimizer ignored them; they normally ran a procedure to set nrows to 2000. Jonathan Leffler and John Carlson advised UPDATE STATISTICS or optimizer directives. Andrew Hamm advised updating statistics at about 5000 rows and re-preparing cursors.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello Does anybody know a way of changing a running 4ge's optimizer plan. When this 4ge starts, a table starts empty, so when the 4ge prepares the sqls, it decides it will not use the indexes on that table. We normally run a stored procedure that sets the nrows to 2000 on the table if nrows is less than 2000. This table gets populated within the 4ge, so it grows and the 4ge gets slower and slower!! This SP unfortunately was not run, but I would like to know if we can hint to the engine to make the running 4ge/sql use the indexes? Jason Harrington
Jason Harrington wrote: > Does anybody know a way of changing a running 4ge's optimizer plan. > > When this 4ge starts, a table starts empty, so when the 4ge prepares the > sqls, it decides it will not use the indexes on that table. We normally run > a stored procedure that sets the nrows to 2000 on the table if nrows is less > than 2000. This table gets populated within the 4ge, so it grows and the 4ge > gets slower and slower!! > > This SP unfortunately was not run, but I would like to know if we can hint > to the engine to make the running 4ge/sql use the indexes? Run UPDATE STATISTICS for the table. Use optimizer directives. Both should work. Re-prepare the statements after updating the statistics, of course. (I note that ESQL/C has an option WITH REOPTIMIZATION for the OPEN statement which is not, AFAIK, supported by I4GL.) I confess I'm not sure I'd use the stored procedure to set nrows. It smacks of unsupported behaviour (and/or cheating). That said, it probably does the job, but then so does UPDATE STATISTICS. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
On Sun, 26 Oct 2003 22:54:06 -0000, "Jason Harrington" <jason.harrington@blueyonder.co.uk> wrote: >Hello > >Does anybody know a way of changing a running 4ge's optimizer plan. > >When this 4ge starts, a table starts empty, so when the 4ge prepares the >sqls, it decides it will not use the indexes on that table. We normally run >a stored procedure that sets the nrows to 2000 on the table if nrows is less >than 2000. This table gets populated within the 4ge, so it grows and the 4ge >gets slower and slower!! Accccckkkk! Doesn't sound good stuffing nrows like that . . . . probably not supported, either. Any way to include an 'update statistics' as part of the 4gl code? > >This SP unfortunately was not run, but I would like to know if we can hint >to the engine to make the running 4ge/sql use the indexes? > Depending on the engine version, you could use optimizer directives to force the SQL to use the specified index. >Jason Harrington > >
Jonathan Leffler wrote: > > Run UPDATE STATISTICS for the table. >[SNIP] > I confess I'm not sure I'd use the stored procedure to set nrows. It > smacks of unsupported behaviour (and/or cheating). That said, it > probably does the job, but then so does UPDATE STATISTICS. Yup - for any program that loads a large table from scratch, AND if it needs to scan the table for pre-existing rows (ie an insert-or-update process) then I recommend to our programmers that they do update stats on the table at about 5000 rows and then re-prepare the cursors. Usually there's only an insert and a select or update cursor to re-prepare. Even if the critical index is being loaded with an always-increasing key, just getting the row counts up is sufficient. However, if the query component of the program is significantly complicated, you might find value in doing update stats every 10 or 20 thousand rows after that, but I'd guess that the first one probably should be done after just a few thousand rows. Most likely the stats would only need to be LOW at this point. Wrap the cursor prepares and/or opens with SET EXPLAIN ON/OFF and take a squizz at the quality of the cursors after the prepares. If the query plans stop changing after the 2nd prepare, then that's when you should stop doing the stats. After all, preparing stats takes time, especially when the rows get upto several hundred thousand or into the millions... -- I have a simple philosophy that gets me through life: Never buy cheap toilet paper. Learn to know what is important, and what is not important. - Replies directly to this message will go to an account that may not be checked for a week or two. For more timely e-mail response, use (only in an emergency) ahamm sanderson net au with all the usual punctuation.