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. 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

Hello Is's so with Informix SE, you can have a good performance for inserting rows but never try to delete rows from a large table. In your case I would plan to migrate your backend to Informix Online. Michael Milom schrieb: > 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. > > 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

Michael - Unless you've already solved this problem, I have an entirely different opinion. I've seen Informix do this before. Simply, the query optimizer has arbitrarily stopped using *any* indexes at all, and is directing the engine to perform serial searches. Informix has been having this problem for me for over two years, and they seem unable to identify it as a problem. I've mostly seen it on large (million+) tables. The solutions are: 1. UPDATE THE STATISTICS !! Look at the Informix performance guide for suggestions ... but basically you want to drop the distributions and re-create them, and then do a separate "HIGH" update on that table for each primary and foreign key. 2. FORCE the query to use the index ... there are some recently-added options on the "select" statement to do this. 3. Take a look at the "OPTCOMPIND" (??) parameter in onconfig 4. There's some other onconfig parameter which directs the optimizer to bias itself towards returning the first record quickly or all the records quickly .... try twiddling that one as well. Good luck! P.S. #1 above is the correct solution, if it works for you. Rich Michael Milom wrote: > 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. > > 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 -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com