Re: [Python-sybase-misc] Performance issues when upgrading from 0.38 to 0.39

[Sébastien S. <sa...@us...>]

Hi Zsolt,

thank you for the feedback on python-sybase.

the use of ct_cursor was made as a way to make python-sybase behave more 
like dbapi bridges for other databases when doing nested requests.

I used to have plenty of "This routine cannot be called because another 
command structure has results pending" error messages when porting to 
sybase some python DBAPI compliant code that was working fine with 
oracle, postgres or mysql.

There is an example illustrating the problem here:

http://www.object-craft.com.au/pipermail/python-sybase/2006-April/000467.html

Basically with Sybase, if you want to update some row while retrieving 
some rows with a cursor, you have to either fetch all the rows 
beforehand or use a ct_cursor instead of a ct_command.

Fetching all the rows with fetchall can be very time and memory 
consuming when dealing with a huge table.

Even though this mechanism with ct_cursor makes it more transparent to 
work with Sybase in the case of nested queries, it seems to be causing 
some trouble to you and other people in other cases.

The solution for a coming 0.40 version of python-sybase should probably 
  be to deactivate this mechanism by default, and to only use ct_cursor 
when it is explicitly specified.

In the meantime, you may apply the following patch to deactivate 
ct_cursor usage:

Index: Sybase.py
===================================================================
--- Sybase.py	(révision 443)
+++ Sybase.py	(copie de travail)
@@ -457,7 +457,7 @@
              if status != CS_SUCCEED:
                  break

-    def prepare(self, sql, select = None):
+    def prepare(self, sql, select = False):
          '''Prepare to retrieve new results.
          '''
          self._lock()
@@ -477,7 +477,7 @@
          finally:
              self._unlock()

-    def execute(self, sql, params = {}, select = None):
+    def execute(self, sql, params = {}, select = False):
          '''DB-API Cursor.execute()
          '''
          self._lock()


As far as performances are concerned, I made a few improvements in the 
subversion trunk (for example reuse the same ct_command/ct_curor when 
doing repeatedly the same request).

I have other performance improvements in the pipe which have not been 
commited yet (including rewriting some parts in cython/pyrex), but I 
didn't have the time recently to finish them since I had other 
priorities. I hope to be able to dedicate a few weeks to python-sybase 
in coming months to finish this and correct a few bugs which have been 
reported.

regards

--
Sébastien Sablé



Cserna, Zsolt a écrit :
> Hi all,
> 
> We've found performance drop in python-sybase when upgrading from 0.38 to 0.39.
> We run it on Linux, python version 2.5.4 and python-sybase is linked to 15.0.0.7 version of sybase client library.
> 
> The problem occur when we are fetching the results of a select query from the server. The difference between 0.38 and 0.39 is huge, we found 0.39 is about 10-100 times slower than 0.38.
> 
> Please find the test script attached (rename it to .py). There's no difference in what table we select, it could contain text or number, it may have only one column or more columns.
> 
> Usually we get the following output when using 0.38:
> Took 3.626094 seconds to fetch 10000 rows: 2757.788330 rows/second
> 
> And from 0.39:
> Took 647.513509 seconds to fetch 10000 rows: 15.443693 rows/second
> 
> If I replace the line calling execute() to the following:
> cursor.execute('SELECT top %d * FROM test_table' % rows, select=False)
> 
> ...then in 0.39 it becomes fast, getting similar results as in 0.38.
> It seems to me that using ct_cursor (which can be disabled by select=False) results the performance drop in 0.39.
> Is there any reason to use ct_cursor or any drawback of not using it?
> 
> Is this problem general? Or does it occur in our enviroment? 
> 
> Thank you in advance,
> Zsolt
> 
> Zsolt Cserna
> Morgan Stanley | IDEAS Practice Areas
> Lechner Odon fasor 8 | Floor 07
> Budapest, 1095
> Phone: +36 1 881-3968
> Zso...@Mo...
> 
> --------------------------------------------------------------------------
> NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law.
> 
> 
> ------------------------------------------------------------------------
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
> trial. Simplify your report design, integration and deployment - and focus on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc