Multithreaded and SQL connections

[cj]

I have a multithreaded transaction processing app that now needs to
write to a sql db. I assume I will have to make the connection in the
main program and then each thread/transaction would create it's own
command and execute it? Is this correct?

 

[Guest]

I have a multithreaded transaction processing app that now needs to
write to a sql db. I assume I will have to make the connection in the
main program and then each thread/transaction would create it's own
command and execute it? Is this correct?

Each thread typically has it's own connection to the database... because
connections can only process one command at a time. If you share one
connection across threads, you're going to bottleneck your application.

 

[cj]

In this app a thread is created to handle an incomming TCP/IP
connection/request. Part of processing this request currently involves
making a web request to a webserver. After the web server responds and
a bit of extra manipulation is done the thread responds to the TCP/IP
client and terminates.

The web server is being replaced by a stored procedure. Since speed
through the web server is a problem--these threads should live no longer
than 5 seconds. I don't think we can afford to make a seperate
connection to the sql server in each thread.

What do you think?

 

[Guest]

The web server is being replaced by a stored procedure. Since speed
through the web server is a problem--these threads should live no longer
than 5 seconds. I don't think we can afford to make a seperate
connection to the sql server in each thread.

What do you think?

Should be fine - .NET actually uses a connection pool to handle database
connections.

After a connection has been opened an a command processed, the connection
gets placed into the connection pool. After 30 seconds of idle time (or
perhaps longer) the connection is terminated.

So if you have several requests requiring connections, it'll fetch a pre-
existing connection from the pool, if non-exists a new connection is
created.

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/connectionpooling
..aspx

http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html

 

[cj]

I've written it to make the connection in each thread as per your advise
and some info I've been able to find. They aren't ready for me to put
it into production now but I have tested the response time and it seems ok.