0

I'm using Mysql and PHP with PDO and all my tables are InnoDB. Does beginTransaction improve performance of multiple UPDATE?

For example, is the first example below better than second in terms of performance?

First example:

<?php
$dbh = new PDO($dsn, $user, $password);
$dbh->beginTransaction();
$sql = 'UPDATE test_commit SET ad=:ad WHERE id=1';
$sth = $dbh->prepare($sql);
$sth->execute(array(':ad' => 10));
$sql = 'UPDATE test_commit SET ad=:ad WHERE id=2';
$sth = $dbh->prepare($sql);
$sth->execute(array(':ad' => 11));
$dbh->commit();

Second example:

<?php
$dbh = new PDO($dsn, $user, $password);
$sql = 'UPDATE test_commit SET ad=:ad WHERE id=1';
$sth = $dbh->prepare($sql);
$sth->execute(array(':ad' => 10));
$sql = 'UPDATE test_commit SET ad=:ad WHERE id=2';
$sth = $dbh->prepare($sql);
$sth->execute(array(':ad' => 11));

The second question is, if instead of two update I had made two inserts in the two examples, what would be in terms of performance the better example?

Joy
  • 267
  • 1
  • 4
  • 13
  • You could as well try and run these updates in a single query: http://stackoverflow.com/a/84111/2298301. As far as default processing for PDO is concerned, connection is closed after each `execute()` so starting or stopping a transaction manually won't specifically add up to any huge advantages, except if you ever wanted a chance to rollback the updates made during the course of the program. – Dhruv Saxena Feb 09 '17 at 20:15
  • @DhruvSaxena may be you can explain as well how it's possible to rollback a transaction after closing a connection? – Your Common Sense Feb 09 '17 at 20:21
  • @YourCommonSense Sorry, I may not have articulated the comment well. A roll back was possible only if a transaction was started manually using `beginTransaction()` and not otherwise. – Dhruv Saxena Feb 09 '17 at 20:25
  • @DhruvSaxena I didn't ask you how to start a transaction. I asked you how it;s possible to **rollback** a transaction after closing a connection, as, according to your comment, connection will be closed after execute. – Your Common Sense Feb 09 '17 at 20:28
  • @YourCommonSense - I apologise. I realise the error made in the comment. I'd misunderstood the scope / life of a connection before... (I mistook it to be limited to an execute statement as opposed to the end of the script). I therefore had a notion that adding `beginTransaction()` explicitly would prevent closing the connection after the `execute()` unless a `close()`, `commit()` or `rollback()` command was issued. I stand corrected. Apologies. – Dhruv Saxena Feb 09 '17 at 20:44
  • Since no proper answer was provided - short answer is **yes**, using a transaction improves performance when issuing multiple *write* queries. You don't need to prepare the statement more than once though. Simply prepare it once and execute multiple times. The reason why using a transaction helps make updates or inserts faster is because HDD's bandwith is utilized in an optimal way, and only 1 I/O is being spent for the write. When you don't do this, multiple I/O's are spent, 1 per query. You don't have many I/O's. However, transaction comes with a tradeoff so use it wisely. – N.B. Feb 09 '17 at 23:08
  • @N.B. write queries means UPDATE and INSERT? Also, my first example is the right way? – Joy Feb 10 '17 at 08:31

0 Answers0