Unanswered: Update partionned table in batch mode too slow
Hi, I have program that launch an informix procedure 90 times, in this procedure I'am doing a loop on a table with holds accounts in order to update some attributes in an other pationned table, in each call of the procedure I deal with a range of accounts, but my propblem that is too slow, for 4 000 000 accounts, the program make 13hr to execute, I think that the destination table (the partionned one) wich is impacted by updates is slow, also I can tell you that the chunks are in a windows 2008 server R2 on a compressed disk.
any help plz
is it the partionning problem or the compressed disks or other parameters in informix.
there are tons of possible reasons for you process to be slow, and when it is about updating 4 millions of accounts, you should pay a special attention to do it in the best possible way.
I have never seen table partitionning being the cause of poor performance, generally it is the cause of high performance.
I would say that Windows compression is not a good idea because it will make informix writes slower.
Depending on the version of Informix you run, check with onstat -g iof, or better onsstat -g ioh if in 12.10, and see if write times are lower than 0.001 seconds or not
Also pay attention to your checkpoint times and the balance between LRU writes and checkpoint writes. If you are in 11.10 or higher, you have absolutely no interest in having LRU writes, and give preference to checkpoint writes that are more efficient. this is not true if you are below version 11.10.
Does the process seems slow during ALL TIME (can you see that with displayed counters or similar), or does it have fast phases then slow or almost stopped phase ?
If fast then slow, this probably points to application slowdown during checkpoints, which may be due to windows compression.
Also, you may run out of buffers. CHeck your read cache % and write cache % with onstat -p: if read is under 90% and write under 80%, you need more buffers.
Now in general, updating that many rows will produce a lot of logical log writes, problably lots of index writes and if you indexes are complex (many columns), performance will decrease and decrease during the process. This may be one of the reasons, that can also show fast moving phases, then slow phases.
Another reason can be the logic of you process: does it concentrate on reducing repetition of identical reads (i.e read the same data that won't be altered thousands of times...)
Finallly, if you run 11.70 or higher you can use the STMT_CACHE parameters that will put query plans of statements repeated many times in cache.
You have many things to check.
What version of informix is this ? ( no having an old version is not the cause of the problem, but a new one can have features that can help :-)