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));
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
