Saya memiliki batch job yang menggunakan Doctrine, tetapi berjalan lambat sekali!, Mengapa ?

Hal ini terjadi karena terjadinya memory leak, seperti dilaporkan oleh banyak orang misalnya [1], [2]. Tapi

solusinya ada, yaitu dengan menggunakan Doctrine_Core::HYDRATE_ON_DEMAND ketika mem-fetch data dan jangan lupa memanggil ->free(true) dan meng-unset object setelah object tsb tidak digunakan.

UPDATE :

Selain itu, ada beberapa hal cara optimalisasi lagi, yaitu dengan menyimpan beberapa row dalam 1 kali eksekusi, untuk lebih jelas tentang bagaimana caranya bisa membaca Doctrine - insert multiple rows with just one save() [3]. Untuk aspek-aspek yang mempengaruhio proses insert, terutama di MySQL, bisa membaca [4].

Berikut adalah contoh symfony task beserta snippet fungsi yang dipanggil oleh ini, tetapi task ini sudah tidak mengalami masalah memory leak lagi, alhamdulillah.

<?php

/**

*

* @package lib.task

* @author Wildan Maulana, OpenThink Labs

*/

class tirtaerpIssue1Task extends sfBaseTask

{

public function configure()

{

$this->namespace = 'tirtaerp';

$this->name = 'issue1';

$this->briefDescription = 'Proses update id serah terima amdk agar mengandung kode depot';

$this->detailedDescription = "

Task [tirtaerp:issue1|INFO] akan mengubah id serah terima amdk agar

mengandung kode depot agar dapat dilakukan ekpsor-import antara cabang

dan kantor pusat (lihat : https://bitbucket.org/wildanm/tirta-erp/issue/1/perubahan-id-serah-terima)

[./symfony tirtaerp:issue1|INFO]

" ;

$this->addOptions(array(

new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'Koneksi database yang ingin digunakan', 'doctrine_batch'),

));

}

public function execute($arguments = array(), $options = array())

{

sfConfig::set('sf_debug', false);

$databaseManager = new sfDatabaseManager($this->configuration);

$connection = $databaseManager->getDatabase(isset($options['connection']) ? $options['connection'] : null)->getConnection();

$connection->setAttribute(Doctrine_Core::ATTR_AUTO_FREE_QUERY_OBJECTS, true );

$this->logSection('tirtartp-issue1', "Proses perubahan serah_terima_amdk_id dimulai");

SerahTerimaAmdkTable::solveIssue1() ;

$this->logSection('tirtartp-issue1', "Proses perubahan serah_terima_amdk_id selesai");

}

}

Snippet fungsi :

/**

*

* @description

* cek digitnya ada berapa, kalau sudah ada 15, maka skip

* kalau kurang dari 15, maka tambah digit 0 didepan-nya sampai

* total digit berjumlah 12, dan kemudian ditambah 3 digit kode depot didepannya

*/

