I just have migrated my code from mysql_* To PDO and amused to see the huge performance hit. I have been into reading of http://wooptoo.com/blog/pdo-vs-mysqli-performance-comparison/ and thought PDO will be more efficient.
But i think i may be doing something wrong which making my code much slower than expected.
i have created a singleton class
class db
{
static $dbinstance=null;
public static function getinstance()
{
if (!self::$dbinstance)
{
try
{
self::$dbinstance=new PDO("mysql:host=127.0.0.1;dbname=inventory",'user','pass');
self::$dbinstance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch( PDOException $e)
{
die( '<b>Errors:</b> '.$e->getMessage());
}
}
return self::$dbinstance;
}
Below Code I'm Using to Insert (I Have cut the code short for simplicity)
if(isset($_POST['Build']))
{
$InCart=db::getinstance()->prepare("SELECT `Vendor`, `ItemType`, `ItemCode`, `ItemDesc`, `SerialNo`, `Asset_Code`, `ItmUnit`, `Qty`,`SiteId`, `id`, `Ownership`, `wh`, `Phase`, `Category`, `Issue_Vendor`, `AssetName`, `po`, `FaultInfo`, `willreturn`, `Person`, `remarks`, `grnno`,stockid FROM temptable WHERE id=?");
$insertinSIR=db::getinstance()->prepare("INSERT INTO Sir(SirNo,SiteId,Vendor,Type,ItemDesc,ItemCode,SerialNo,Unit,AssetCode,Qty,Region,Phase,Category,Issue_vendor,AssetName,ownership,Dated,PersonName,Remarks,po,invuser,grnno,WarehouseType,WarehouseLocation) values( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?,?,?,?,?,?,?)");
foreach ($_POST['id'] as $id)
{
$InCart->execute(array($id));
$arr=$InCart->fetch(PDO::FETCH_NUM);
$insertinSIR->execute(array($a,$arr[8],$arr[0],$arr[1],$arr[3],$arr[2],$arr[4],$arr[6],$arr[5],$arr[7],$user,$arr[12],$arr[13],$vendor,$arr[15],$arr[10],$person,$remarks,$arr[16],$invuser,$arr[21],$whtype,$whlocation));
}
I would Appreciate if someone point out the performance hitter?
EDIT
Is it the right way i'm preparing statements and executing in loop?
Proposal:
INSERT INTO Sir (SirNo, ...)
SELECT Vendor, ...
FROM temptable
WHERE id IN (?, ?, ...)
There's no need to pipe all the data through PHP to begin with. See http://dev.mysql.com/doc/refman/5.0/en/insert-select.html.