Re: how to speed up SE and DS

Andre Koppel wrote: > > I have written an application, that manages a great INFORMIX-database with > several tables. I am using SE on a LINUX PII/400 for development, the > production-computer is a SUN Enterprise 450 with INFORMIX-DS installed. > This is my first application written in ESQL/C. I have found, that > inserting a row into a table, that contains more than 10000 rows is very > slow (on both SE and DS). Are there any genreral hints or a FAQ how to > speed up the insertion? To Billy's and Laurent's comments let me add: For the production instance: What is your NEXT SIZE for the tables and the row size? Also are the several tables in the same dbspace? You may be thrashing many fragments of the several tables interleaved. Run oncheck -pe or oncheck -pT database:table to check the number of extents (-pe willalso show you how the extents of the various tables are interleaved). This can be slowing you down. Also of the indexes are attached and not on a key that is strictly increasing as the rows are added the index-data locality may be poor. Additionally if the indexes were created on empty tables, with a NEXT SIZE of 16K, the nodes would have begun to split after about 10000 inserts with a key length of about 16 bytes. The new nodes would be located many pages further away on disk further slowing index updates which are required to insert. So the suggestions are: 1) Separate active tables to private, or at least semi-private, dbspaces to eliminate interleaving and reduce or eliminate fragmentation. 2) Detach indexes whose keys do not match the load order of the rows. 3) Increase NEXT SIZE so the tables do not fragment and interleave. 4) Defragment any tables not moved to a new dbspace. Use ALTER FRAGMENT ON TABLE ... INIT IN ...; both to move tables and to defragment them. Additionally look into increasing the size of the communications buffer for jobs doing large batch type INSERTs or SELECTs using either the environment variable FET_BUF_SIZE=[4096 < value < 32767] or the global variable FetBufSize in your program and use PUT cursors for inserts. Art S. Kagel