ansaurus

Question

Need Time-Efficient Method of Importing Large CSV File Via PHP Into Multiple MySQL Tables

Answer 1

+3 A:

To efficiently import data in MySQL you have to use LOAD DATA INFILE. It will make a huge difference in performance.

If you need to pre-process your data, do it with the above script, then export back to CSV/TSV and use LOAD DATA queries to finally import into your database.

Your script is not going beyond 500 rows because it is most likely reaching the PHP execution time limit. You can use the set_time_limit() function to give your script no time limit at all, in which case you have to call set_time_limit(0) at the start of your script.

bobdiaes 2010-10-11 17:35:21

Oh no. This was an accidental downvote on my part. After I returned to the page and realized my mistake, it was too late to undo it. Perhaps you could edit your answer so I can undo it or is there another way around this?

webbiedave 2010-10-11 18:09:02

I was a bit perplexed about the downvote, thanks for the comment. I did a minor edit.

bobdiaes 2010-10-11 19:08:41

Thanks. It's now an upvote.

webbiedave 2010-10-11 21:47:22

Something that may work is creating a table called import_leads with all the fields I need, then calling LOAD DATA INFILE and later working off of that copy of the data... However, I think I have found an alternate solution by cleaning up my script a bit and running it through an engine which will load, say, 100 (maybe around 400-500 if I can improve processing of the script) every few minutes. The data just won't be available immediately, but it should be fine.

UltimaHedgie 2010-10-15 19:42:08

Answer 2

A:

Ok, your question is about performance, so I'll start with that, but this code needs refactoring also.

So for performance, you want to easily be able to see where your script is spending time doing things. There is absolutely no point in guessing, you MUST analyze. These are the exact steps you take to optimize:

1) add timing metrics to your code 2) run the code and review the timing metrics 3) optimize algorithm OR code to improve performance

There are 2 basic ways to do this:

1) Use a profiler

2) Add timing metrics to points within your code.

PHPed has a good profiler built in, and you can just run your script in the profiler and it will tell you where you are going slow. It will automatically add timing points at each function call. This is the fastest way to see where your code is slow if you already have it set up (which you should).

But assuming you don't have a profiler set up, you can just add in some timing metric logs. This is as simple as writing a function that will log code location and time, then inserting a call to that func in your code.

So for example, suppose your func looks like this

function logCodePoint($codePointMarker)
{
    static $logHandle;
    if($logHandle == "")
    {
        $logHandle = fopen("/tmp/performance.log", "w");
    }
    fwrite( $logHandle, $codePointMarker . ":" .microtime());
}

you would just insert that in a few places in your code, then start looking to see how long it takes between each code point. Look for the biggest time gap, then isolate to find the longest part of your code.

After you do that and post some metrics here, we can give you further ideas to optimize.

Zak 2010-10-11 17:37:18

Why should he benchmark and optimize his code? It's bulk data loading that will probably only ever happen a couple times. I think explaining how he can profile his code is off-topic here.

bobdiaes 2010-10-11 19:10:23

how will he ever know that's the problem if he has to do it on his own? why shouldn't he do this simple step. It quantitatively gives him a metric he can watch change (and even test against) to ensure his code is meeting a performance requirement.

Zak 2010-10-11 20:44:24

Answer 3

+1 A:

load the raw csv file data into staging (temporary) tables using load data infile method which is nice and fast:

set autocommit = 0;

load data infile..
load data infile..
...

commit;

once the data is loaded run your data cleansing, mapping and validation stored procedures etc:

call cleanse_staging_data();
call map_staging_data();
call validate_staging_data();

once the data has been processed copy the data from the staging tables into correct data tables:

call copy_staging_to_production();

or something like that.

f00 2010-10-11 17:39:07

Answer 4

+2 A:

I have written PHP scripts to bulk-load the data published by Stack Overflow data dump. I import millions of rows and it doesn't take that long.

Here are some tips:

Don't rely on autocommit. The overhead of starting and committing a transaction for every row is enormous. Use explicit transactions, and commit after every 1000 rows (or more).
Use prepared statements. Since you are basically doing the same inserts thousands of times, you can prepare each insert before you start looping, and then execute during the loop, passing values as parameters. I don't know how to do this with CodeIgniter's database library, you'll have to figure it out.
Tune MySQL for import. Increase cache buffers and so on. See Speed of INSERT Statements for more information.
Use LOAD DATA INFILE. If possible. It's literally 20x faster than using INSERT to load data row by row. I understand if you can't because you need to get the last insert id and so on. But in most cases, even if you read the CSV file, rearrange it and write it out to multiple temp CSV files, the data load is still faster than using INSERT.
Do it offline. Don't run long-running tasks during a web request. The time limit of a PHP request will terminate the job, if not today then next Tuesday when the job is 10% longer. Instead, make the web request queue the job, and then return control to the user. You should run the data import as a server process, and periodically allow the user to glimpse the rate of progress. For instance, a cheap way to do this is for your import script to output "." to a temp file, and then the user can request to view the temp file and keep reloading in their browser. If you want to get fancy, do something with Ajax.

