Re: BUG #2236: extremely slow to get unescaped bytea data

I modified your code slightly to isolate the pg_unescape_bytea() from
the pg_fetch_result():

<?php
// Connect to the database
$dbconn = pg_connect('dbname=foo');

// Get the bytea data
$res = pg_query("SELECT data FROM bar WHERE name='image'");
$tstart = microtime(true);
$raw = pg_fetch_result($res, 'data');
$fetch = microtime(true) - $tstart;
$tfetch = microtime(true);

// Convert to binary and send to the browser
// header('Content-type: image/gif');
header('Content-type: text/plain');
$data = pg_unescape_bytea($raw);
$dt = microtime(true) - $tfetch;
printf("fetch time = %.3fms, %d bytes\n", $fetch*1000.0,strlen($data));
printf("unescape time = %.3fms, %d bytes\n", $dt*1000.0,strlen($data));
?>

When run from a browser, accessing an Apache webserver, the results
displayed were:

fetch time = 3.632ms, 296043 bytes
unescape time = 70625.518ms, 296043 bytes

When running the same code from command line php, the results were:

fetch time = 3.618ms, 296043 bytes
unescape time = 8.298ms, 296043 bytes

Am running Apache 1.3.33. I can't figure the difference between
environments - command line php and apache php are compiled the same
(except for the --with-apxs=... instruction for apache version).

The code to insert into the database was:

<?php
// Connect to the database
$dbconn = pg_connect('dbname=foo');

// Read in a binary file
$data = file_get_contents('big.gif');

// Escape the binary data
$escaped = pg_escape_bytea($data);

// Insert it into the database
pg_query("INSERT INTO bar (name, data) VALUES ('image', '{$escaped}')");
?>

As an aside, I'm now using base64 encode/decode to insert data without
problems. For GIF images, at least, it is also a much smaller encoding
than the escape_bytea encoding.

Kai Ronan
Technical Support
Kalador Entertainment Inc.

Michael Fuhr wrote:

>On Sat, Feb 04, 2006 at 04:06:11PM -0800, Kalador Tech Support wrote:
>
>
>>I've since isolated the problem to the unescape_bytea function not the
>>SELECT.
>>
>>I inserted the same image to a bytea column using base64 encoding, and
>>extracted it from the table (using base64 decoding) and this worked very
>>fast (<1 second). So, it is the unescape_bytea function that is to blame.
>>
>>
>
>pg_unescape_bytea is fast here; I just unescaped an 850K jpeg image
>in about 0.18 seconds on a slow (500MHz) machine.
>
>How did you determine that pg_unescape_bytea was the problem? What
>does something like the following show?
>
>$tstart = microtime(true);
>$data = pg_unescape_bytea(pg_fetch_result($res, 'data'));
>$dt = microtime(true) - $tstart;
>header("Content-Type: text/plain");
>printf("unescape time = %.3fms, %d bytes\n", $dt * 1000.0, strlen($data));
>
>
>