Performance Low Using the Prepare and Execute
Hi,
I want to use the Prepare and Execute resources of PostgreSQL to
increment the performance of my SQL's.
I do tests using the PHP and ADODB inserting 5000 registers and
counting the time of execution. But the results was pratically
identical.
See below the results:
Time of Execution (ADODB:: Prepare and Execute Manually) : 44.9510087967
Time of Execution (ADODB:: Prepare and Execute of ADODB): 47.6438999176
Time of Execution (ADODB:: Without Prepare): 47.6229438782
Test1 Code:
$sSQL = 'EXECUTE teste(\'teste0...............\')';
for ( $i = 0; $i < $iNTestes; $i++ )
$oDB->execute_query( $sSQL, __LINE__, __FILE__ );
Test2 Code:
$sSQL = 'INSERT INTO teste (nome) VALUES( ? )';
$oDB->Prepare( $sSQL );
for ( $i = 0; $i < $iNTestes; $i++ )
$oDB->ExecutePrepare( Array( 'teste1...............' ), __LINE__, __FILE__ );
Teste3 Code:
$sSQL = 'INSERT INTO teste (nome) VALUES(\'teste2...............\')';
for ( $i = 0; $i < $iNTestes; $i++ )
$bOk = $oDB->execute_query( $sSQL, __LINE__, __FILE__ );
The SQL that they use the Prepare and Execute would have to be lesser
than the others?
Thanks.
--
__________________
Marcos José Setim
mjs_ops@gmx.net
http://www.linuxhard.org
On Sat, 2006-01-07 at 20:38 +0000, Marcos Jos� Setim wrote:
I want to use the Prepare and Execute resources of PostgreSQL to
increment the performance of my SQL's.
$sSQL = 'INSERT INTO teste (nome) VALUES( ? )';
$oDB->Prepare( $sSQL );
The PREPARE documentation states:[1]http://developer.postgresql.org/docs/postgres/sql-prepare.html
Prepared statements have the largest performance advantage when
a single session is being used to execute a large number of
similar statements. The performance difference will be
particularly significant if the statements are complex to plan
or rewrite, for example, if the query involves a join of many
tables or requires the application of several rules. If the
statement is relatively simple to plan and rewrite but
relatively expensive to execute, the performance advantage of
prepared statements will be less noticeable.
Since an INSERT ... VALUES without a subselect or any applicable rules
requires very little parsing, planning, or rewriting time,
PREPARE/EXECUTE is unlikely to improve performance.
-Neil
[1]: http://developer.postgresql.org/docs/postgres/sql-prepare.html