CODEWITHSUNDEEP
phpmysqlzend-framework2
yasir kk
yasir kk

Reputation: 171

Reduce Execution time of database insertion using PHPxcel

I am trying to insert my excel file worksheet contain 20k+ rows into my database table. I am using Zend Framework(ZF2),PHPExcel and mysql. My problem is while I am trying to insert data to mysql table it takes more than 10 minutes.Everything work perfect, but only problem is with time. Anyone have any idea about how to reduce this execution time. Thanks in advance and Below is my sample code:

for($i=2;$i<=$highestRow;++$i)
            {
                $data = array(
                    'tid' => $posts[$i][0],
                    'PID' => $posts[$i][1],
                    'RNO' => $posts[$i][2], 
                    'BCD' => $posts[$i][3],
                    'ENO' => $posts[$i][4],
                    'ARC' => $posts[$i][5],
                    'LD' => '',
                );
                $insert = $sql->insert('table');
                $insert->values($data);
                $selectString = $sql->getSqlStringForSqlObject($insert);
                $results = $this->adapter->query($selectString, Adapter::QUERY_MODE_EXECUTE);
            }

Upvotes: 2

Views: 282

Answers (3)

timdev
timdev

Reputation: 62894

Building on the two previous answers, I'd approach the issue in the following way:

First I'd be suspicious of PHPExcel. I've never used the library, but I've heard that it can be pretty slow.

So the first thing I'd do is comment out the line that actually writes to the DB and see how long it takes to simply iterate over your input data.

If that alone takes a long time, your ideal solution is to make it the users' problem and demand a plain CSV file.

If accepting an excel file is a hard requirement, you'll have to look to optimize how you're using PHPExcel. See the SO page I linked above as one starting place to try to improve performance.

Next, optimize on the database side per Your Common Sense's second suggestion.

Wrapping all your inserts in a transaction has two advantages. As YCS mentions, this will allow innodb to defer confirming writes until the end. Perhaps more importantly, wrapping the inserts in a single transaction will cause InnoDB to defer updating indices until commit-time. In your current code, InnoDB is updating indicies after each insert.

This is likely to improve things somewhat, but inserting 20k records even with a commit-per-insert should take nowhere near 10 minutes. So attacking things on the DB side is not likely to yield significant improvements until you're worried about seconds instead of minutes.

Upvotes: 1

AlexP
AlexP

Reputation: 9857

If you working with a large data set then you might want to consider a different approach where you do not load the file into memory.

Personally, I would convert to CSV and import the data set in to a temporary database table that has columns that match the file. The LOAD DATA INFILE command is specifically designed to do this and will normally handle extremely large file in seconds.

Once you have the data imported, you can then use all the power of SQL to query it and insert the data where you actually want it.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157888

Most likely it's the issue with InnoDB engine. By default this engine is too cautious and doesn't utilize the disk cache, to make sure that data indeed has been written on disk, before returning you a success message. So you basically have two options.

  1. Most of time you just don't care for the confirmed write. So you can configure mysql by setting this mysql option to zero:

    innodb_flush_log_at_trx_commit = 0

    as long as it's set this way, your InnoDB writes will be almost as fast as MyISAM.

  2. Another option is wrapping all your writes in a single transaction. As it will require only single confirmation from all the writes, it will be reasonable fast too.

Upvotes: 2

Related Questions