Re: [cx-oracle-users] Creating a large number of database connections

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

Oracle's threading overhead is about 10-15% so this likely will not be a
huge issue. Could you explain about the "cursors only used from a single
thread" problem is? I am assuming here that you have one connection per
thread and therefore one cursor per thread so there shouldn't be a
problem here, right?

This code:

for db in listOfDbs:
    connection = cx_Oracle.connect("user", "pw", db)
    cursor = connection.cursor()
    cursor.execute("some_query")

will definitely be slower than this code:

def PerformQuery(db):
    connection = cx_Oracle.connect("user", "pw", db)
    cursor = connection.cursor()
    cursor.execute("some_query")

for db in listOfDbs:
    thread = thread.start_new_thread(PerformQuery, (db,))

Of course, you need to add synchronization code which is completely
missing but I can help you with that, if needed. I've written a few
threaded programs and I know the pitfalls that can suddenly open up
beneath your feet. :-)

On Thu, 2004-01-15 at 04:54, Moore, Paul wrote:
> I have a program which needs to connect to a large number of
> databases. Each connection is fairly short-lived (run a single SQL
> query), but the connect times can be quite long, particularly as some
> databases may be inaccessible due to network issues, and so there's a
> long wait for timeouts.
> 
> What I'd like to do is to create all of my connections "in parallel",
> and then run the queries as the connections are ready. This would
> reduce the runtime of my application from the sum of all the connect
> times (plus a fixed overhead) to the maximum connect time (plus a
> possibly different fixed overhead).
> 
> At the moment, I'm doing this using multiple threads, but the extra
> code needed to handle the threads, and to make sure that cursors are
> only used from a single thread, is non-trivial. And there's also
> Oracle's threading overhead.
> 
> Is there a simpler way of doing this, that I am missing? I need a
> solution which will handle 100 or so connections, and will scale
> reasonably sensibly. My initial thought was that some form of
> non-blocking connect might work (I'm not an OCI expert), but when I
> scanned the OCI manuals, it doesn't seem like OCI lets you do any
> other database work while a nonblocking connect is in progress.
> 
> Can anyone suggest anything?
> 
> Thanks,
> Paul Moore.
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: Perforce Software.
> Perforce is the Fast Software Configuration Management System offering
> advanced branching capabilities and atomic changes on 50+ platforms.
> Free Eval! http://www.perforce.com/perforce/loadprog.html
> _______________________________________________
> 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