Bill Karwin 2010-10-11 17:46:36

Okay, thanks for these tips. I read up on the Speed of INSERT Statements and now I know what you mean by the autocommit...What I think I will do is run it on an engine (this engine is already set up on the system, I just didn't know how to work it) which will load, say, 100 rows every few minutes, and instead of doing an auto-insert every line it'll generate a long query to load all of them in one insert, each time the script loads.If this works better speed-wise, too, I can probably up it to around 400-500 without causing much trouble.I'll work on it more and see how it goes. Thanks!

UltimaHedgie 2010-10-15 19:24:02

Alright, progress update. I've finished updating the script and getting everything re-written, with all bugs fixed. I rewrote the script to not have such a long switch statement (mentioned below), but also to simply grab data and create a series of arrays that holds all the field data to be inserted, and I've also added checks to calculate the Inserted ID (either checking the max id or returning the current row number). After reaching the end of the file, the insert statements are created and run. 1,200 rows takes a few seconds. I'm going to try something longer, but it works MUCH better.

UltimaHedgie 2010-10-17 02:50:19

Answer 5

+1 A:

One other item I HAVE to bring up, is this code NEEDS the following to happen:

currently where you do this :

foreach($row as $num => $val) {
        if(empty($map[$num])) {
            continue;
        }
        $val = str_replace('"', "&#34;", $val);
        $val = str_replace("'", "&#39;", $val);
        switch($map[$num]) {
        // Company Account
        case "company_name":
            $acct['company_name'] = $val;
            break;

You need to change the switch/case to do this instead:

1) create a data map of your mapped fields. The data map should have the correct array that the field maps to, as well as the index of that array. For example:

$dataMap['company_name'] = array($acct, 'company_name');
$dataMap['lead_type']    = array($lead_type, 'name');
.
.
.
$dataMap['bols_per_mo']  = array($acct_cstm, 'approx_bols_per_mo');
.
.
.

And so on

Then 2) Replace your massive switch statement with this simple code snippet:

foreach($row as $num => $val) {
        if(empty($map[$num])) {
            continue;
        }
        $val = str_replace('"', "&#34;", $val);
        $val = str_replace("'", "&#39;", $val);
        $mappingRecord = $dataMap[ $map[$num] ];

        //The first element is the array the data should go in 
        $destinationArray = $mappingRecord[0];

        //the second element is the index of the array it should go in
        $destinationArray[$mappingRecord[1]] = $val;

Zak 2010-10-11 17:52:11

This isn't the full solution to the problem, but this will definitely help out a lot. That's one of the issues I had is writing out that huge switch... This looks much better and runs on fewer lines.

UltimaHedgie 2010-10-15 19:18:31

Answer 6

A:

Are you hitting the PHP script time limit loading your big files?

Try this:

set_time_limit(0);

to disable the default 30-second time limit. There's a server wide limit you can retrieve using max_execution_time(). If the server wide limit isn't long enough for this bulk load job, you'll need to figure out how to get your local server wrangler to change it or do your upload some other way.

Ollie Jones 2010-10-11 20:16:17

Answer 7

A:

I had to do something similar on occasion. There are a couple problems you're potentially running into:

PHP script timeout – After a pre-defined period of time PHP will automatically kill the process. You can disable this in the php.ini file or with set_time_limit(0). Some hosts disable this method, though, and some hosts have separate proc watches setup to kill processes that have run for a set period of time to keep the server from being taken down.
Memory limit – PHP will let you set a maximum memory limit in that same php.ini. If it hits it it'll trigger a fatal error and die. You can see this in the error log, but nothing will be output to the browser.
MySQL Query Overhead – As others have pointed out, there's a lot of overhead to each individual query. I didn't have enough rows to justify throwing load data infile into the storm. I didn't need to get results for each individual query, either, so I just put them all in a single call and fired it off to a single mysql_query (which you can do from your controller in CodeIgniter like so: mysql_query($sql, $this->db->conn_id); since it'll throw a fit if you pass that to DB::query).

Bill Karwin pointed out a lot of good ways to optimize when you're dealing with very large data sets, but if you're having trouble at ~400 rows I don't think it'll do you much good just yet. Check your error logs, get the problem fixed, and then work on optimizing it.

coreyward 2010-10-11 21:03:05

ansaurus

tags:

views:

answers:

Need Time-Efficient Method of Importing Large CSV File Via PHP Into Multiple MySQL Tables

related questions