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

Comments