Sorry for the delay. Informix is right up my street, so I can be more specific.

Disabling the transaction logging would mean that the inserts are not logged in the logical logs. If something went wrong you could not roll back your transactions. Transaction logging is an overhead you could do without, but by not using it there would be a risk. If your system is being used during the load it will not be an option. To switch transaction logging, use ontape. Use ontape to make a backup of the database before you do anything. It might actually be quicker to restore from a level 0 rather than trying to fix a botched, half done, load with your volumes.

Your indicies are definitely slowing things down, but without complete control of the table, there is not much you could do about transaction logging or dropping the indicies. Consider, the more records you insert, the larger the indicies become and the overhead becomes greater. In terms of the optimiser, it will progressively become more and more expensive to insert a row into your table. Your initial calculation of 16 hours may become even worse as the load continues.

Is it possible to stop the querying process? Is there a time during the 24hr period when the table is not used and can be loaded to?

Since you have posted in this forum, and not the scripting forum, I will assume you are using ESQL/C.

You could write a process that would:

1. Drop the indicies.
2. Load some or all of the data to the table.
3. Re-create the indicies
4. Update statistics for the table

When you write the process make sure that the insert statement is prepared
e.g.
sprintf(sqlvar, "insert into mytable values (?, ?, ?, %ld, ?)", num_var);

$prepare p_ins for $lv_sql;

Then, later in program, whenever a row is required to be inserted:

$execute p_ins using $var1, $var2, $var3, $var4;

This will help your inserts go a little quicker. The fastest way to perform inserts programatically is with using insert cursors.

$declare c_ins cursor for p_ins;
$open c_ins;
$put c_ins using $var1, $var2, $var3, $var4;
$close c_ins;

Read up some more on this, but your real problem is getting rid of the indicies on the table. They really, really do slow things up. Also consider that if you have other demands on your Informix system during the load, it will have a detrimental effect on your load's performance. Maybe a quiet time would be beneficial. You can always run a program you have written from cron.

Generally the principle of multi-threading or using multiple processes is good, but not too many at once. From my own experience, there will come a point that running more load processes concurrently will slow things down rather than speed them up.

As a digression, which might yet apply to your loading process, have you considered the impact of the queries on a table which is 5+ million rows? Are your indicies all working? Do you have any sequential scans?

You may investigate the optimiser by using the sql command sqlexplain. Also the sysmaster database has tables which hold the costs on sql statements run against the database. You should certainly investigate high cost sql statements and how they may be affecting your overall system performance.

Well hopefully I have given you plenty to be getting on with!

MBB