Re: delete very slow with SE ?

Frederic Bouquet (Frederic_Bouquet@ascus.com) wrote: : An Informix SE kernel release 5 is installed on a SCO pentium 90 MHz. : It takes 8 mins to delete at least 200 rows (or more) from a table that : contains about 56000 rows !!! of course the server is not overloaded with : other task. : Here's the structure of this table : : mou_numecr CHAR(11) : mou_numord INTEGER : mou_cptgal CHAR(9) : mou_secana1 CHAR(6) : mou_secana2 CHAR(6) : mou_cptaux CHAR(9) : mou_libel CHAR(30) : mou_montdebf DECIMAL : mou_montcref DECIMAL : mou_codlet CHAR(3) : mou_datlet DATE : mou_montdebd DECIMAL : mou_montcred DECIMAL : mou_qte DECIMAL : mou_immo CHAR(1) : mou_numpiece CHAR(12) : mou_ecrdate DATE : mou_jal CHAR(3) : mou_codedev CHAR(5) : mou_regroup CHAR(6) : mou_pointage CHAR(6) : mou_transtreso CHAR(1) : here's the index for this table : : CREATE INDEX i_mou_cptgal ON mouvts (mou_cptgal) : CREATE INDEX i_mou_cptaux ON mouvts (mou_cptgal,mou_cptaux) : CREATE INDEX i_mou_ecrdate ON mouvts (mou_ecrdate) : CREATE INDEX i_mou_numecr ON mouvts (mou_numecr) : CREATE INDEX i_mou_pointage ON mouvts (mou_pointage) : CREATE INDEX i_mou_numord ON mouvts (mou_numecr,mou_numord) : here's the delete command: : delete from mouvts mou_numecr="95010100001" : Do you have any explanation about a so long time (8 mins) to delete so few : rows ? : Frederic I suggest you that you try dropping the indexes 'i_mou_cptgal' and 'i_mou_numecr' which are redundant. Each index is duplicated by 'i_mou_cptaux' and 'i_mou_numord' respectively and only serve to slow down updates & deletes. The performance of queries should not be affected by dropping these indexes permanently. Given that you say you have 200 rows each with the value "95010100001" you have a high number of duplicate values for each value, thus making a single column index like 'i_mou_numecr' inefficent. Hope this helps ... -- ---- Adam Middlemass (amm@booth.com) +44 113 - 2832000 ext:2533 Computer Development, Booth & Co. Solicitors