Re: Batch Loading of data through DBI

Most databases have dedicated tools that do bulk loading of files. You use sybase (I think), and for sybase you would use bcp. (search sybase.com and, even better: sybooks)

MySQL has mysqlimport that does the same thing.

Oracle's bulk loader is sqlldr.

All these tools are typically much faster than DBI.

If you are trying to make your DBI data loading faster, make sure AutoCommit is off and that you are not committing too often (only every few hundred inserts).

It will also help to remove indexes and recreate them after the load.

Comment on Re: Batch Loading of data through DBI

Replies are listed 'Best First'.
Re^2: Batch Loading of data through DBI by DrHyde (Prior) on Jan 21, 2005 at 10:54 UTC
Another way to make an import go faster is - if your database allows it - to temporarily turn off logging.	[reply]
Re^3: Batch Loading of data through DBI by thor (Priest) on Jan 21, 2005 at 23:12 UTC
Another (safer) way is to drop all but the clustered index. When you're done, re-create all the other indicies. In most situations though, it's not worth the extra overhead. thor `Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come`	[reply]
Re^3: Batch Loading of data through DBI by jfroebe (Parson) on Jan 21, 2005 at 21:06 UTC
Turning off logging should only be done on initial loads of the database. Turning off logging runs the risk of corruption if something should go wrong during the bulk copy of the data. IMHO, the slight performance improvement of loading the data is not worth the risk. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]