php - mysqli prepared statements buffered data vs unbuffered data Performance? -

php - mysqli prepared statements buffered data vs unbuffered data Performance? -

how much % performance increased or decreased on using buffered info vs unbuffered data in mysql using mysqli prepared statements.,

1. buffered data. e.g.

$query = "select name, countrycode city order id desc limit 150,5"; if ($stmt = mysqli_prepare($link, $query)) { /* execute statement */ mysqli_stmt_execute($stmt); /* bind result variables */ mysqli_stmt_bind_result($stmt, $row['name'], $row['countrycode']); /* store result */ mysqli_stmt_store_result($stmt); /* fetch values */ while (mysqli_stmt_fetch($stmt)) { echo $row['name'].'-'. $row['countrycode']; } /* free result */ mysqli_stmt_free_result($stmt); /* close statement */ mysqli_stmt_close($stmt); } /* close connection */ mysqli_close($link);

2. unbuffered data. e.g.

$query = "select name, countrycode city order id desc limit 150,5"; if ($stmt = mysqli_prepare($link, $query)) { /* execute statement */ mysqli_stmt_execute($stmt); /* bind result variables */ mysqli_stmt_bind_result($stmt, $row['name'], $row['countrycode']); /* fetch values */ while (mysqli_stmt_fetch($stmt)) { echo $row['name'].'-'. $row['countrycode']); } /* close statement */ mysqli_stmt_close($stmt); } /* close connection */ mysqli_close($link);

the first illustration uses

mysqli_stmt_store_result($stmt);

which stores result info in buffer later fetching

while illustration fetches info straight client without storing in buffer .

you must phone call mysqli_stmt_store_result() every query produces result set (select, show, describe, explain), if , if want buffer finish result set client, subsequent mysqli_stmt_fetch() phone call returns buffered data.

ref: http://php.net/manual/en/mysqli-stmt.store-result.php

so question how much performace increased if mysqli_stmt_store_result used ?

although haven't ever had need stored results still found question interesting , know same thing. have created database junk info in , ran tests against see timing differences using unbuffered , buffered results. i've included tests , findings in case helps (or else matter).

database setup

the database consists of single table 4 different fields. here schema:

id | int(5) | primary_key, auto_increment name | tinytext | not_null countrycode | int(3) | not_null description | tinytext | not_null

the table has on 10,000 rows junk data. id filled auto-increment value, countrycode random number between 11 , 999, , lastly 2 fields, name , description, contain md5 hashes of numerical data.

the tests

in total 6 different tests done. first 5 tests comparisons of processing times between unbuffered , buffered results. overall goal of tests see benchmarks of different sized results sets starting @ origin of table , in middle. final test benchmark of buffered results beingness accessed randomly versus sequentially. unbuffered results cannot arbitrarily through mysqli_stmt_data_seek() comparing hard craft , considered fair.

benchmark times calculated using microtime(). ticks started right before mysqli statement prepared , ended statement closed.

here breakdown of 6 tests:

test 1: compares unbuffered/buffered processing times of result set of 100 rows selected beginning of table. test 2: compares unbuffered/buffered processing times of result set of 100 rows selected middle of table. test 3: compares unbuffered/buffered processing times of result set of 1000 rows selected beginning of table. test 4: compares unbuffered/buffered processing times of result set of 1000 rows selected middle of table. test 5: compares unbuffered/buffered processing times of result set of 5000 rows selected beginning of table , repeated 3 times. test 6: benchmark of processing time of result set of 10000 rows, accessed randomly, , repeated 3 times. the results

php produced next output tests described above.

test 1 took 0.002000093460083 seconds process unbuffered result of 100 rows origin of table took 0.0019998550415039 seconds process buffered result of 100 rows origin of table test 2 took 0.012001037597656 seconds process unbuffered result of 100 rows middle of table took 0.011001110076904 seconds process buffered result of 100 rows middle of table test 3 took 0.013001918792725 seconds process unbuffered result of 1000 rows origin of table took 0.012001037597656 seconds process buffered result of 1000 rows origin of table test 4 took 0.023001909255981 seconds process unbuffered result of 1000 rows middle of table took 0.020002126693726 seconds process buffered result of 1000 rows middle of table test 5 took 0.19601988792419 seconds process unbuffered result of 5000 rows sequentially, 3 times took 0.085008144378662 seconds process buffered result of 5000 rows sequentially, 3 times test 6 took 4.2634270191193 seconds process buffered result of 10000 rows randomly, 3 times conclusion

tests 1-4 show performance gain negligible part. gains buffering results minimal when dealing lot of rows or taking records various offsets in table. offsetting location added overhead (about hundredth of sec advance 5000 rows).

