PHP mySQL performance issues on large series of insert statements, should I use something else?

Question

I have a script that compiles a list of items that can be very long - just compiling this list can take 10-15 minutes, but that is acceptable. When I incorporate a function that iterates through the list and inserts them all into a mySQL table, that time is increased about about 50%. I was wondering if there was a faster way of serializing this data? Should i explore CSV or something else? Or can I optimize my code to do this faster:

    private function toDB(){
    $sql[] = "DROP TABLE IF EXISTS checklisttest";$sql[] = "CREATE TABLE checklisttest (
  Incident varchar(12) NOT NULL,
  TestID mediumint(9) NOT NULL AUTO_INCREMENT,
  Element varchar(12) NOT NULL,
  Name varchar(128) NOT NULL,
  Code varchar(512) NOT NULL,
  Expected varchar(512) NOT NULL,
  Actual varchar(512) NOT NULL,
  AutoVerifyResult varchar(32) NOT NULL,
  QAResult varchar(32) DEFAULT NULL,
  Comments text,
  PRIMARY KEY (TestID)
)";

    //iterate through the records $this->records[10001] -- There can be anywhere from 100 - 300 records
    foreach($this->records as $inc => $record){
        //iterate through the element ids $this->records[10001][E02_04]
        foreach($this->records[$inc]["Elements"] as $elementID => $element){
            //iterate through the element ids $this->records[10001][E02_04][1] --There can be anywhere from 150 - 350 elements per record.
            foreach($element as $key => $val){
                $sql[] = "
INSERT INTO `checklistTest` VALUES (\"$inc\",NULL,\"$elementID\",\"$val[name]\",\"$val[code]\",\"$val[expected]\",\"$val[actual]\",\"$val[match]\",\"$val[QAResult]\",NULL)";
            }
        }
    }
    foreach($sql as $key => $val){
        mysql_select_db("new",$GLOBALS['local']);
        mysql_query($val,$GLOBALS['local']) or die(mysql_error());
    }
    //echo "<textarea style='width:100%;height:400px'>$sql</textarea>";
    //mysql_select_db("new",$GLOBALS['local']);
    //mysql_query($sql,$GLOBALS['local']) or die(mysql_error());
}

There must be a better way to go about doing this, I just don't have much experience performing a lot of queries like this - usually they are just one-and-done for me. Thanks for the help.

thanks for the answers, I posted my solution in a comment to the accepted answer.

There are a lot of factors to take into account when doing inserts of a large dataset - such as the size of the dataset, engine of mysql table, mysql configuration, php configuration, load of the server at the time you ran the script and so on. If you take a look on what you did, you duplicate already large dataset by the factor of 2 which makes php spend additional memory, not to mention that you iterate 2x over the same data. Add mysql overhead when inserting and you won't get results that you want. — Furicane, Mar 21 '11 at 15:46
You can get some tips from this question: http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql — nikhil500, Mar 21 '11 at 15:52
@Pentium: Does wrapping a slow query series in a transaction make it magically faster? — Marc B, Mar 21 '11 at 17:01
Bundling together numerous writes to a table inside a transaction allows the data to be written together rather than piecemeal. This can be a *huge* performance gain. — Charles, Mar 21 '11 at 17:07
Thanks for the advice I got it sorted, I'm still a noob in my ways when it comes to mysql, so it just took a different way of thinking about it. — jreed121, Mar 21 '11 at 18:33

score 3 · Answer 1 · answered Mar 21 '11 at 17:34

There are a whole bunch of factors that impact performance, including server hardware, average load, MySQL settings, memory use, etc. I'm going to blindly assume that you have an I/O bottleneck and that MySQL is properly configured for the load you're putting on it.

Let's use a prepared statement and a transaction. I'll be using PDO in this example, but you could use mysqli if you want. Just stop using the old and busted mysql extension.

