Today it was with PDO and memory usage.
When doing large data inserts with transactions, you should
always do your query preparation outside of your loop.
For example:
$datasource->open();
$datasource->beginTransaction();
$p = $datasource->prepare(‘INSERT a SET b = :c’);
while(!feof($line)) {
$p->bindValue(‘:c’, $line);
$p->execute();
}
$datasource->commit();
If the query prepare is moved into the loop, an exorbitant
amount of memory and time is consumed parsing the query every
iteration. For a million inserts, this can result in
exceeding allowed memory usage.
Written by
Zachary Wilson
I enjoy reading & writing about the web and digital marketing. My day-to-day focuses on ensuring our clients have big wins. That begins with extraordinary website design and UX. The real exciting stuff is helping develop strategies to drive traffic (on-site page optimization) and help conversion rate optimization (getting new sales and / or customers). With all my “other” time, I enjoy exploring new adventures with my 3 daughters and wife.