Improving bulk loading speed

> aa1@unixfe.rl.ac.uk (Mr A Akkas) writes:
> 
> I got a table with btree storage structure and contained > 250,000 rows.
> Twice a day about > 1500 rows are get inserted into this table using COPY
> command and takes about 1 hour to load. Surely this is not right. Can someone
> please suggests me how to improve this? I am thinking to modify the table to
> heap before doing data loading. But when I tried to change it to heap it took
> about 20 mins.
> 
> Thanks in advance for any help.
> 
> Regards,
> Ali Akkas

Ali,

Has the table got (m)any secondary indices ? If so, every insert into the 
table is also causing one into each index. You may find it is more 
performance to drop the indices, copy the data in, and then recreate.

Also try sorting the data into the btree order.

Finally, you will be incurring logging overhead. If this job is performed 
at a suitable time (say following a checkpoint) you may consider switching 
off logging.
-- 
Graham Parsons
Principal Consultant
Forest Consulting Limited


From: "Jones, Gerard" (GJones@aylesburyvaledc.gov.uk)
Subject: Improving bulk loading speed

If it takes Ali an hour to load data into a BTREE using copy and 20 minutes
to modify to HEAP surely the  modify provides the  best option as a load of
1500 records into a HEAP should be negligible (Ingres should use fast load)
then when modifying back to BTREE again he is restructuring his BTREE index
which will improve table performance. 
Another consideration, if modifying is acceptable, would be to look at
changing the structure to ISAM, this of course depends on the overall usage
of the table. 

Gerard E. Jones   -  ( AVDC I.T. Division  x5302 )
Ingres Q & A
To William's Home Page
Email William