One of our development team found something strange, whilst trying to tune a large month end job.
The results boils down to:
Test 1
1. Drop/create working table (say abc_wk)
2. Update stats high for all tables involved in the job except abc_wk.
3. Run job to populate abc_wk (110000 recs)
4. Run update stats high on abc_wk after population
5. Run rest of the job
total about 9 hours to complete
Test 2
1. Drop/create working table (say abc_wk)
2. Update stats high for all tables involved in the job INCLUDING abc_wk.
3. Run job to populate abc_wk (110000 recs)
4. Run update stats high on abc_wk after population
5. Run rest of the job
total about 3.5 hours to complete
A difference of 5 hours.
The server was dedicated for the job, not other tasks such as backups were running on the development machine.
I first thought that Informix may not be clearing the distributions after the drop/create but a little test proves this is not the case.
How does running update statistics high on an empty table improve performance. Unfortunately I do not have much more details of the tests.
BTW, we are using IDS 7.31 UD1 on Solaris 7, E3500 with 4CPU 2G RAM
The developers ask me if this method can be used to increase performance of other jobs, before they go and alter other jobs. I could not give them an answer.
It would be greatly appreciated if anyone has an explaination or have a similar set up which they can test and see if they get performance increase using the same method.
The results boils down to:
Test 1
1. Drop/create working table (say abc_wk)
2. Update stats high for all tables involved in the job except abc_wk.
3. Run job to populate abc_wk (110000 recs)
4. Run update stats high on abc_wk after population
5. Run rest of the job
total about 9 hours to complete
Test 2
1. Drop/create working table (say abc_wk)
2. Update stats high for all tables involved in the job INCLUDING abc_wk.
3. Run job to populate abc_wk (110000 recs)
4. Run update stats high on abc_wk after population
5. Run rest of the job
total about 3.5 hours to complete
A difference of 5 hours.
The server was dedicated for the job, not other tasks such as backups were running on the development machine.
I first thought that Informix may not be clearing the distributions after the drop/create but a little test proves this is not the case.
How does running update statistics high on an empty table improve performance. Unfortunately I do not have much more details of the tests.
BTW, we are using IDS 7.31 UD1 on Solaris 7, E3500 with 4CPU 2G RAM
The developers ask me if this method can be used to increase performance of other jobs, before they go and alter other jobs. I could not give them an answer.
It would be greatly appreciated if anyone has an explaination or have a similar set up which they can test and see if they get performance increase using the same method.
