Optimizing big import in php

Question

Dear SO: I have a simple importer, it goes through each line of a rather big csv and imports it to the database.

My question is: Should I call another method to insert each object (generating a DO and telling it's mapper to insert) or should I hardcode the insert process in the import method, duplicating the code?

I know the elegant thing to do is to call the second method, but I keep hearing in my head that function calls are expensive.

What do you think?

Answer 1

Many RDBMS brands support a special command to do bulk imports. For example:

• MySQL: LOAD DATA INFILE
• PostgreSQL: COPY
• Microsoft SQL Server: BULK INSERT
• Oracle: SQL*Loader

Using these commands is preferred over inserting one row at a time from a CSV data source because the bulk-loading command usually runs at least an order of magnitude faster.

Answer 2

I don't think this matters too much. Consider a bulk insert. At least make sure you're using a transaction, and consider to disable indices before inserting.

Answer 3

It shouldn't matter, as the insertion will take probably orders of magnitude longer than the php code.

As others have stated, bulk insert will give you much more benefit. Those line-level optimizations will only make you blind for the good higher level optimizations.

If you are unsure, do a simple timing with both ways, it shouldn't take longer than a couple of minutes to find out.

Consider combining both approaches to make batch inserts, if all-at-once hits some memory/time/.... limits.