Re: [cx-oracle-users] Help with fetchraw (performance results)
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] Help with fetchraw (performance results)
|
From: Chris D. <cdu...@ya...> - 2005-05-26 10:59:18
|
My results based on selecting 100k rows (via a join with another table) from a 13 col table into
an identical table are (CPU figures only, elapsed times have too much "noise" due to disk
performance etc):
1. Original using fetchmany()
Python CPU: 12.1 secs
Oracle CPU: 6.8 secs
2. New approach using fetchraw() [special restricted fast method (see below) that happens to be
what I need - YES!]
Python CPU: 1.8 secs
Oracle CPU: 7.0 secs
So as you can see fetchraw() is vastly superior.
Test environment:
Athlon AMD XP2200+
Suse 8.2 with 2.4.22 kernel
Oracle 9.2
Python 2.3.4
cx_Oracle 4.1
Thanks very much for your help Anthony and the examples, they really helped
Chris
--- Chris Dunscombe <cdu...@ya...> wrote:
> > > > --- Anthony Tuininga <ant...@gm...> wrote:
> > > > > 1) You should be able to do something like this without the need to
> > > > > descend into fetchraw().
> > > > >
> > > > > import cx_Oracle
> > > > >
> > > > > fromConnection = cx_Oracle.Connection("user/pw@tns")
> > > > > toConnection = cx_Oracle.Connection("user/pw@tns")
> > > > > fromCursor = fromConnection.cursor()
> > > > > fromCursor.arraysize = 250 # set as desired
> > > > > toCursor = toConnection.cursor()
> > > > > toCursor.arraysize = 250 # set as desired, same as fromCursor
> > > > >
> > > > > fromCursor.execute("some query")
> > > > > toCursor.setinputsizes() # set things up if nulls are allowed
> > > > > while True:
> > > > > data = fromCursor.fetchmany()
> > > > > if not data:
> > > > > break
> > > > > toCursor.executemany("some insert statement", data)
> > > > > toConnection.commit() # if desired
> > > > >
> > > > > That should do it and should perform quite well. The fetchraw(),
> > > > > bindvar.copy() and executemanyprepared() trio were created solely to
> > > > > avoid the overhead of creating Python objects for the data. Its more
> > > > > complicated than the above code but I can give that as an example as
> > > > > well if you really want it.
> > > > >
> > > >
> > > > I've done essentially the same as you've got above and yes it works fine with pretty good
> > > > performance. I'm just trying to improve it further as there are cases when the app will be
> > running
> > > > a number of parallel streams each selecting and inserting millions of rows in a fixed
> window
> > so
> > > > saving 5% - 15% of the Python CPU time within this loop is definately worth doing. I'd
> like
> > a
> > > > fetchraw example so I can benchmark it against my current approach. I'll then post the
> > results so
> > > > everyone can see the general sort of performance differences between the two.
> > >
> > > Another method which also works but only when the exact data is being
> > > replicated from one cursor to another is the following. It is faster
> > > than both methods but limited in usefulness of course.
> > >
> > > import cx_Oracle
> > >
> > > sourceConnection = cx_Oracle.Connection("user/pw@tns")
> > > sourceCursor = sourceConnection.cursor()
> > > sourceCursor.arraysize = 250
> > > sourceVars = sourceCursor.execute("select * from testdata_source")
> > >
> > > targetConnection = cx_Oracle.Connection("user/pw@tns")
> > > targetCursor = targetConnection.cursor()
> > > targetCursor.execute("truncate table testdata_copy")
> > > targetCursor.arraysize = 250
> > > targetVars = targetCursor.setinputsizes(*sourceVars)
> > > targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
> > >
> > > while True:
> > > rowsFetched = sourceCursor.fetchraw()
> > > print "fetched", rowsFetched, "rows"
> > > if not rowsFetched:
> > > break
> > > targetCursor.executemanyprepared(rowsFetched)
> > > targetConnection.commit()
> > >
> > > Hope this helps,
> > >
> > > Anthony
> > >
> > >
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_idt12&alloc_id344&opclick
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
>
>
> __________________________________
> Yahoo! Mail Mobile
> Take Yahoo! Mail with you! Check email on your mobile phone.
> http://mobile.yahoo.com/learn/mail
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
|
