Re: OnLine 5.0 ESQL/C Query timing problems

cwakins@leia.alloys.rmc.com (Clem Akins) wrote: >brent@hypercom.co.nz (Brent Jackson) wrote: > >> 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!) >> 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. I believe I have got to the nub of this problem. I borrowed the customers back up machine and restored their data from an archive. I then wrote an ESQL/C test program to just do the processing that was having the problem. I then rewrote it in a variety of ways and got the processing taking wildly different times. (FYI : merch_id is the primary key on merchant which has 2500 rows, and merch_id is indexed on tran which has 250000 rows. About 1000 rows are added to and 500 deleted from tran each day. The test query returned 6 rows of the 1000 that were greater than yesterdays key). The original code was : $DECLARE acquirer_curs CURSOR FOR SELECT some_columns INTO $some_variables FROM tran WHERE primary_key > $yesterdays_primary_key AND merch_id IN (SELECT merch_id FROM merchant WHERE group_code = $this_group_code); For my test query this took 53 seconds to run. Rewriting it to be : $DECLARE acquirer_curs CURSOR FOR SELECT some_columns INTO $some_variables FROM tran, merchant WHERE primary_key > $yesterdays_primary_key AND tran.merch_id = merchant.merch_id AND group_code = $this_group_code; ... also took 53 seconds. However, rewriting it to be : $SELECT merch_id FROM merchant WHERE group_code = $this_group_code INTO TEMP temp_table; $DECLARE acquirer_curs CURSOR FOR SELECT some_columns INTO $some_variables FROM tran WHERE primary_key > $yesterdays_primary_key AND merch_id IN (SELECT merch_id FROM temp_table); ... made it take only 11 seconds. Now for the interesting bit. I ran an "UPDATE STATISTICS FOR TABLE tran". The original test query then only took 11 seconds (the two new methods were unchanged). [Incidently: In systables for tran, npused was 18169 originally, and became -759 after the update statistics. I updated systables setting npused to nrows * (rowsize+4) / 2020 (ie 18472) and this had no effect on the queries (ie the same as when -759)]. I then used "SET EXPLAIN ON" to isolate the differences. When using the join, a sequential scan of merchant was followed by using the merch_id index on tran. The original (after the update statistics) used the primary_key index with a subquery sequential scan on merchant. Similarly, the temporary table method used the primary_key index with a subquery sequential scan on the temporary table. CONCLUSION : The optimiser is making a choice between which index to use. Some days it erroneously chooses the merch_id index which takes 5 times longer than the primary_key index. (I resolve to be very cautious of sub-queries in the future). QUESTIONS : Can anyone tell me what it was that the update statistics changed that would cause the optimiser to not use the merch_id index ? Are there dangers in explicitly coding the temporary table method ? Will it ensure that the merch_id index is not used ? Any and all comments appreciated. Cheers, Brent Jackson. brent@hypercom.co.nz Phone : 64-9-3603593 Fax : 64-9-3602840