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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
