Codeigniter: Benchmarking record count.

 Menggunakan database SQL Server 2012, dengan jumlah record diatas 1juta. Juga ditambah dengan menggunakan MemoryCache untuk process yang nomor 4.

function get_test() {
$this->benchmark->mark('one');
$this->db->select('*')->from('big_data')->order_by('id', 'asc');
$num_row1 = $this->db->get()->num_rows();
$this->benchmark->mark('two');
$this->benchmark->mark('three');
$this->db->flush_cache();
$this->db->select('COUNT(*) AS rec_count', FALSE)->from('big_data');
$num_row2 = $this->db->get()->row()->rec_count;
$this->benchmark->mark('four');
$this->benchmark->mark('five');
$this->db->flush_cache();
$num_row3 = $this->db->query("select count(*) as rec_count from big_data")->row()->rec_count;
$this->benchmark->mark('six');
$this->benchmark->mark('seven');
$num_row4 = $this->cache->memcached->get('num_row4');
if ( !$num_row4 ) {
$this->db->flush_cache();
$this->db->select('COUNT(*) AS rec_count', FALSE)->from('big_data');
$num_row4 = $this->db->get()->row()->rec_count;
$this->cache->memcached->save('num_row4', $num_row4, 60);
}
$this->benchmark->mark('eight');
$this->benchmark->mark('nine');
$this->db->flush_cache();
$num_row5 = $this->db->get('big_data')->num_rows;
$this->benchmark->mark('ten');
echo "PROCESS #1 : ".$this->benchmark->elapsed_time('one', 'two')."<br />";
echo "REC. COUNT #1 : $num_row1"."<br />";
echo "PROCESS #2 : ".$this->benchmark->elapsed_time('three', 'four')."<br />";
echo "REC. COUNT #2 : $num_row2"."<br />";
echo "PROCESS #3 : ".$this->benchmark->elapsed_time('five', 'six')."<br />";
echo "REC. COUNT #3 : $num_row3"."<br />";
echo "PROCESS #4 : ".$this->benchmark->elapsed_time('seven', 'eight')."<br />";
echo "REC. COUNT #4 : $num_row4"."<br />";
echo "PROCESS #5 : ".$this->benchmark->elapsed_time('nine', 'ten')."<br />";
echo "REC. COUNT #5 : $num_row5"."<br />"; }

RESULT #1
====================
PROCESS #1 : 8.0914
REC. COUNT #1 : 1077960
PROCESS #2 : 0.2156
REC. COUNT #2 : 1077960
PROCESS #3 : 0.1757
REC. COUNT #3 : 1077960
PROCESS #4 : 0.1782
REC. COUNT #4 : 1077960
PROCESS #5 : 7.8618
REC. COUNT #5 : 1077960

RESULT #2
====================
PROCESS #1 : 8.1435
REC. COUNT #1 : 1077960
PROCESS #2 : 0.2062
REC. COUNT #2 : 1077960
PROCESS #3 : 0.1807
REC. COUNT #3 : 1077960
PROCESS #4 : 0.0018
REC. COUNT #4 : 1077960
PROCESS #5 : 8.1332
REC. COUNT #5 : 1077960

Result #1, data di memorycache belum tersimpan (PROCESS #4 : 0.1782).
Result #2, data di memorycache sudah tersimpan (PROCESS #4 : 0.0018).

WOW.....!!!

Codeigniter: Active Record, or_like and brackets

Fix untuk "DB_active_rec.php".

Edit function "protected function _like", tambahkan yang saya beri color merah.

if ( ! is_array($field)) 

{ 

$field = array($field => $match); 

} 

$prefix = '';

Kemudian, masih di function yang sama dengan yang diatas, pindahkan yang saya beri color biru ke bagian bawah, masih didalam foreach. Lihat line yang saya beri color merah.

$prefix = (count($this->ar_like) == 0) ? '' : $type;

$v = $this->escape_like_str($v); 

if ($side == 'none') 

{ 

  $like_statement = $prefix." $k $not LIKE '{$v}'"; 

}