that said, buffering still has place. in test five, result set several one thousand rows iterated multiple times, there clear benefit using buffered results. both buffered , unbuffered versions of test process 15,000 rows. because buffered version not have retrieve results 1 time again able work in less half of time unbuffered version does.

as pointed out others in question, buffering helpful when having access rows arbitrarily/randomly. test 6 shows how long buffered set of 10,000 rows can accessed randomly , repeated additional 2 times. test 6 fetches 30,000 rows out of order.

of course, code

here code used create test. procedural isn't prettiest at, if ever find myself making class out of or modifying code clean up, i'll sure update here!

<?php //tell php not mind how long running set_time_limit(0); //control output test results ob_start(); //array hold time values tests $times = array(); //connect database $connection = mysqli_connect("localhost", "root", "", "blah"); /*********************************************************************** * test 1: little result set of 100 rows origin of table **********************************************************************/ $times['test 1'] = array(); //unbuffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 100"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 1'][] = "took $time seconds process unbuffered result of 100 rows origin of table"; //buffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 100"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); mysqli_stmt_store_result($stmt); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_free_result($stmt); mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 1'][] = "took $time seconds process buffered result of 100 rows origin of table"; echo "\n ** end test 1** \n\n"; /******************************************************************* * test 2: little result set of 100 rows middle of table ******************************************************************/ $times['test 2'] = array(); //unbuffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 5000, 100"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 2'][] = "took $time seconds process unbuffered result of 100 rows middle of table"; //buffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 5000, 100"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); mysqli_stmt_store_result($stmt); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_free_result($stmt); mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 2'][] = "took $time seconds process buffered result of 100 rows middle of table"; echo "\n ** end test 2** \n\n"; /*********************************************************************** * test 3: big result set of 1000 rows origin of table **********************************************************************/ $times['test 3'] = array(); //unbuffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 1000"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 3'][] = "took $time seconds process unbuffered result of 1000 rows origin of table"; //buffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 1000"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); mysqli_stmt_store_result($stmt); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_free_result($stmt); mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 3'][] = "took $time seconds process buffered result of 1000 rows origin of table"; echo "\n ** end test 3** \n\n"; /******************************************************************** * test 4: big result set of 1000 rows middle of table *******************************************************************/ $times['test 4'] = array(); //unbuffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 5000, 1000"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 4'][] = "took $time seconds process unbuffered result of 1000 rows middle of table"; //buffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 5000, 1000"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); mysqli_stmt_store_result($stmt); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } mysqli_stmt_free_result($stmt); mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 4'][] = "took $time seconds process buffered result of 1000 rows middle of table"; echo "\n ** end test 4** \n\n"; /****************************************************************************** * test 5: work larger result set, 5000 rows, multiple times, sequentially *****************************************************************************/ $times['test 5'] = array(); //unbuffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 5000"); ($i = 0; $i < 3; $i++) { mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } } mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 5'][] = "took $time seconds process unbuffered result of 5000 rows sequentially, 3 times"; //buffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 5000"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); mysqli_stmt_store_result($stmt); ($i = 0; $i < 3; $i++) { mysqli_stmt_data_seek($stmt, 0); while (mysqli_stmt_fetch($stmt)) { printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); } } mysqli_stmt_free_result($stmt); mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 5'][] = "took $time seconds process buffered result of 5000 rows sequentially, 3 times"; echo "\n ** end test 5** \n\n"; /*************************************************************************** * test 6: work larger result set, 10000 rows, multiple times, randomly **************************************************************************/ $times['test 6'] = array(); //unbuffered version //can't test unbuffered result sets randomly mysqli_stmt_data_seek //only works on buffered results. //buffered version $benchmarkstart = microtime(true); $stmt = mysqli_prepare($connection, "select * city limit 10000"); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc); mysqli_stmt_store_result($stmt); ($i = 0; $i < 3; $i++) { $rows = range(0, (mysqli_stmt_num_rows($stmt) - 1)); shuffle($rows); ($j = 0; $j < 10000; $j++) { mysqli_stmt_fetch($stmt); printf("%d, %s, %d, %s \n", $id, $name, $code, $desc); $row = $rows[0]; mysqli_stmt_data_seek($stmt, $row); array_shift($rows); } } mysqli_stmt_free_result($stmt); mysqli_stmt_close($stmt); $benchmarkend = microtime(true); $time = $benchmarkend - $benchmarkstart; $times['test 6'][] = "took $time seconds process buffered result of 10000 rows randomly, 3 times"; echo "\n ** end test 6** \n\n"; /******************* * print results ******************/ $output = ob_get_clean(); foreach ($times $tests => $results) { echo $tests . "\n"; foreach ($results $result) { echo $result . "\n"; } echo "\n\n"; } //dumps of rows have been getting printed out browser. //this kicked out little north of 64,000 lines in browser. echo $output; ?>

php mysql mysqli prepared-statement