Hey, i did a test included below with this output: $ python cxotest.py Setting arraysize to 1 before execution yields 25.8921508181 Setting arraysize to 500 before execution yields 0.26524348765 Setting arraysize to 1 after execution yields 25.8829982582 Setting arraysize to 500 after execution yields 25.8650820146
/* sql code for the table */ begin for i in 1..10000 Loop insert into testtable (id, name) values (i, 'abcde'); end loop; end; #python code################################# from cx_Oracle import connect, Cursor conn = connect('username', 'password', 'testaccount') curs = Cursor(conn) def fetchdata_preset(sql, arraysize=1, n=1000): curs.arraysize = arraysize curs.execute(sql) curs.fetchmany(n) def fetchdata_postset(sql, arraysize=1, n=1000): curs.arraysize = 1 #make sure this is set back to default curs.execute(sql) curs.arraysize = arraysize curs.fetchmany(n) if __name__ == '__main__': from timeit import Timer sql = "\'select * from testtable\'" t1a = Timer("fetchdata_preset(%s,1,1000)" % sql, "from __main__ import fetchdata_preset") t1b = Timer("fetchdata_preset(%s,500,1000)" % sql, "from __main__ import fetchdata_preset") t2a = Timer("fetchdata_postset(%s,1,1000)" % sql, "from __main__ import fetchdata_postset") t2b = Timer("fetchdata_postset(%s,500,1000)" % sql, "from __main__ import fetchdata_postset") statement = "Setting arraysize to %s %s execution yields %s" print statement % (str(1), "before", str(t1a.timeit(1))) print statement % (str(500), "before", str(t1b.timeit(1))) print statement % (str(1), "after", str(t2a.timeit(1))) print statement % (str(500), "after", str(t2b.timeit(1))) On May 27, 1:21 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---