Multiple Inserts with Prepared Statements in Symfony 1.4

While working on a Symfony1.4 project I needed to create a function that would insert 5,000 records into a database. On previous Symfony1.0 projects I would typically create a custom query and manually create the SQL statement.

I just recently started working with Symfony1.4 and found that it uses PDO (“PHP Data Objects“).   It is my understanding as well as experience thus far that I couldn’t create a custom query that would contain all records for insertion in one SQL statement.

What I had to do was create a prepared statement. Here was the basic set up with Propel:

$connection = Propel::getConnection();

$sql = 'INSERT INTO table_1 (name, date) VALUES (:name, :date)';

$statement = $connection->prepare($sql);

foreach ($my_array as $key => $value) { $statement->bindValue(':name', $value['name']'); $statement->bindValue(':date', $value['date']'); $result = $statement->execute(); }

This was faster than using a doInsert and then inserting via creation of an object and using ->save(). I also found that the above setup was able to insert all 5,000 records without timing out whereas using doInsert() only inserted ~4,600 records until it timed out.

Creating an object and using ->save() in a loop was the slowest method. I was able to insert ~3,700 records before it timed out.  I tested this out several times and was able to replicate the results.

So to summarize the speed of inserting records (from fastest to slowest):

1. Prepared statements

2. doInsert()

3. $object->save()

Benefits of a Prepared Statement

The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize it’s plan for executing the query.

For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using prepared statements in Symfony the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.

Additionally, the parameters to prepared statements don’t need to be quoted; the driver automatically handles it. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Endertech is a Los Angeles Software Development Company able to provide solutions for your Database Applications and other Web Development needs. Contact us for a free consultation.

Written by Senior Developer Rolando Barona with minor editing by Digital Media Coordinator Casey.