So, I think i'll probably implement the patch I posted earlier and
actually default arraysize to 50, although I have a feeling it might
mess around with some of the BLOB-oriented functionality (since BLOBs
must be fetched at the point of receiving the row, else the cursor
moves on)..if thats the case I'll try to keep it as an option.
On May 27, 2008, at 5:34 PM, gniquil wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
