[Sqlrelay-discussion] SQLrelay very slow on getting data

[Jens B. <je...@ch...>]

Hi all,

i have installed sqlrelay on or development envireonment and made some
performance tests. I knew, that sqlrelay is slower as PDO, because of
the internal double copy of data. But so many times slower? 

Configuration: sqlrelay on pentium4 2,8GHz connected to a Oracle 10g RAC
cluster. Simple select statement on a table with around 700MB data. 

Here is the result:
PDO: 7733 rows in 27 seconds (286.40740740741 rows/second)
SQL Relay: 7733 rows in 207 seconds (37.357487922705 rows/second)

The amount of data transferred is around 60 MB. 

I tried to play with fetchatonce and maxselectlistsize, but if i set
fetchatonce to 100 and maxselectlistsize to 512, i couldn't establish a
connection to the cluster.


Connection Data:

<!-- Regular SQL Relay Instance -->
<instance id="OMNIDUMP" port="9000" socket="/tmp/sqlr_OMNIDUMP.socket"
dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5"
growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener"
handoff="pass" deniedips="" allowedips="" debug="none"
maxquerysize="65536" maxstringbindvaluelength="4000"
maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
listenertimeout="0" reloginatstart="false">
                <users>
                        <user user="xxx" password="xxx"/>
                </users>
                <connections>
                        <connection connectionid="OMNIDUMP1"
string="user=xxx;password=xxx;oracle_sid=OMNIDUMP;oracle_home=INSTANT;nls_lang=GERMAN_GERMANY.WE8ISO8859P1;" metric="1" behindloadbalancer="no"/>
                </connections>
</instance>


SQL Relay Script:
/**
 * SQL Relay
 */

$sqlRelayConnection =
sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "xxx",
"xxx", 0, 1);
if (sqlrcon_ping($sqlRelayConnection)) {
        $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);

        $sqlRelayLocations = new CList();

        $sqlRelayTime = time();
        sqlrcur_prepareQuery($sqlRelayCursor, $query);
        if (sqlrcur_executeQuery($sqlRelayCursor)) {
                for ($row = 0; $row < sqlrcur_rowCount($sqlRelayCursor);
$row++) {
                        $sqlRelayLocations->addElement(new
CObject(sqlrcur_getRowAssoc($sqlRelayCursor, $row)));
                }
        }
        $sqlRelayTime = time() - $sqlRelayTime;
        echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
$sqlRelayTime)." rows/second)<br />\n";

        sqlrcur_free($sqlRelayCursor);
}
sqlrcon_free($sqlRelayConnection);


Anyone a idea?


Regards,

Jens Brey