OnLine 5.0 ESQL/C Query timing problems

> We currently have a problem with an ESQL/C query running on an > INFORMIX OnLine database version 5.01.UD2. This particular query is > run every day at about 6:45 pm. Some days it takes 12 seconds to run, > other days it takes about 120 seconds. > > An update statistics is run every night at 1:00 am. This is almost certainly NOT your problem. Update stats is only helpful if the *total number* of rows in a given table has changed significantly since the stats were last updated. A mature system will have a stable number of rows, as some are added and some are purged off periodically. Unless your tables are growing by some healthy percentage each night (or shrinking) you are wasting time and machine effort by updating stats that often. (A DBA pet peeve of mine!) > Nobody else is on the machine, and no functions on the system lock > that table (although inserts are being done to it constantly). The > system is processing messages and this message has a 30 second time > out. On the days that it takes 120 odd seconds, the system > performance is adversely affected as 4 or 5 of these transactions are > processed, tieing up the machine for 8 to 10 minutes. > > An attempt to add another index to improve the query resulted in it > taking 11 minutes !!! This index was then dropped. > > Running the same query from the command line always takes about 8 > seconds. We cannot reproduce the problem on our development system, > even when using the same data. It only occurs on the customers > machine. This is the key. I'm just taking a wild guess, here, but I'd bet your development box does not recieve the same updates (constantly) as the customer's. This constant addition to tables could cause your symptoms, as each addition locks rows. Do your tables have row locking or page? Do you allow dirty reads (and would that help? Is anybody reading?) > Has anybody got any ideas on where to look or what to try to resolve > this ? That's my $0.02 worth. Good luck, __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|