Re: Performance issues when upgrading from 0.38 to 0.39

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.ht=
ml

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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- Sybase.py	(r=E9vision 443)
+++ Sybase.py	(copie de travail)
@@ -457,7 +457,7 @@
              if status !=3D CS_SUCCEED:
                  break

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

-    def execute(self, sql, params =3D {}, select =3D None):
+    def execute(self, sql, params =3D {}, select =3D 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=E9bastien Sabl=E9



Cserna, Zsolt a =E9crit :
> 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 1=
5.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 diffe=
rence 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=3DFalse)
> =

> ...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=3DFa=
lse) 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
> [email protected]
> =

> --------------------------------------------------------------------------
> NOTICE: If received in error, please destroy, and notify sender. Sender d=
oes 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 e=
xtent permitted by applicable law.
> =

> =

> ------------------------------------------------------------------------
> =

> -------------------------------------------------------------------------=
-----
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-D=
ay =

> trial. Simplify your report design, integration and deployment - and focu=
s 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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc


---------------------------------------------------------------------------=
---
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