Re: ADO Connection.Execute Method leaves Open SQL Process
- From: Joe Cool <joecool1969@xxxxxxxx>
- Date: Thu, 30 Jul 2009 07:58:40 -0700 (PDT)
On Jul 29, 3:23 pm, "Ralph" <nt_consultin...@xxxxxxxxx> wrote:
"Joe Cool" <joecool1...@xxxxxxxx> wrote in message
news:b8d8dde0-f1f1-49d3-b2cb-96dfdd073b0b@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jul 29, 9:56 am, "Ralph" <nt_consultin...@xxxxxxxxx> wrote:
"Joe Cool" <joecool1...@xxxxxxxx> wrote in message
Definitely a multi-user app with multple boxes. What makes it
sound otherwise?
ha, your first paragraph where it talks about "a VB6 app".
but ... nvm as we are likely going into other waters... lol
Have the App create a single ADODB.Connection object (New),That's a very good suggestion but unfortunately would require
then .Open and .Close it on demand, but do NOT destroy it
(Set to Nothing) until the App exits. This will insure the App is
requesting only one connection from a single ADO Connection
Pool, thus if all requests are sequenced and not over-lapping
- from the App's point of view only one connection will be ever
be requested from the Pool.
some major work as to the complexity of this app and my
boss is not likely to approve such a major overhaul at this time.
Then you may be screwed. <smile>
The best way to manage connections with ADO is to use the Connection Pool
and thus your first objective has to be to insure that each App uses the
Connection Pool in the most optimal manner. Period.
The connection pool is created when you create an ADODB.Connection object..
You get a pool and one connection from the pool. As long as your App makes
requests in an orderly fashion - one after another without 'over-lap'
(something in a loop, or mulitple calls to Execute in the same routine),
then the App will be able to use a single 'ADO connection' through-out its
lifetime.
Also note that an ADO Connection (not the object) is only open while it is
being used - ado connections close after 2secs to 2 minutes of inactivity
anyway. Telling the connection object to close a connection is allowing the
object to close it sooner.
Creating an destroying an ADODB.Connection adds nothing to the solution but
additional overhead.
Even after you insure this - there is likely something else going on that is
the real cause and you will have to modify your code to adapt to it.
However, SQL Server itself often creates additional "connections" tosatisfy
a request. In which case there may be little to nothing you can do aboutit
from the App's side of things.
Also, if not using the return, use the adExecuteNoRecords option.
Tried that, didn't help.
It might be useful to see your connection string and attributes, and a
sample query
Typical code:
Dim cn as Connection
With cn
.Provider = "MSDASQL"
.ConectionString = "User Id=username;Password-password;Data
Source=dsnname;"
.Open
End With
' single stepping through the code and checking the SQL Activity
Monitor shows no new process created at this point!!
cn.Execute "INSERT INTO tablename ( columna ) VALUES
( 'somevalue' )", , adExecuteNoRecords
' at this point a new SQL process HAS been created!!
cn.Close
Set cn = Nothing
' SQL Process STILL exists!!
==============================================
==============================================
That is easy enough to fix.
Just create a single global reference to one ADOD.Connection object.
Run that "With cn" block once at the start of your program, then Set to
Nothing at the end. Keep the .Open - to test the connection then .Close it.
In your code before you make any connection request do a ...
cn.Open
.... ' do your stuff, then ...
cn.Close
Delete all occurances of "Set cn = Nothing".
Why? Again because after you have done that we know each App is using only
one ADO Connection. If your app is not deliberating requesting multiple
connections - then it is not ADO that is the problem.
Note: Setting a cn object to Nothing, while usually is quite deterministic
from a practical view, is not absolutely going to destroy everything at that
very sec. As ADO runs outProc - there can be an delay on a busy system,
though that is usually an exotic case.
Your real problem is most likely because SQL Server is opening up additional
implicit connections (not pooled) and holding them open - because it is
receiving multiple pending requests. Changing the Queries to SPs that don't
produce implicit default recordsets may help.
On an extremely busy system, reworking the Apps to queue messages to an
external application which does the actual communication with SQL Server may
also help to cutdown the number of actual connections.
You know, most of the time, when the connection is closed and disposed
in this VB6 app (which happens a LOT), the SQL Process in the
ActivityMonitor goes away.
But it is in this particular case, when making use of the
Connection.Execute method where it doesn't.
And what is even strnager is that the SQL Process doesn't show up the
ActivityMonitor when the connection is opend, it shows up just after
the Connection.Execute method has been invoked.
But you do make some excellent suggestions. I will pass that on, but I
am still mystified why the active SQL Process created by the
Connection.Execute method will not go away after the connection is
closed and disposed.
.
- Follow-Ups:
- References:
- ADO Connection.Execute Method leaves Open SQL Process
- From: Joe Cool
- Re: ADO Connection.Execute Method leaves Open SQL Process
- From: Ralph
- Re: ADO Connection.Execute Method leaves Open SQL Process
- From: Joe Cool
- Re: ADO Connection.Execute Method leaves Open SQL Process
- From: Ralph
- ADO Connection.Execute Method leaves Open SQL Process
- Prev by Date: Re: ADO Connection.Execute Method leaves Open SQL Process
- Next by Date: Re: ADO Connection.Execute Method leaves Open SQL Process
- Previous by thread: Re: ADO Connection.Execute Method leaves Open SQL Process
- Next by thread: Re: ADO Connection.Execute Method leaves Open SQL Process
- Index(es):
Relevant Pages
|
