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));
>
>
>