|
Re: Batch Loading of data through DBI
by erix (Prior) on Jan 20, 2005 at 21:40 UTC
|
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.
| [reply] |
|
|
Another way to make an import go faster is - if your database allows it - to temporarily turn off logging.
| [reply] |
|
|
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] |
|
|
| [reply] |
Re: Batch Loading of data through DBI
by moot (Chaplain) on Jan 20, 2005 at 21:41 UTC
|
Do you mean processing more than one line per SQL statement? or are you talking about transactions?
If the former, you could think about loading N lines into a buffer, then passing the buffer to $dbh->do(), but results will depend on the actual driver you are using (i.e. which DBMS).
If the latter, transaction availability is again dependent on the DBMS you are using.
You could also investigate the use of pre-prepared statement handles, depending on the format of data within the file - if you have the values themselves (as opposed to complete INSERT or UPDATE statements), pre-preparing your statement handles will save some time.
Outside perl, some database engines (MySQL for one) will allow use of 'extended' SQL, where multiple entries can be added using one INSERT statement.
Perhaps you could clarify your question. | [reply] |
Re: Batch Loading of data through DBI
by mkirank (Chaplain) on Jan 21, 2005 at 10:23 UTC
|
| [reply] |
|
|
| [reply] |
Re: Batch Loading of data through DBI
by CountZero (Bishop) on Jan 21, 2005 at 07:31 UTC
|
In MySQL there is the LOAD DATA INFILE function to do what you want and it can be called by the usual DBI-syntax.
CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law
| [reply]
[d/l] |
Re: Batch Loading of data through DBI
by samgold (Scribe) on Jan 21, 2005 at 20:26 UTC
|
As I found out SQL*Loader, Oracle's tool, is much much faster than using DBI. You can check out Best method to load a csv file. Your best bet is to use the tools that your Database provides. In my case I used sqlldr and a PL/SQL procedure to load my data. It takes less than a minute to load the data into a temp table and then run the procedure to load it into the production table.
HTH,
Sam Gold | [reply] |
Re: Batch Loading of data through DBI
by Anonymous Monk on Jan 21, 2005 at 16:51 UTC
|
As mentioned earlier, transactions allow savings in file access time usually (especially in the uber-leet SQLite driver). Another thing to try is to do $dbh->prepare() on the insert query with param placeholders. Most drivers will then cache the compiled state of the query, cutting out a chunk of used time later on. | [reply] |
Re: Batch Loading of data through DBI
by zebedee (Pilgrim) on Jan 23, 2005 at 18:36 UTC
|
mysqldump generates the following ...
LOCK TABLES makes WRITE;
INSERT INTO makes VALUES (1,'Acura'),(2,'Alfa Romeo'),(3,'Aston Martin'),(4,'Audi'),...(83,'Lifan');
UNLOCK TABLES;
I do not know if the INSERT INTO syntax is MySQL-specific or not. (The "..." is where I cut out a whole list of items.) | [reply] |
Re: Batch Loading of data through DBI
by naChoZ (Curate) on Jan 24, 2005 at 01:12 UTC
|
| [reply] |
