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

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

On Thu, 2004-01-15 at 09:32, Moore, Paul wrote:
> From: Anthony Tuininga [mailto:an...@co...]
> > Could you explain about the "cursors only used from a single
> > thread" problem is?
> 
> Sorry. Basically, I'm running a query against lots of databases,
> and storing the results in a central database. So I have a single
> connection to the central DB, against which I'm running a series
> of inserts.

Ok, that makes sense now. :-)

> Hmm, maybe I should be sharing the central *connection* between
> the multiple threads, and creating individual cursors to do the
> inserts on a per-thread basis. Is that OK? (Am I reading the
> documentation right that I can share a connection, but not a
> cursor?)

Yes, you can share the connection, but don't try sharing a cursor,
unless you like segfaults, of course. :-)
> 
> > 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?
> 
> It's passing the data back to a central update thread that hurts.
> Mucking about with queues, knowing when the threads have completed,
> etc :-(

Although you __can__ create a separate cursor for each thread and
perform the insert in each separate thread, if you have a large number
of them, this can hurt you. At that point, the queue will work in your
favor because the prepare() phase can be skipped. The difference is
probably a few milliseconds per thread so it is significantly smaller
than the connection overhead (at least 35 ms). Your call. :-)

> > This code:
> [...]
> > 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. :-)
> 
> Thanks for the offer. I've managed to find a lot of thread pitfalls
> myself :-)

They're amazingly easy to find, eh? That's why I've written a few simple
classes to manage such things as queues and the like so that I don't
have to think about it each time. :-)

> How likely is the following sketch to work? (I'll try it, but one of
> the things I have learned is that running a threaded program twice
> exposes two different sets of bugs :-))
> 
> cx_Oracle.OPT_Threading = 1 # I think I need this?

Yes, you definitely need this. Without it, your program will crash and
burn rather quickly. It turns on Oracle threading mode. I've considered
making this a part of the connect() method but it really does need to be
global. Perhaps another method might make it more clear. Something like
this, perhaps?

cx_Oracle.setthreaded()

Any thoughts on that?

> def PerformQuery(db, cn):
>     connection = cx_Oracle.connect("user", "pw", db)
>     cursor = connection.cursor()
>     cursor.execute("some_query")
>     ins = cn.cursor()
>     insert_data = process(cursor.fetchall())
>     ins.executemany("insert_sql", insert_data)
> 
> cn = cx_Oracle.connect("user", "pw", "centraldb")
> threadlist = []
> for db in listOfDbs:
>     thread = thread.start_new_thread(PerformQuery, (db, cn))
>     threadlist.append(thread)
> 
> for thread in threadlist:
>     thread.join()
> 
> cn.commit()
> cn.close()

Yes, this should work. Of course, you might want to decide whether you
want to use the threading module or the thread module and then make the
necessary changes. :-)

> Paul
-- 
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