Re: [Sqlrelay-discussion] SQLrelay very slow on getting data

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

Hi Dave,

now i have tested the setting of sqlrcur_setResultSetBufferSize(), but
it not helps.

Result: 

PDO: 7733 rows in 31.92887711525 seconds (242.1945492191 rows/second)
SQL Relay: 7733 rows in 212.47803497314 seconds (36.394350131191
rows/second)

But the interesting part is, that if i limit the resultset to a value
lower as around 1600, SQLrelay is faster.

PDO: 1600 rows in 5.829540014267 seconds (274.46419375872 rows/second)
SQL Relay: 1600 rows in 5.2128088474274 seconds (306.93625007747
rows/second)

I also checked out the tunning and the programming page, but i found no
tip, that helps...


Regards, 

Jens

<--- Script --->

/**
 * SQL Relay
 */
$sqlRelayConnection =
sqlrcon_alloc("127.0.0.1","9000","/tmp/sqlr_OMNIDUMP.socket", "map24",
"mappas", 0, 1);
if (sqlrcon_ping($sqlRelayConnection)) {
        $sqlRelayCursor = sqlrcur_alloc($sqlRelayConnection);

        $sqlRelayLocations = new CList();

        $sqlRelayTime = microtime(true);
        sqlrcur_setResultSetBufferSize($sqlRelayCursor, 10000);
        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 = microtime(true) - $sqlRelayTime;
        echo "SQL Relay: ".$sqlRelayLocations->count()." rows in
".$sqlRelayTime." seconds (".($sqlRelayLocations->count() /
$sqlRelayTime)." rows/second)<br />\n";

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


<----- /Script ----->





Am Dienstag, den 25.09.2007, 22:39 -0400 schrieb David Muse:
> That's pretty odd.  I wouldn't expect it to be slower.  Did you try
> using sqlrcur_setResultSetBufferSize()?
> 
> See
> http://sqlrelay.sourceforge.net/sqlrelay/programming/php.html#largeresultsets
> for more info.
> 
> Offhand, that's the only thing I can think of that would cause slowness
> like this.
> 
> Also, check out the tuning tips at:
> http://sqlrelay.sourceforge.net/sqlrelay/tuning.html
> Maybe one of them will help.
> 
> Dave
> dav...@fi...
> 
> On Tue, 2007-09-25 at 18:04 +0200, Jens Brey wrote:
> > 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
> > 
> > 
> > 
> > 
> > 
> > 
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> > 
> > 
> > _________________________________________________________________
> > Need personalized email and website? Look no further. It's easy
> > with Doteasy $0 Web Hosting! Learn more at www.doteasy.com
> 
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion