Hi, I have a raw fragmented table with 10 indexes, I want to load many files of approx 50 million row each file with external tables, the table now contain 163 Million row, but I remark that it begin loading very fast 8000 row/sec or more and after hours, the loding become too slow it step by 100 row/sec or 500 row/sec sometimes, how could I analyse this behaviour I have to load 1 billion row in this table and I want to kill the process and repeat from bigening each time, should I load a small numerous files of 5 million maybe each??? Help plz
If I kill the process and restart the database, the initial load would be vers fast and after that it start to go very slow, help plz.
You are using the right method: RAW tables and external tables.
Typically, this behaviour happens when you have "complex" indexes on your table. When I say complex, I mean globally many columns in the indexes. You are saying that the table has 10 indexes, which is for me a sufficient reason to get the exactly problems you have.
I would say that getting temporarily rid of non indispensable indexes and rebuild them after. this may be long to rebuild, but at the end it is probably profitable.
Check also whether all those indexes are efficient. Seing for very far, it may be not the case.
The cause of this phenomenon is that periodically during the load, the engine takes time to split b-tree nodes and typically slows the whole process down more and more, like if it were bleeding more and more.
You may want to check a presentation I did in the IIUG 2013 Conference about Indexes, which may give you pertinent ideas about indexing.
Go either to the International Informix Users Group website, click on "member area" (you can register for free and become an IIUG member, which gives you many advantages if you are not yet a member).
Else you can get it from slideshare
Merry Christmas in the meantime!