BECOME

$this->ar_like[] = $like_statement; 

if ($this->ar_caching === TRUE) 

{   

  $this->ar_cache_like[] = $like_statement; 

  $this->ar_cache_exists[] = 'like'; 

} 

$prefix = (count($this->ar_like) == 0) ? '' : $type;

Edit file "/system/database/DB_active_rec.php", tambah function dibawah ini :

var $ar_bracket_open = FALSE; 

var $last_bracket_type = 'where'; 

function bracket($type = NULL,$append='where') 

{

        if ( strtolower($type) == 'open' )

        {

            // fetch the key of the last entry added

            $key = key($this->ar_where);

            $this->ar_bracket_open = TRUE;

            // add a bracket close

            $this->ar_where[$key] = '('.$this->ar_where[$key];

        }

        elseif ( strtolower($type) == 'close' )

        {

            // fetch the key of the last entry added

            if ($append == 'like')    {

                end($this->ar_like);

                $key = key($this->ar_like);

                // add a bracket close

                   $this->ar_like[$key] .= ')';

                // update the AR cache clauses as well

                if ($this->ar_caching === TRUE)

                {

                    $this->ar_cache_like[$key] = $this->ar_like[$key];

                }

            } else {

                end($this->ar_where);

                $key = key($this->ar_where);

                // add a bracket close

                   $this->ar_where[$key] .= ')';

                // update the AR cache clauses as well

                if ($this->ar_caching === TRUE)

                {

                    $this->ar_cache_where[$key] = $this->ar_where[$key];

                }

            }

        }

        return $this;

} 

 Dan cara menggunakannya seperti ini…

//bracket started

$this->db->bracket('open','like'); //bracket closed  

$this->db->or_like(array('field1'=>$filter1, 'field2'=>$filter2)); 

$this->db->bracket('close','like'); //bracket closed 

Codeigniter sqlsrv_driver.php (affected_rows) (fix)

Fix update untuk "affected_rows".
Edit file "/system/database/drivers/sqlsrv/sqlsrv_driver.php", cari function dibawah ini :
function _execute($sql)
{
$sql = $this->_prep_query($sql);
if(stripos($sql,'UPDATE') !== FALSE) {
return sqlsrv_query($this->conn_id, $sql, null, array());
}  
return sqlsrv_query($this->conn_id, $sql, null, array(
'Scrollable' => SQLSRV_CURSOR_STATIC,
'SendStreamParamsAtExec' => true
));
}

function affected_rows()

{

// return @sqlrv_rows_affected($this->conn_id); //{BUG} : RETURN WHITE BLANK SCREEN ERROR !

return sqlsrv_rows_affected($this->result_id);

}

Codeigniter sqlsrv_driver.php (function _limit) (fix)

