We have a custom table that has a problem with one of the indexes. We are running on Informix 7.31 uc3. It is an xref part number table, where the alternate part number (index2) is 64 byte string. Sometimes, after reorganizing and updating statistics - it's fine for awhile - but it never seems to last for more than a half day before getting hosed. The table contains about 60,000 rows of about 100 bytes. It can take up to 10 minutes to open a display session with this index. There should be no extents on the table. It works just great in tbase.

If anyone has any idea what could be causing - please respond. Thank you!

Give me an easy one.

The only thing I can think of is that somehow the table definition did not get created properly in Baan.

Check the dd file for the table and see if it matches your definition in Baan.

I would think that even without an index 60,000 (small) records should not take 10 minutes to display.

Thanks for you reply.

When you say check the dd file, I suppose you mean the data def. file for that table in the ../dict dir. I've done that, and though I'm not that familar the format of that file, it appears to jive with the data def. as defined in Baan.

I will attach the file, maybe you will see something.

Thanks again for the help.

That's what I meant and you're right, it fits the description.

Wasted my good thought of the day ;)

In the mean time, I retract my comment about 60,000 records not being a big deal. Obviously with a 100kb rec. size, this amounts to a 6mb table, which I would say is a big deal.

What about the code you use to retrieve the data?
Using any order by or other hint-offsetting commands?

It seems to messed-up differently at different times. Right now, If I use a straight Baan generated "display" session, it takes about 50 seconds to come up. If I go into GTM, and do a search on index2 with all Z's it brings up a row right away, but if I do a "P" for previous - it takes about 50 seconds.