Neil,
I'm thinking as I write:-

I think the problem comes from the way that Informix re-uses deleted
records.
I assume the table has a fixed record sixe of 219 bytes. That means there
are 9 rows/page.
That gives a total row size for the table with 1500004 pages of 13500036
rows. So you are trying to re-use the 13500036 - 12871602 deleted rows
which are dotted around over the allocated pages. So,when you insert a new
row you have to search for it which could mean reading lots of pages.
However, when the table is re-org'd all of the free pages are together,
there aren't any free rows, and therefore you don't have to look for them.
I have seen similar effects on many tables. And the problem gets worse over
time. If my memory serves me right I had a similar performance problem with
SE back in about 1988. This is why many applications which work like this
will unload the remaining records at month end, drop the table, and then
recreate it, before reloading the records. However with 12 million rows
this could be time consuming.

What do the other "gurus" think?

Malcolm

----- Original Message -----
From: "Neil Truby" <neil.truby@ardenta.com>
To: <informix-list@iiug.org>
Sent: Monday, September 08, 2003 4:07 PM
Subject: Re: Slow insert when table is almost "full". IBM Informix call
376023


> "Andy Lennard" <andy@kontron.demon.co.uk> wrote in message
> news:qNvFE8JIFIX$EwY7@kontron.demon.co.uk...
> > I'm surprised that no-one clever has responded yet.
>
> You have, Andy. Oh, I see what you mean ... :-)
>
> > I guess this is a simple table, with no blobs/etc.
> Yes.
>
> > I also guess there is no intentional fragmentation.
> Yes, that's right, there isn't.
>
> > Is the indexing simple? Is it possible to remove the indexes when it's
> > going slow and see if that helps. Does the fabled 'update stats' help at
> > all in that case?
>
> Hmmm. Indexing is simple. I haven't tried dropping one. I doubt if
it'll
> make any difference, because the indexes are detatched. Update stats?
> Well, Mark Denham suggested an "update statistcis low", and he's clever,
so
> I'll try that too ....
>
>
> > If your hypothesis is correct, then wouldn't the deletion of rows at the
> > end of the month result in large blocks becoming available. Of course
> > if, over time, you add (numerically) more rows in successive months,
> > then fragmentation will start to happen as time goes by...
>
> You'd have thought so. The 2nd occurrence occurred only six weeks after
> we'd rebuilt the table following the first occurrence.
>
> > If you are fragmenting on date, is that working correctly?
> n/a
>
> > Is there any insert trigger being fired when these rows go in?
> No.
>
> > Is the insert going straight into the table, or via a view?
> Direct to a table.
>
> > Any CHECK constraints?
> No, only a primary (and therefore unique) key
>
> > Yes, I'd agree that your hypothesis looks reasonable, but wonder whether
> > it's data or index fragmentation that may be responsible. Maybe you can
> > pursue this in your lab? If you can get it a bit repeatable, then
> > perhaps you could try some things before you get to loading the data in.
>
> Indeed. I'd suspect data, since the problem seems to coincide with the
> number of pages allocated being equal to the number used, as per the
oncheck
> output. But who knows ...?
>
> > A suggestion for testing in the lab, if you can do it, would be to 'dd'
> > the dbspace before trying anything, then at least you can get back to a
> > known state.
>
> You reckon? Only by dropping all the other application dbspaces first I'd
> have thought ... and even then it's timestamps would be out of step with
> rootdbs, llogdbs, physdbs etc ....
>
> Thanks for stimulating my mind, I'll let you know how your suggestions go.
>
> cheers
> Neil
>
>

sending to informix-list