| Adam Flint 2006-02-15, 3:23 am |
| I have a .NET application (actually MCMS, but I think that's irrelevant)
that access a SQL Server database using Microsoft Data Access Blocks (DAB).
Consider the following pseudo-code:
For i = 1 To 10000
SqlHelper. ExecuteNonQuery(strC
onnection, storedProcType, storedProcName,
params)
Next
This code will cause the classic ODBC timeout error every time after a few
iterations. Now consider this pseudo-code:
Dim oConn as New SqlConnection(strCon
nection)
oConn.Open
For i = 1 To 10000
SqlHelper. ExecuteNonQuery(oCon
n, storedProcType, storedProcName, params)
Next
oConn.Close : oConn.Dispose
This works fine. But what's the difference? I've been into the code of DAB
and every call to ExecuteNonQuery does close and dispose the connection.
So my guess is that in the first example, something is not letting go of the
connections fast enough. Yet if I look on the SQL server, there are
virtually no active connections. So where are they being held? What's
causing the timeout?
Now this might seem like an extreme example, but the actual problem I am
having with our application is that in a single HTTP request there is a lot
of database access going on, mostly using the ExecuteNonQuery method in
different functions and event handlers all over the application. The
application is pretty big, it's an Intranet solution for a global financial
services organisation. We've just added a new module that executes every
time an HTTP request is coming in, and we are now getting the timeout errors.
Anyone else experienced this?
|