RE: Slow data load

If you use the load command, you may need to take the following actions before loading the table: * Lock the table in an exclusive manner. Otherwise, Informix will maintain row or page level locks. * Issue a checkpoint. * Disable or drop indexes and referential integrity constraints. Larger values in for BUFFERS, LRU_MIN_DIRTY and LRU_MAX_DIRTY in the $ONCONFIG file can also speed the loading of large tables. The dbload utility is another option. It issues periodic commits, which releases locks and avoids a "long" transaction. -----Original Message----- From: James Richardson [mailto:richardsonja@logica.com] Sent: Tuesday, December 01, 1998 06:48 To: informix-list@iiug.org Subject: Re: Slow data load Alastair Bell <113322.3375@CompuServe.COM> wrote in message <#WidZl4G#GA.186@nih2naab.prod2.compuserve.com>... >I am trying to load a large table into On - Line v 7.20 (running >on HPUX 10.10). The table has 3million rows with a row size of >700. Initially it runs quickly (20,000 rows /min) but after >loading 2million rows the performance falls away quickly until at >the 2.5 million mark it is only loading 800 rows/ min. >The load routine is a 4gl with a cursor, but even when I try >using a load statement from an ascii file a get the same problem. >Note there are no indexes on the table whilst the data is being >loaded. Does anyone have any suggestions how to speed the data >load up. Also note that I am building a test database so nothing >else has access to it. > >-- >Alastair Bell 1) What are the extent sizes for the table? If you know there are going to be at least n rows, and you know the rowsize for the table, then use the "extent size x next size y" part of the create table statement to make sure you use the minimum number of extents. and when you have that sorted... 2) If its a junk database, or one that you intend to preload, then put into service, you can make the datbase no logging (which saves the server having to write all that info into the logical logs). Use the ontape command for this: export ONCONFIG=onconfig.whatever ontape -N <database> <load data> ontape -B -s <database> Note that this does a level 0 backup of the data... so you might want to set TAPEDEV to /dev/null first! I found that these two steps greatly increased loading of data into tables.... 3) Are there constraints on the table? - Check clauses & referential integrity checks could also slow down the load, you could zap them, and reapply them later, if you know the data is OK. HTH! James