Re: Re: [metabase-dev] RE: [PEAR-DEV] Re: [binarycloud-dev] FW: letstalk "metapear" - politics aside:-)
| From: | Thies C. Arntzen | Date: | Tue, 19 Mar 2002 18:42:00 +0000 |
| Subject: | Re: Re: [metabase-dev] RE: [PEAR-DEV] Re: [binarycloud-dev] FW: letstalk "metapear" - politics aside:-) | ||
| References: | 1 2 3 | Groups: | php.pear.dev |
| Request: | Send a blank email to pear-dev+get-5048@lists.php.net to get a copy of this message | ||
On Wed, Mar 20, 2002 at 01:06:28AM +0800, John Lim wrote:
>
> > Query caching: Some databases, like Oracle, have a cache of parsed and
> > optimized queries. By using prepare(), the database will need to parse
> > and optimize your query just once instead of every time you execute it.
> > This can be a really big speedup.
> >
> Hi Stig,
>
> Strangely enough, I recently benchmarked binding of variables and got some
> strange results. They were 6 times slower than sending pure sql.
try my attached "insert_speed" benchmark. fior me it shows a
3,5 fold speedup using bind and reusing the statement.
re,
tc
<?php require_once 'Benchmark/Timer.php'; $insert_rows = 5000; function make_table($db) { @OCIExecute(OCIParse($db,"create table instest (test varchar2(32))")); } function drop_table($db) { @OCIExecute(OCIParse($db,"drop table instest")); } function insert_reuse_bind($db,$insert_rows) { $stmt = OCIParse($db,"insert into instest values (:test)"); $test = 0; OCIBindByName($stmt,":test",$test,32); for ($test = 0; $test < $insert_rows; $test++) { if (! OCIExecute($stmt, OCI_DEFAULT)) break; if (!($test % 100)) { echo "$test "; flush(); } } } function insert_bind($db,$insert_rows) { for ($test = 0; $test < $insert_rows; $test++) { $stmt = OCIParse($db,"insert into instest values (:test)"); OCIBindByName($stmt,":test",$test,32); if (! OCIExecute($stmt, OCI_DEFAULT)) break; if (!($test % 100)) { echo "$test "; flush(); } } } function insert($db,$insert_rows) { for ($test = 0; $test < $insert_rows; $test++) { $stmt = OCIParse($db,"insert into instest ". "values ('$test')"); if (! OCIExecute($stmt, OCI_DEFAULT)) break; if (!($test % 100)) { echo "$test "; flush(); } } } $db = OCILogon("thies","tubu"); $timer = new Benchmark_Timer; function start_test() { global $db; global $timer; drop_table($db); make_table($db); $timer->start(); } function end_test() { global $timer; $timer->stop(); $tim = $timer->timeElapsed(); echo "\ntime elapsed $tim\n"; } start_test(); echo "timing $insert_rows inserts using parse\n"; flush(); insert($db,$insert_rows); end_test(); start_test(); echo "timing $insert_rows inserts using parse & bind\n"; flush(); insert_bind($db,$insert_rows); end_test(); start_test(); echo "timing $insert_rows inserts using the same statment & bind\n"; flush(); insert_reuse_bind($db,$insert_rows); end_test(); drop_table($db); ?>
<?php require_once 'Benchmark/Timer.php'; $insert_rows = 5000; function make_table($db) { @OCIExecute(OCIParse($db,"create table instest (test varchar2(32))")); } function drop_table($db) { @OCIExecute(OCIParse($db,"drop table instest")); } function insert_reuse_bind($db,$insert_rows) { $stmt = OCIParse($db,"insert into instest values (:test)"); $test = 0; OCIBindByName($stmt,":test",$test,32); for ($test = 0; $test < $insert_rows; $test++) { if (! OCIExecute($stmt, OCI_DEFAULT)) break; if (!($test % 100)) { echo "$test "; flush(); } } } function insert_bind($db,$insert_rows) { for ($test = 0; $test < $insert_rows; $test++) { $stmt = OCIParse($db,"insert into instest values (:test)"); OCIBindByName($stmt,":test",$test,32); if (! OCIExecute($stmt, OCI_DEFAULT)) break; if (!($test % 100)) { echo "$test "; flush(); } } } function insert($db,$insert_rows) { for ($test = 0; $test < $insert_rows; $test++) { $stmt = OCIParse($db,"insert into instest ". "values ('$test')"); if (! OCIExecute($stmt, OCI_DEFAULT)) break; if (!($test % 100)) { echo "$test "; flush(); } } } $db = OCILogon("thies","tubu"); $timer = new Benchmark_Timer; function start_test() { global $db; global $timer; drop_table($db); make_table($db); $timer->start(); } function end_test() { global $timer; $timer->stop(); $tim = $timer->timeElapsed(); echo "\ntime elapsed $tim\n"; } start_test(); echo "timing $insert_rows inserts using parse\n"; flush(); insert($db,$insert_rows); end_test(); start_test(); echo "timing $insert_rows inserts using parse & bind\n"; flush(); insert_bind($db,$insert_rows); end_test(); start_test(); echo "timing $insert_rows inserts using the same statment & bind\n"; flush(); insert_reuse_bind($db,$insert_rows); end_test(); drop_table($db); ?>