Re: [Sqlrelay-discussion] Sybase/Perl performance

[Carlos V. <cve...@em...>]

I have determined the lag of SQLRelay compared to ctLib is directly related to the size of the query result set.



For anything under 1000 rows returned, the performance is comparable, for results with more than 1000 rows returned SQLRelay starts to lag.



It seems to be nearly doubling the time to return with every 1000 rows returned.



Test 1


i10

i100

i1000

i2000

i3000

i4000

i5000

i6000

i7000

i8000

i9000

# of rows selected

ctLib

3

4

4

5

5

5

5

6

6

6

6

time in seconds

SqlRelay

3

4

6

12

21

40

83

181

369

620

950






Any ideas what is going on?  I do not have results caching on.  Here is my configuration:



<instance id="test" port="9000" socket="/tmp/sybasetest.socket" dbase="sybase" connections="5"   maxconnections="5" maxqueuelength="1" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">

    <users>

      <user user="dev" password="syb"/>

    </users>

    <connections>

      <connection connectionid="test" string="server=dev_prop;db=prop;user=dev;password=syb;port=4125"  metric="1" behindloadbalancer="no"/>

    </connections>

  </instance>



My test is very basic it just runs the query it doesn't try and print results out or manipulate it.



  my $time2 = new Benchmark;

       $ss->sendQuery($sql);

  my $time3 = new Benchmark;

  my $runtime2 = timestr(timediff($time3,$time2));



Thank you for any help in this matter, I really want to use SQLRelay but I will not be able to unless I can get its performance up to par with ctLib.



CJ Vergara





-----Original Message-----
From: Carlos Vergara
Sent: Monday, February 01, 2010 3:25 PM
To: 'Discussion of topics related to SQL Relay'
Subject: Sybase/Perl performance



  I have a perl use question.  I am implementing SQLRelay with Sybase and Perl.  I want to replace my use of Sybase::CTlib



However just with initial tests, it seems the first query through SQLRelay takes substantially longer that through CTlib.  I have compiled SQLRelay to use the native Sybase C libraries.  I am not sure why the query takes what looks like a whole 1/2 second longer than through ctlib.



I have checked to make sure SQLRelay results caching is off, I am using the local unix socket, is there any other option I can configure?



Is there a way to run queries without setting up a cursor?



my $sc=SQLRelay::Connection->new("localhost",9000," /tmp/sybasetest.socket", "dev","devsyb",0,1);

  if($sc->ping()){ print "DB PING! Successful connection!\n"; } else { print "FAILED PING!  QUITING NOW\n"; exit; }

  my $ss=SQLRelay::Cursor->new($sc);



[1129]./test4.pl dev 10

Sending Query 10 times

ctStub:   x.........

SqlRelay: x.........



Connect 1:  0 wallclock secs ( 0.01 usr +  0.00 sys =  0.01 CPU)

 RunTime1:  1 wallclock secs ( 1.06 usr +  0.05 sys =  1.11 CPU)



Connect 2:  0 wallclock secs ( 0.00 usr +  0.00 sys =  0.00 CPU)

 RunTime2:  4 wallclock secs ( 2.64 usr +  0.02 sys =  2.66 CPU)



Printing Results RowCount|517|

Test Complete!





If I run the same query 1000 times, sqlrelay seems to out perform ctlib because after the first two queries subsequent ones run much faster than ctlib which has the same performance query after query.  Whereas sqlrelay is slow the first two times, then super fast.



My application is not going to run the same query 1000 times though, each query will be different, so I need the first query to be as fast as using ctlib.  Any suggestions would be helpful?



CJ Vergara