[sqlalchemy] Re: oracle speed problem and arraysize hack - Michael Bayer - com.googlegroups.sqlalchemy

14 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: oracle speed problem...

From	Sent On	Attachments
gniquil	26 May 2008 01:35
Michael Bayer	26 May 2008 08:06
Waldemar Osuch	26 May 2008 15:19
gniquil	26 May 2008 15:33
Michael Bayer	26 May 2008 16:09
Michael Bayer	26 May 2008 16:10
gniquil	26 May 2008 21:54
Michael Bayer	27 May 2008 07:44
Waldemar Osuch	27 May 2008 12:20
Michael Bayer	27 May 2008 13:20
gniquil	27 May 2008 14:34
gniquil	27 May 2008 14:37
Michael Bayer	27 May 2008 15:34
Michael Bayer	30 May 2008 14:05

Subject:	[sqlalchemy] Re: oracle speed problem and arraysize hack
From:	Michael Bayer (mike...@zzzcomputing.com)
Date:	05/27/2008 01:20:39 PM
List:	com.googlegroups.sqlalchemy

On May 27, 2008, at 3:21 PM, Waldemar Osuch wrote:

Quote:

Up to this point the default arraysize is 1 meaning that a single row is internally fetched at a time. This has nothing to do with fetchone(), fetchmany() or fetchall(). Regardless of which of those methods is used, internally cx_Oracle fetches one row at a time. If you change the arraysize to 50, internally cx_Oracle will fetch 50 rows at a time. Again, this is regardless of whether you use fetchone(), fetchmany() or fetchall(). Some of the confusion may lie in the fact that the default value for rows to fetch in fetchmany() is the arraysize -- but that is all it is, a default value!

see, that's a really unfortunate decision on his part to reuse "arraysize" in such an arbitrary way like that, while *not* using it at the point at which it is entirely reasonable, that is when you have already stated you want to fetchmany(n) or fetchall(). This is totally a bug in cx_oracle.

class MyConnection(cx_Oracle.Connection):

def cursor(self): cursor = cx_Oracle.Cursor(self) cursor.arraysize = 50 return cursor

What this does is automatically set the arraysize to 50 every time a cursor is created. This can be done to transparently set the arraysize and should allow you to proceed with whatever code needs to assume an arraysize of that value. Otherwise you can feel free to change it yourself after creating the cursor.

And as has already been noted, in the next release of cx_Oracle, the default arraysize will be 50 in order to resolve this problem "permanently". :-)

that workaround works, and also implementing "default_arraysize" within OracleDialect as follows is acceptable. If someone can test this and post a trac ticket I can commit this to 0.4/0.5:

Index: lib/sqlalchemy/databases/oracle.py =================================================================== --- lib/sqlalchemy/databases/oracle.py (revision 4819) +++ lib/sqlalchemy/databases/oracle.py (working copy) @@ -213,6 +213,12 @@ self.out_parameters[name] = self.cursor.var(dbtype) self.parameters[0][name] = self.out_parameters[name]

+ def create_cursor(self): + cursor = self._connection.connection.cursor() + if self.dialect.default_arraysize: + cursor.arraysize = self.dialect.default_arraysize + return cursor + def get_result_proxy(self): if hasattr(self, 'out_parameters'): if self.compiled_parameters is not None and len(self.compiled_parameters) == 1: @@ -242,8 +248,9 @@ supports_pk_autoincrement = False default_paramstyle = 'named'

- def __init__(self, use_ansi=True, auto_setinputsizes=True, auto_convert_lobs=True, threaded=True, allow_twophase=True, **kwargs): + def __init__(self, use_ansi=True, auto_setinputsizes=True, auto_convert_lobs=True, threaded=True, allow_twophase=True, default_arraysize=None, **kwargs): default.DefaultDialect.__init__(self, **kwargs) + self.default_arraysize = default_arraysize self.use_ansi = use_ansi self.threaded = threaded self.allow_twophase = allow_twophase