Thread: 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]

        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