Thread Pooling and Database Connections

[Bernie]

I have an application where I have a list of patients and I need to perform 3 or 4 database accesses, create a report, and send a email for each. I'd like to try it with thread pooling to see if it increases the speed.

My question is about the database connection. I'm assuming I can't use one database connection and pass it to every thread due to collisions. How do I handle getting each thread a database connection?

I wouldn't make sense for each one to create and delete it's connection each time I don't think. So is there an easy way to handle this?

Thanks,
Bernie

 

[JohnH]

You need one connection instance for each simultanous db interaction, ie each thread. Connection pooling is automatic and will 'help out' when possible, more about this here: SQL Server Connection Pooling (ADO.NET)

 

[Bernie]

John,
Thanks for the reply and the link!

I've been doing some reading and experimenting since I posted and have learn alot. I came from traditional ADO where connections cost time.

I ran a test where I have 8607 patients to process and each requires two database accesses; this is all non threaded code. I ran the process with a single db connection and it took 640 seconds. I modified the code to create, use, then dispose a db connection every time it accessed the database. Running in this configuration it took 643 seconds. Very impressive!

I figure the margin of error in measuring is at least 1% so the 3 seconds doesn't matter. So my conclusion is there is virtually no penalty for creating and deleting connections at will.

John, thanks again for your help here and in the past.

Bernie

 

[JohnH]

I figure the margin of error in measuring is at least 1% so the 3 seconds doesn't matter. So my conclusion is there is virtually no penalty for creating and deleting connections at will.

The aquisition of an existing connection from the pool is quite fast, it is plausible this would add 3 seconds in total for 17000 calls. As you understand from the connection pooling article the same underlying connection to database was used each time (and socket not actually closed between calls).

When converting to multithreading be wary of the pools default connection limit of 100 actual db connections. You can't pump out too much work or else you will get exceptions caused by pool not being able to serve a new connection within time. If you use Threadpool to call the worker for each client you could limit threadpool to 100 threads (or up the connection pool limit), the workers will then queue up and not cause connection trouble later. Threadpool has a default limit of 250 thread/cpu, so this will quickly outnumber the connection pool for many worker tasks.

Also the database service has a connection limit, but for example with SQL 2000-2008 max user connections is 32767 and not likely to be a problem.

 

[cjard]

I have an application where I have a list of patients and I need to perform 3 or 4 database accesses, create a report, and send a email for each. I'd like to try it with thread pooling to see if it increases the speed.

You may find faster ways by doing the solution completely differently. If your app is performing several data accesses, it may ultimately be faster to upload any local data into the database, have the database manipulate it all, and download one lot at the end

e.g. suppose a file arrives and you have to modify several tables per line, then select some extra data from the db for each line, then send a mail

I'd bet it's much faster to uplaod your file into the db, run updates based on it, then run a join query to download the entire resultsset and send the emails

I have serveral processes that work like this in Oracle.. uploading works at about 3000 lines/second, and having all the data locally means updates are performed en masse and always within the db


Tip: when asking questions, provide additional info so that we can see if the solution you have envisaged is actually broken in some way with regards to the original problem (i.e. describe the problem youre trying to solve, not the problem with your solution)