Re: Extremely slow deletes from table

In article <245F837E570FD211976200A0C984AF0E56B12C@mailman.mdf.bassinc.c om>, Pachiano, Vince <pachiano@bassinc.com> writes >> -----Original Message----- >> From: Michael Milom [SMTP:smilom@bellsouth.net] >> Posted At: Sunday, October 10, 1999 10:47 PM >> Posted To: informix >> Conversation: Extremely slow deletes from table >> Subject: Extremely slow deletes from table >> >> This is my first post, but here goes... >> >> We use SE 7.24UC5 for our primary database (c. 24GB, 450 tables). Our >> hardware platform is an IBM RS/6000 with 8 GB memory. The table in >> question resides on a SCSI drive (in a RAID array). >> >> Here is the problem. We distribute magazines. Four tables are used >> to >> keep track of which products go into which boxes, and which boxes go >> to >> which customers. Everything has been working fine for about 10 years. >> Lately, however, we have run into a very strange problem when trying >> to >> delete records from our pkgs (packages) table. There is a unique >> index >> on the pk_pkg_id field. The pk_pkg_id field is defined as >> decimal(16,0). We do not currently have referential integrity >> constraints defined, nor do we use transactions or logging. >> >> Even specifying the key directly, as in >> >> delete from pkgs where pk_pkg_id = 3685665001;>> >> results in very long delete times, sometimes in the order of 2-3 >> minutes. Insertions are virtually instantaneous, and there are no >> perceptible problems with selects. bcheck (many, many times) has >> turned >> up absolutely nothing. The data appears to be consistent. >> Make sure that the indexes which you have on tables are ALL unique. Sounds like one of the indexes on the table is not that unique. When an value in an indexed column is not very unique Informix keeps a list of rows with that value in the indexed column and has to rewrite the list when you delete from it. E.g. create index t1 on tab1(mycol1). If the table t1 10000 rows where mycol1=6 then Informix keeps a list of 10000 rows. When yoy delete a row which contains mycol1=6 then Informix rewrites the list of 10000 rows! Hence it is slow. Make sure that pk_pkg_id in part of every index (add it on the end if needed). >> The pkgs table currently contains about 3 million records, but that is >> relatively small compared to other tables in the db which are working >> fine. We've tried everything we can think of to correct this >> problems, >> but nothing helps. >> >> Any suggestions? >> >> Thanks in advance > -- David Williams