Re: Getting time of a postgresql-request - Mailing list pgsql-performance

On Tue, 18 Aug 2009 06:25:57 +0200, Russell Smith <mr-russ@pws.com.au>
wrote:

> Kai Behncke wrote:
>>
>> But I would like to get it in a php-script, like
>>
>> $timerequest_result=pg_result($timerequest,0);
>>
>> (well, that does not work).
>>
>> I wonder: Is there another way to get the time a request needs?
>> How do you handle this?
>>
> $time = microtime()
> $result = pg_result($query);
> echo "Time to run query and return result to PHP: ".(microtime() -
> $time);
>
> Something like that.
>
> Regards
>
> Russell
>

I use the following functions wich protect against SQL injections, make
using the db a lot easier, and log query times to display at the bottom of
the page.
It is much less cumbersome than PEAR::DB or pdo which force you to use
prepared statements (slower if you throw them away after using them just
once)

db_query( "SELECT * FROM stuff WHERE a=%s AND b=%s", array( $a, $b ))

db_query( "SELECT * FROM stuff WHERE id IN (%s) AND b=%s", array(
$list_of_ints, $b ))

------------

function db_quote_query( $sql, $params=false )
{
    // if no params, send query raw
    if( $params === false )    return $sql;
    if( !is_array( $params )) $params = array( $params );

    // quote params
    foreach( $params as $key => $val )
    {
        if( is_array( $val ))
            $params[$key] = implode( ', ', array_map( intval, $val ));
        else
            $params[$key] = is_null($val)?'NULL':("'".pg_escape_string($val)."'");;
    }
    return vsprintf( $sql, $params );
}

function db_query( $sql, $params=false )
{
    // it's already a query
    if( is_resource( $sql ))
        return $sql;

    $sql = db_quote_query( $sql, $params );

    $t = getmicrotime( true );
    if( DEBUG > 1 )    xdump( $sql );
    $r = pg_query( $sql );
    if( !$r )
    {
        if( DEBUG > 1 )
        {
            echo "<div class=bigerror><b>Erreur PostgreSQL :</b><br
/>".htmlspecialchars(pg_last_error())."<br /><br /><b>Requête</b> :<br
/>".$sql."<br /><br /><b>Traceback </b>:<pre>";
            foreach( debug_backtrace() as $t ) xdump( $t );
            echo "</pre></div>";
        }
        die();
    }
    if( DEBUG > 1)    xdump( $r );
    global $_global_queries_log, $_mark_query_time;
    $_mark_query_time = getmicrotime( true );
    $_global_queries_log[] = array( $_mark_query_time-$t, $sql );
    return $r;
}