Web Services, ADO.NET Connection Pool, Timeout Error

Question

If I have an .asmx Web Service that only exposes one call. One that takes a few params and inserts those values as a record in SQL Server 2005 table. What should that method look like to be as "kind" to the ADO.NET Connection Pool as possible? What should it look like if its going to be called maybe 10 times a second (spread out) over multiple clients. Any other settings I should tweak?

Currently I'm getting the error:

System.Web.Services.Protocols.SoapException:
Server was unable to process request --> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to the operation or the server is not responding.

Would the error be different if the connection pool was the problem? How can I tell if this is a connection pool issue, a SOAP limitation, or if my database is so busy that the calls are really timing out?

Answer 1

You are not posting some of the most important information about what you are doing. The fact that it is a webmethod is a red-herring, it simply comes down to a database issue and how much you are hammering it.

It would seem that you might have a connectivity issue here, as the exception states (and I don't believe it has anything to do with the pool). Are you getting this when trying to open the connection, or when trying to execute the operation? Each one has a separate timeout, so it might be a good idea to explicitly open the connection and then make the call to the operation, so you can see where the timeout is occuring.

Also, I would look to see if you are using transactions. If this is encompassed in a connection, and the timeout is on the operation, and you are using transactions, then are you using transactions to access this table anywhere else (read or write) that is wrapped in a transaction? It might be a deadlock at that point.

Barring all that, if that is not the case, then it's a connectivity issue and you have to look to see if the machine and server are configured correctly (are you using tcp ip, named pipes, or shared memory, for example) for database operations.