We upgraded informix from 7.31UD1 to 7.31UD9
It's now running for a couple of weeks, but it's not running perfect.
Calculating cost prizes are very slow.
There is enough free disk space, CPU, memory and I did an update statistics.

Is there somebody with the same problem?
Is there something I can check or do?

I can't find the reason why it's slow. Doesn't look like the server is busy.

The only solution I can think of is go back to 7.31UD1 :-(

Hello,

Did you update statistics medium or low ? May also be worth looking closely at the tables the session is using ie. a reorg.

Anything in the informix log ?

Also spotted that there is a performance fix for ticpr2210m00 on the support web site.

Cheers
Neal

I only did an update statistics low. I will try a medium
Already did a reconfig of a couple of tables, but that didn't help.

Hello,

May also be worth putting a trace on the session try and isolate the tables which are causing the performance issue.

Cheers
Neal

Looks like update statistics medium helped a lot.
By trace the session do you mean debug it and look where it's slow?

If you do a search on the board for bshell trace options you can actually add settings into your command line in bw config to write to an SQL trace file.

Glad the medium helped.

Cheers Neal

Looks like it isn't completely solved.
I think I have an index problem.

I did a trace:
================================================
Fetch times of Query (QID : 74) Trans 3 (ticpr2230s000) in SqlClose :

SELECT ticst001.* WHERE ticst001._index3 >= { :1, :2, :3, :4 } ORDER BY ticst001.sitm ASC, ticst001.opol ASC, ticst001
.pdno ASC, ticst001.pono ASC AS SET WITH 2 ROWS
------------------------------------------------
Nr Rows Fetched : 1
Fetch Time for 1st Row : 51.030 sec
Total Time : 51.030 sec
================================================


This is very slow in informix:
SELECT * from tticst001100 ticst001
ORDER BY hash3

(works fine with hash1 or hash2)

An SQL explain gave this information:
Estimated Cost: 149405
Estimated # of Rows Returned: 192285
Temporary Files Required For: Order By

1) root.ticst001: SEQUENTIAL SCAN

The index isn't used?

No errors found:
s3@/tmp>oncheck -cI baan:tticst001100
Validating indexes for baan:baan.tticst001100...
Index ticst0011001abaan
Index fragment in DBspace datidx100
Index ticst0011002abaan
Index fragment in DBspace datidx100
Index ticst0011003abaan
Index fragment in DBspace datidx100

Can I rebuild an index?

Here are a few examples when it's slow and when not.
(I already reorganized the table and a drop, create from sequential dump)

QUERY:
------
select * from tticst001100
order by hash1

Estimated Cost: 27326
Estimated # of Rows Returned: 192913

1) baan.tticst001100: INDEX PATH

(1) Index Keys: hash1 (Serial, fragments: ALL)

QUERY:
------
select * from tticst001100
order by hash2

Estimated Cost: 27655
Estimated # of Rows Returned: 192913

1) baan.tticst001100: INDEX PATH

(1) Index Keys: hash2 (Serial, fragments: ALL)

QUERY:
------
select * from tticst001100
order by hash3

Estimated Cost: 149642
Estimated # of Rows Returned: 192913
Temporary Files Required For: Order By

1) baan.tticst001100: SEQUENTIAL SCAN

QUERY:
------
select * from tticst001100
where hash3 like "12%"
order by hash3

Estimated Cost: 3853
Estimated # of Rows Returned: 4818

1) baan.tticst001100: INDEX PATH

(1) Index Keys: hash3 (Serial, fragments: ALL)
Lower Index Filter: baan.tticst001100.hash3 LIKE '12%'

QUERY:
------
select * from tticst001100
where hash3 like "1%"
order by hash3

Estimated Cost: 146210
Estimated # of Rows Returned: 187900
Temporary Files Required For: Order By

1) baan.tticst001100: SEQUENTIAL SCAN

Filters: baan.tticst001100.hash3 LIKE '1%'

Hello,

I must confess to not being an expert when it comes to reading to trace files. However can you confirm that you are running the latest version of the session. The support site does mention a performance issue in relation to this session.

Also in respect of rebuilding the table it may be worth at looking at the various options with the reorganise tables session. A search on the board should give you some decent info in respect of the various options.