Migration from transbase to ingres , using copy

[bmwael]

I am migrating a database from transbase to ingres
from transbase I can spool data into csv files, then I manipulate data in csv files
to meet ingres data type compatibility :
for example I transform a date :
'datetime[yy:dd](1993-9-20)' --> 1993.9.20

to insert data in the ingres database I use the copy command,
with the sysntax generated by copydb tool
now copy command is working and there is no problem with data.
but I have speed problems, I am migrating about 4.7 G of data (in csv format)
and it took me more than 14h , and this is still running

Hint: It took less than 1h to spool data from transbase into csv files


Thanks for your help and suggestions

[denjo02]

It would appear as though the load is not using the bulk loader. The fastest way to load data with copy statements is to:

1. Ensure that the tables do not have any secondary indexes - they can however have a structure (BTREE, ISAM, HASH) but no indexes.
2. Ensure that journaling is not enabled
3. Ensure the tables are empty.

If any of these conditions are not met, then each row loaded will also be written to the transaction log file, significantly slowing down the load.

[bmwael]

i am using "set nologging" statement so I don't think the slowing down is due to
transaction log
but I am using indexes, I will retry without indexes, hope it will run faster

thx

[denjo02]

If your tables meet the criteria mentioned above, then the only logging is an entry indicating that a bulk load is being done. No rows will be logged. It's not a good habit to get into, using "set nologging".

BTW, when I saw "empty table" I mean newly created, or modified to truncated. Not simply emptied by deleting rows, which leave the empty pages present in the table.

[bmwael]

thanks for tour help
now copy works fine and in 12 minutes I can import all csv files in the database

[stephenb]

for absolute best performance you want to use an empty heap. To be absolutely certain of this you could truncate all tables and make them all heap using "MODIFY <tablename> TO TRUNCATED".

After loading the data you will need to re-create all primary and secondary indexes on the table.

[kschendel]

And don't forget that primary key and foreign key constraints on a table will generate secondary indexes by default (ie unless you use syntax to tell Ingres not to)!