Post reply

Multi threaded application and database user count

  • December 17, 2002 at 9:52 am

    #58217

    Hi,

    We are in the process of re-writing an existing package to be multi-threaded. I am wondering if I have multiple threads writing and reading from a sql (or oracle) database am I burning a user connection for each thread? Or am I going to be controlled and only allow the number of threads that equals my user count?

  • December 17, 2002 at 10:10 am

    #444605

    I guess this depends on the way you want to implement this.

    You can create a connection in each thread or you can create a single connection and share that over different threads.

    If I had the choice, I would opt for a shared connection. This reduces the overhead of creating all different user connections. A hybrid approach, using a 'pool' of connections is also possible, but this will make your app a lot more complex and most middlewares do support some kind of connectionpooling.

    Anyway, a lot depends on the development environment you are using. Make sure you are multi-threading safe if you are sharing connections.

    • I know some ODBC drivers are NOT safe. All 'decent' drivers are, so you should be safe for SQL Server and Oracle.
    • Don't know about ADO, DAO and the like. But again, you should be safe there. If you are using any other framework, you will have to check this.
    • Also, bear in mind that if you are using a single connection, temporary tables might be a problem, depending on your implementation.

  • December 17, 2002 at 10:55 am

    #444614

    One connection or more really depends on what you're trying to do. If you've got a long process you need to run, makes sense to open an extra connection for it. Long might be five seconds or five minutes, depending!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    Connect with me on LinkedIn

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply