[Sqlrelay-discussion] [Solved] Re: SQLrelay very slow on getting data
Brought to you by:
mused
Menu
▾
▴
[Sqlrelay-discussion] [Solved] Re: SQLrelay very slow on getting data
|
From: Jens B. <je...@ch...> - 2007-10-01 12:02:49
|
Hi all,
after we decreased the size of the ResultBuffer it kicks ass... ;)
[root@outpost1 jens]# php comparePdoToSqlRelay2.php
SQL Relay: 7734 rows in 9.5583131313324 seconds (809.13858896794
rows/second)<br />
[root@outpost1
jens]#
It's around three times faster as PDO...
PDO: 7733 rows in 31.92887711525 seconds (242.1945492191 rows/second)
ResultBuffer size is set to 500 ;)
Hf,
Jens
Am Mittwoch, den 26.09.2007, 17:02 +0200 schrieb Jens Brey:
> 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", "debug",
> "debug", 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
>
>
> -------------------------------------------------------------------------
> 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
|
