Re: [cx-oracle-users] How to optimize (Pro*C is 5x faster)?

[Anthony T. <an...@co...>]

A few points:

a) C is always going to be faster than Python when coded using the same 
algorithms and data structures; in general the advantage of Python is 
the ability to use advanced algorithms and data structures with little 
effort compared to writing the same code in C; I've noted that there is 
about a 10-15% performance penalty for writing code in Python for the 
stuff I've written (although clearly it depends on what has been 
written) but I've also noted that due to the ability to write more 
advanced algorithms in Python some code I originally wrote in C++ was 
actually slower than the original code written in C/C++.

b) The code "compiled_curs.execute(None, row[0], row[2]) is not valid 
since the execute() method only accepts one argument (a list of 
parameters) or a set of keyword parameters; what is going on here??

c) Is there any reason you didn't use an iterator? Using the code "for 
row in curs:" is more efficient than using the method fetchmany() and is 
considerably easier to read as well. I've never checked the difference 
in performance, though -- the timeit module is your friend.... :-)

d) Are you actually doing anything with the rows from the first cursor 
other than using them as input to the second cursor? If so, you'd be far 
better off to create a view that put those two queries together -- the 
performance increase will be quite significant.

e) If you really need performance and don't mind deviating from the DB 
API to get it, there are a number of methods that can be used to avoid 
the overhead of creating result tuples and binding parameters. This will 
definitely bring the performance numbers more in line with the Pro*C 
numbers but at the cost of less portable code.

f) What kind of times are we talking about here? Are you saying that one 
method is 50 ms and the other is 10 ms? Or is it 50 minutes and 10 
minutes? In other words, is this an operation that is being performed 
many times? 40 ms is not a big deal if it happens a few times but a big 
problem if it happens a few million times, right?

So, to summarize: I need more information in order to determine whether 
this is a "real" performance problem or not. If you could give me some 
actual code that I can execute to perform my own testing, that would be 
great.

Christopher J. Bottaro wrote:
> I have some code structured like this:
> 
> curs.execute(some_sql)
> rows.fetchmany()
> while rows:
>         for row in rows:
>                 compiled_curs.execute(None, row[0], row[2])
>                 more_rows = compiled_curs.fetchmany()
>                 while more_rows:
>                         #do something with them
>                         more_rows = compiled_curs.fetchmany()
>         rows = curs.fetchmany()
> # arraysize is set to 3000 for both cursors
> 
> The problem is that it is so slow.  Yes, I realize that its not good to
> execute sql in a large for loop, but my problem is that this code is
> something like 5x as slow as the corresponding Pro*C code.
> 
> Whats going on?  Am I just not using the cx_Oracle module efficiently?  Why
> is embedding sql in Pro*C code *so* much faster.
> 
> Thanks for the help, I hate coding in Pro*C and C ever since I learned
> Python...please tell me I'm doing something wrong, I'd hate to go back to
> C!
> 
> P.S.  I'm using cx_Oracle-4.1-beta1.
> 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com