$pdo->beginTransaction();
$statement = $pdo->prepare('
    INSERT INTO checklistTest
           VALUES(?, NULL, ?, ?, ?, ?, ?, ?, ?, NULL)
');
foreach($this->records as $inc => $record){
    foreach($this->records[$inc]["Elements"] as $elementID => $element){
        foreach($element as $key => $val) {
            $statement->execute(array(
                $inc,
                $elementID,
                $val['name'],
                $val['code'],
                $val['expected'],
                $val['actual'],
                $val['match'],
                $val['QAResult']
            ));
        }
    }
}
$pdo->commit();

So, what's happening here? First, we're starting a transaction. We're telling the database that we're about to do a bunch of work, and we either want it all done, or none of it.

Second, we're preparing a SQL statement. See those question marks? Those are called placeholders. We'll later tell the database to fill in specific data at each placeholder. Also note that there are no quotation marks. Those are basically added automatically when the placeholders get filled in.

Inside the loop, we're telling the statement to execute, and we're using PDO's execute method to pass in an array of values for the placeholders. Some people prefer doing this one variable at time using bindParam, but I prefer the array method.

Prepared statements repeated in a loop can be faster than unprepared statements, though the difference won't really be noticeable unless you're running tens of thousands of queries, which is kind of sounds like you are.

Finally, once the loop is finished, we tell the database to commit the work we've just done. As I mentioned in the comments, that's where a big performance boost is possible. The database will only actually permanently write changes to disk when you perform the commit. This means that normal book-keeping tasks can wait until the commit happens, instead of needing to happen on every single insert. This way, the bulk of the I/O you need doesn't need to happen live, as you run the insert.

There's one more change that you'd need to make if you use this technique. For years, MySQL has been configured to not create transaction-safe tables by default. This means that we need to actually change your CREATE TABLE statement a bit:

CREATE TABLE checklistTest (
   ... // No changes inside
) ENGINE=InnoDB

The only difference is there at the end, after the close-paren. We're asking MySQL to use the InnoDB storage engine instead of whatever the server default is. This guarantees that we'll get a table that supports transactions.

Now, I realize that asking you to change database adapters might be a bit silly, but it's not without reason. While you can perform transactions using the oldschool mysql interface (by issuing START TRANSACTION and COMMIT/ROLLBACK commands yourself), you can't use prepared statements with it. While that alone isn't a deal-breaker, the prepare-bind-execute process is one that every modern PHP database adapter follows. The old mysql interface is not a modern PHP database adapter, and you really should give some serious consideration to switching to PDO or mysqli.

One more performance factor is actually how you gather the data you're about to write. While this answer focuses on making sure that the database itself is as small of a bottleneck as practical, it could be that your performance problem is earlier in the process. Can you tell us where this data comes from, and how you're building it? You should seriously consider profiling your code, which will reveal the real performance problem. It could be that the database bits are already lightning-quick and the problem is somewhere else entirely.

Thanks for the suggestion, I decided to try Alpesh's solution first since it seemed simpler and that instantly improved the performance so I'll stick with that for now. — jreed121, Mar 21 '11 at 18:35

score 1 · Accepted Answer · edited Nov 15 '12 at 10:41

Insert of firing multiple insert statements you can combine them in to one query as below -

//iterate through the records $this->records[10001] -- There can be anywhere from 100 - 300 records

$sql = "INSERT INTO checklistTest VALUES ";

    foreach($this->records as $inc => $record){
        //iterate through the element ids $this->records[10001][E02_04]

        foreach($this->records[$inc]["Elements"] as $elementID => $element){

            //iterate through the element ids $this->records[10001][E02_04][1]--There can be anywhere from 150 - 350 elements per record.
            foreach($element as $key => $val){
                $sql.= "(\"$inc\",NULL,\"$elementID\",\"$val[name]\",\"$val[code]\",\"$val[expected]\",\"$val[actual]\",\"$val[match]\",\"$val[QAResult]\",NULL),";
            }
        }
    }

Note: Now here remove the last comma. ie for the last value in array too a comma will be appended at the end do remove that else you will get database error.

Now because of above merging of insert queries you have to query your database only once which improves your query perfomance a lot.

mysql_select_db("new",$GLOBALS['local']);

mysql_query($sql,$GLOBALS['local']) or die(mysql_error());

//echo "<textarea style='width:100%;height:400px'>$sql</textarea>";
//mysql_select_db("new",$GLOBALS['local']);
//mysql_query($sql,$GLOBALS['local']) or die(mysql_error());

}

Your can refer below links for detailed documentation of this method -

http://www.brainbell.com/tutorials/MySQL/Inserting_Multiple_Rows.htm

http://dev.mysql.com/doc/refman/5.5/en/insert.html

This can work, until you add so many field sets that you exceed the max_allowed_packet size and the whole thing blows up. — Marc B, Mar 21 '11 at 17:02
I'll take that into consideration when I start pushing more values through. This suggestion worked for me though, instantly cut the time down to about 1/4 of the original time. I was told that i couldn't do this in a previous answer to a similar question i asked. Note: The VALUES needs to be added to the string before the loop, otherwise you have a bunch of VALUES in the statement. Thanks! — jreed121, Mar 21 '11 at 18:39

PHP mySQL performance issues on large series of insert statements, should I use something else?

2 Answers2