| Hi,
For the record....
I have been at the customer together with an Informix specialist.
the performance problem is solved!
What has been done.
Looking at the activity during the archive we noticed that the database engine
was constantly accessing the 'cha' table. And it seemed to do it in a
sequential way.
As this is the table containing history information, this is probably the
reason why you did not encounter the problem ( the default is 'no history')
So this could be the explanation that the testsresults of the engineering
system and Ptt's system were totally different.
As the access was sequential at the table, we had a look at the index.
surprise: it was there.
Now the reason that the access was sequential, despite of the index is
something called ' the optimizer'
The optimizer of an Informix database may decide to access a table in a
sequential way. This decision is made based on 'statistics' information.
Now, there is a sql-command 'update statistics medium' that triggers the
optimizer about the layout and contents of database tables.
When the command is never issued, the optimizer thinks the number of rows
in the table is '0' and will always do a sequential search.
After issueing the above command, the archive speed was very fast.
Note: Is is funny that other tables were not affected by not having issued
this command.
Regards
GJ
|