Re: [cx-oracle-users] Help with fetchraw
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] Help with fetchraw
|
From: Chris D. <cdu...@ya...> - 2005-05-17 09:54:19
|
--- 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.
> 2) I'm not sure exactly what you are intending with this so I can't
> see why the simple
>
> for row in cursor:
> process_the_row()
>
> isn't going to work or isn't desirable. Perhaps you could give more
> detail? Thanks.
The "problem" with:
for row in cursor:
process_the_row()
is that process_the_row() is essentially:
for col in row:
rowOut = rowOut.append(format_col(col))
rowOutString = "".join(rowOut)
which creates quite an overhead when the tables have lots of columns (>40).
So I'm looking at anything that could help speed things up (it's quite possible that in this case
fetchraw() won't make much of an impact compared to the process_the_row() code) This is why I'm
looking the None as Null returning "" stuff so as to reduce the complexity of "format_col()" and
hence CPU cost.
BTW I've actually put the process_the_row and format_col() code in-line as per
http://www.python.org/doc/faq/programming.html and associated performance guidelines to avoid the
function call overhead and this reduced the Python CPU usage by almost 20% on my standard perf
test.
Thanks again for your much appreciated help,
Chris
> On 5/12/05, Chris Dunscombe <cdu...@ya...> wrote:
> > I'm looking to use cursor.fetchraw to help improve performance in an app I'm writing. I've
> looked
> > at CopyData from cx_OracleTools but I'm afraid I couldn't work it out.
> > If possible I'd like a simple full example to show me the way. The context is:
> >
> > 1) Retrieve rows from source table and insert into a target table which has exactly the same
> > structure.
> >
> > 2) Retrieve rows from source table, add some column formatting and write out to a file.
> >
> > Thanks very much,
> >
> > Chris Dunscombe
> >
> > PS Thanks for all the input re my previous posting on Nulls and None. I've still got some
> thinking
> > and work to do on which is best for performance and how best to cope with different datatypes,
> > particularly Longs (what a pain they are!!)
> >
> > __________________________________
> > 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=7393&alloc_id=16281&op=click
> > _______________________________________________
> > 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
|
