Re: OnLine 5.0 ESQL/C Query timing problems
Version 5.01 of Online has a bug in Update Statistics. I
have found my copy of the proposed work-around from Informix
Tech Support. (Bug #14829 - Scheduled Fix 5.02.UC1) This bug
affects tables that undergo bulk inserts and bulk deletes to
get negative values in np_used statistics (in systables), also
reported by 'tbstat -t' and 'tbcheck -pt'. This causes the
query sloptimizer to choose extremely poor search methods
for the afflicted tables. Re-running UPDATE STATISTICS fails
to clear up the problem.
Note: This fix was suggested by Informix Tech Support, and I
take no credit, responsibility, or assume any liability for
this suggested work-around.
Instead of running UPDATE STATISTICS from dbaccess or 4GL, run
execute procedure "update_stats ()"
CREATE PROCEDURE update_stats ()
DEFINE numpgs, numrows, rowsz, tableid INTEGER;
UPDATE STATISTICS FOR TABLE;
FOREACH SELECT tabid, rowsize, nrows
INTO tableid, rowsz, numrows
FROM systables
WHERE npused < 0
LET numpgs = numrows / (2020/(rowsz+4));
UPDATE systables
SET npused = numpgs
WHERE systables.tabid = tableid;
END FOREACH
END PROCEDURE
Paul Kapp ----> PLKapp@aol.com -OR- plk@usorder.com