Skip to content

Loading…

Making DB_result driver method _data_seek public #2050

Closed
ragboyjr opened this Issue · 2 comments

2 participants

@ragboyjr

Currently with CI result object, when you call the row method, it has to iterate over the entire result set into an array before it gets a row at the desired offset. I've done some profiling and have found that using _data_seek to get to a desired row is much faster and memory efficient than throwing everything into an array (at least for the mysqli driver).
Currently Ci's result class's _data_seek is protected. With CI 3.0's unbuffered_row allowing for memory efficient traversal, allowing _data_seek to be public will nicely follow in its footsteps. All we'd have to do to implement this change would be to edit the line
Codeigniter/system/database/DB_result.php:610
from
protected function _data_seek($n = 0)
to
public function data_seek($n = 0)

Below is some code I generated to do my profiling. I used php highlight_file() to generate the html, so sorry ahead of time if it looks a little weird.

RJ


<?php
$db = new mysqli('host', 'username', 'password', 'db_name');
$res = $db->query("select  from table");

$t_start = microtime(TRUE);
$obj = $res->fetch_object();
while ($obj !== NULL)
{
    $obj = $res->fetch_object();
}
$t_end = microtime(TRUE);
echo $obj->id . PHP_EOL;

echo round(($t_end - $t_start) 
 1000, 2) . 'ms' . PHP_EOL;


$t_start = microtime(TRUE);
$res->data_seek(10000);
$obj = $res->fetch_object();
$t_end = microtime(TRUE);
echo $obj->id . PHP_EOL;

echo round(($t_end - $t_start)  1000, 2) . 'ms' . PHP_EOL;


$t_start = microtime(TRUE);
$vals = $res->fetch_all();
$obj = $vals[10000];
$t_end = microtime(TRUE);
echo $obj[0] . PHP_EOL;

echo round(($t_end - $t_start) 
 1000, 2) . 'ms' . PHP_EOL;

// comment out two of the three tests and uncomment the next instruction to see memory usage
//echo memory_get_peak_usage() . PHP_EOL;

/
Profile Results on my machine
The table I ran these results on had 12527 rows

data_seek
using mysqli data_seek

array
put everything into an array and then get 10000th item

iterate
iterate through using fetch object until reach 10000th item

Profile results

data_seek Memory: 1428992 bytes
data_seek Speed: 0.02 ms

iterate Memory: 8229664 bytes
iterate Speed: 41.8 ms

array Memory: 23826664 bytes
array Speed: 25.73 ms

Results:
data_seek is 5.76x more memory efficient than iterate (iterate consumes 5.76x more memory)
data_seek is 16.7x more memory efficient than array (array consumes 16.7x more memory)

data_seek is 1290x faster than array
data_seek is 2090x faster than iterate
/

@narfbg

See the above commit.

@narfbg narfbg closed this
@ragboyjr

Wow, thank you so much dude.

@nonchip nonchip pushed a commit to nonchip/CodeIgniter that referenced this issue
@narfbg narfbg Rename DB_result _data_seek() to data_seek() and make it publicly ava…
…ilable

(as requested in #2050)
6d222e0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.