Fix update untuk "function _limit", agar compatible dengan SQL Server All Version.
Edit file "/system/database/drivers/sqlsrv/sqlsrv_driver.php", Line 575:

 function _limit($sql, $limit, $offset) { if($offset === FALSE || version_compare($this->version(), "9.0.00", "<=")) { // do simple limit if no offset, or version is earlier than 2005 $i = $limit + $offset; return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); } else { if (version_compare($this->version(), "11.0.00", ">=")) { $orderBy = "ORDER BY "; $orderBy .= implode(', ', $this->ar_orderby); if ($this->ar_order !== FALSE) { $orderBy .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC'; } else { $orderBy .= '1 ASC '; } // for Denali's new feature if ($this->ar_orderby !== FALSE) return $sql . "OFFSET " . $offset . " ROWS FETCH NEXT " . $limit . " ROWS ONLY"; else return $sql . $orderBy . "OFFSET " . $offset . " ROWS FETCH NEXT " . $limit . " ROWS ONLY"; // for Denali's new feature // return $sql . "OFFSET " . $offset . " ROWS FETCH NEXT " . $limit . " ROWS ONLY"; } else { $orderBy = "ORDER BY "; $orderBy .= implode(', ', $this->ar_orderby); if ($this->ar_order !== FALSE) { $orderBy .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC'; } $sql = preg_replace('/(\\'. $orderBy .'\n?)/i','', $sql); $sql = preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 row_number() OVER ('.$orderBy.') AS CI_offset_row_number, ', $sql); $newSQL = "SELECT * \nFROM (\n" . $sql . ") AS A \nWHERE A.CI_offset_row_number BETWEEN (" .($offset + 1) . ") AND (".($offset + $limit).")"; return $newSQL; } } }

SQL Server 2012, Enable Remote Connections. (Untuk meng-aktifkan SQLSvr agar bisa terkoneksi melalui jaringan)

1. Buka "SQL Server Configuration Manager".

2. Masuk ke menu, "SQL Server Network Configuration\Protocols for SQLEXPRESS"

3. Aktifkan Protocol, "Named Pipes" & "TCP/IP"

4. Restart "SQL Server Service"

5. Aktifkan "SQL Server Browser Service", ganti "Start Mode" menjadi "Automatic" 

Selesai !

NB: "SQL Server Browser Service", berguna untuk memberitahukan kepada Aplikasi di Client, apakah "Instance" dari SQL Server is "Listening on TCP/IP or Named Pipes"

Connect Codeigniter with SQL Server 2012

[Steps 1]. Download File2 yang diperlukan for x64 :
1. xampp-win32-1.8.1-VC9-installer.exe ( Install Apache v2.4 & PHP v5.4 )
2. vcredist_x64.exe ( Visual C++ Redistributed for x64 )
3. SQLSRV30_PHP_CONNECTION.EXE ( Microsoft Drivers for PHP for SQL Server )
4. sqlncli_2012_x64.msi ( SQLSERVER NATIVE CLIENT 2012 )

[Steps 2]. Setelah proses installasi [Steps 1] :
1. Copy-kan file "php_pdo_sqlsrv_54_ts.dll" & "php_sqlsrv_54_ts.dll" ke folder "c:\xampp\php\ext\"
2. Edit file php.ini, tambahkan baris ini :

extension=php_sqlsrv_54_ts.dll
extension=php_pdo_sqlsrv_54_ts.dll

3. Restart Apache Service.

[Steps 3]. Setting Database Config di Codeigniter : (Contoh Settingan)
1. Edit file "application/config/database" :

$active_group = 'sqlsvr12';
$active_record = TRUE;
$db['sqlsvr12']['hostname'] = 'AXIOO-PC\SQLEXPRESS';
$db['sqlsvr12']['username'] = 'sa';
$db['sqlsvr12']['password'] = 'password';
$db['sqlsvr12']['database'] = 'db_test';
$db['sqlsvr12']['dbdriver'] = 'sqlsrv';
$db['sqlsvr12']['dbprefix'] = '';
$db['sqlsvr12']['pconnect'] = TRUE;
$db['sqlsvr12']['db_debug'] = TRUE;
$db['sqlsvr12']['cache_on'] = FALSE;
$db['sqlsvr12']['cachedir'] = '';
$db['sqlsvr12']['char_set'] = 'utf8';
$db['sqlsvr12']['dbcollat'] = 'utf8_general_ci';
$db['sqlsvr12']['swap_pre'] = '';
$db['sqlsvr12']['autoinit'] = TRUE;
$db['sqlsvr12']['stricton'] = FALSE;

2. Edit file "/system/database/drivers/sqlsrv/sqlsrv_driver.php" liat ini.
3. Done !

[Codeigniter] sqlsrv_driver.php (bug)

Edit file "/system/database/drivers/sqlsrv/sqlsrv_driver.php"
1. Line 87:
OLD:

function db_pconnect(){$this->db_connect(TRUE);}

NEW:

function db_pconnect(){return $this->db_connect(TRUE);}

2. Line 274 :  {BUG} : RETURN WHITE BLANK SCREEN ERROR !
OLD:

function affected_rows(){return @sqlrv_rows_affected($this->conn_id);}

NEW:

function affected_rows(){return sqlsrv_rows_affected($this->result_id);}