public static function solveIssue1() {

$kode_depot = "U10" ; //hanya ada ada kode depot ini ketika proses perubahan id dilakukan

//disable FK Checks

$q = Doctrine_Manager::getInstance()->getCurrentConnection();

$result = $q->execute("SET FOREIGN_KEY_CHECKS=0");

//tabel serah_terima_amdk

$q = Doctrine_Query::create()

->from("SerahTerimaAmdk") ;

$result = $q->execute(array(),Doctrine_Core::HYDRATE_ON_DEMAND) ;

foreach($result as $res) {

$id = $res["id"] ;

$length = strlen($id) ;

$prefix = "" ;

if($length >= 15) {

echo "serah_terima_amdk.id ". $res["id"]. " melebihi jumlah 15\n" ; ;

} else {

$minus = 15 - $length ;

for($i=$length;$i<12;$i++) {

$prefix .= "0" ;

}

echo "Proses $id \n" ;

$final_id = $kode_depot.$prefix.$id ;

$q = Doctrine_Query::create()

->update("SerahTerimaAmdk")

->set("id","?",$final_id)

->where("id = ?", $id) ;

$obj = $q->execute();

unset($obj);

$res->free(true);

}

}

unset($q);

echo "Tranformasi id table serah_terima_amdk_resume mulai ...\n" ;

//tabel serah_terima_amdk_resume

$q = Doctrine_Query::create()

->from("SerahTerimaAmdkResume") ;

$result = $q->execute(array(),Doctrine_Core::HYDRATE_ON_DEMAND);

foreach($result as $res) {

$id = $res["serah_terima_amdk_id"] ;

$length = strlen($id) ;

$prefix = "" ;

if($length >= 15) {

echo "serah_terima_amdk_resume.serah_terima_amdk_id ". $res['serah_terima_amdk_id']. " melebihi jumlah 15\n" ;

} else {

$minus = 15 - $length ;

for($i=$length;$i<12;$i++) {

$prefix .= "0" ;

}

$final_id = $kode_depot.$prefix.$id ;

echo "Proses $id \n" ;

$q = Doctrine_Query::create()

->update("SerahTerimaAmdkResume")

->set("serah_terima_amdk_id","?",$final_id)

->where("serah_terima_amdk_id = ?", $id) ;

$obj = $q->execute();

unset($obj);

$res->free(true);

}

}

unset($q);

echo "Tranformasi id table serah_terima_amdk_detail mulai ...\n" ;

//tabel serah_terima_amdk_detail

$q = Doctrine_Query::create()

->from("SerahTerimaAmdkDetail") ;

$result = $q->execute(array(),Doctrine_Core::HYDRATE_ON_DEMAND);

foreach($result as $res) {

$id = $res["serah_terima_amdk_id"] ;

$length = strlen($id) ;

$prefix = "" ;

if($length >= 15) {

echo "serah_terima_amdk_resume.serah_terima_amdk_id ". $res["serah_terima_amdk_id"]. " melebihi jumlah 15\n";

} else {

$minus = 15 - $length ;

for($i=$length;$i<12;$i++) {

$prefix .= "0" ;

}

$final_id = $kode_depot.$prefix.$id ;

echo "Proses $id \n" ;

$q = Doctrine_Query::create()

->update("SerahTerimaAmdkDetail")

->set("serah_terima_amdk_id","?",$final_id)

->where("serah_terima_amdk_id = ?", $id) ;

$obj = $q->execute();

unset($obj);

$res->free(true);

}

}

unset($q);

echo "Tranformasi id table st_tujuan_agen mulai ...\n" ;

//tabel st_tujuan_agen

$q = Doctrine_Query::create()

->from("StTujuanAgen") ;

$result = $q->execute(array(),Doctrine_Core::HYDRATE_ON_DEMAND);

foreach($result as $res) {

$id = $res["serah_terima_amdk_id"] ;

$length = strlen($id) ;

$prefix = "" ;

if($length >= 15) {

echo "st_tujuan_agen.serah_terima_amdk_id ". $res["serah_terima_amdk_id"] ." melebihi jumlah 15\n";

} else {

$minus = 15 - $length ;

for($i=$length;$i<12;$i++) {

$prefix .= "0" ;

}

$final_id = $kode_depot.$prefix.$id ;

echo "Proses id $id \n" ;

$q = Doctrine_Query::create()

->update("StTujuanAgen")

->set("serah_terima_amdk_id","?",$final_id)

->where("serah_terima_amdk_id = ?", $id) ;

$obj = $q->execute();

unset($obj);

$res->free(true);

}

}

unset($q);

echo "Tranformasi id table kenek_serah_terima mulai ...\n" ;

//tabel kenek_serah_terima

$q = Doctrine_Query::create()

->from("KenekSerahTerima") ;

$result = $q->execute(array(),Doctrine_Core::HYDRATE_ON_DEMAND);

foreach($result as $res) {

$id = $res["serah_terima_amdk_id"] ;

$length = strlen($id) ;

$prefix = "" ;

if($length >= 15) {

echo "kenek_serah_terima.serah_terima_amdk_id ". $res["serah_terima_amdk_id"] ." melebihi jumlah 15\n";

} else {

$minus = 15 - $length ;

for($i=$length;$i<12;$i++) {

$prefix .= "0" ;

}

$final_id = $kode_depot.$prefix.$id ;

echo "Proses id $id \n" ;

$q = Doctrine_Query::create()

->update("KenekSerahTerima")

->set("serah_terima_amdk_id","?",$final_id)

->where("serah_terima_amdk_id = ?", $id) ;

$obj = $q->execute();

unset($obj);

$res->free(true);

}

}

unset($q);

//enable FK Checks

$q = Doctrine_Manager::getInstance()->getCurrentConnection();

$result = $q->execute("SET FOREIGN_KEY_CHECKS=1");

}

Referensi

• 1. Doctrine_Query (execute / fetchOne) memory leak, http://www.doctrine-project.org/jira/browse/DC-904

  2. php/symfony/doctrine memory leak?, http://stackoverflow.com/questions/2097744/php-symfony-doctrine-memory-leak

  3. Doctrine - insert multiple rows with just one save(), http://stackoverflow.com/questions/5415725/doctrine-insert-multiple-rows-with-just-one-save

  4. Speed of INSERT Statements, http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html