Re: ado connection object in vb6

Ok, now I am confused. One person recommends local objects, you recommend
global objects.....

The basic model that I have used inside the dll is that when I need to
access the database I create a string variable and write the SQL. Then I
use a local adodb.recordset object (lets call it objRS) and I code a line
objRS.Open ("SQL string", global ado.connection).

I populate the record data into the object I am dealing with, say a
customer, and then I close the recordset and set it to nothing

WB


"Ralph" <nt_consulting64@xxxxxxxxx> wrote in message
news:OZOPIqKpIHA.3900@xxxxxxxxxxxxxxxxxxxxxxx

"wb" <none> wrote in message news:u2V0q0IpIHA.4616@xxxxxxxxxxxxxxxxxxxxxxx
I am working through some performance issues with regards to Insert and
Update procedures using a global connection object. During the first
lifecycle of my project, I used an activex dll to store all my classes
and
within each class they use this global connection object to interface
with
the database (SQL Server). On the most recent update I needed to use the
global connection object within my exe project so I just created a second
global connection object to the same DSN as the activex dll. So I
basically
have two, one for all the classes within the dll and one for all the
other
"needs" within the exe. Both are created within modules at the time the
program launches.

The end result, the application slows way down when doing any type of
insert
or update. Is there a limit on the number of connection objects? Should
the objects be global or local variables?

wb


There is little wrong with a 'global' connection object in a VB6
application. In fact it is preferred over the over-head of continuously
creating and destroying an ADODB.Connection object. ADO uses Connection
Pooling. Opening and closing a Connection object just before use is a
useful
practice as it helps the pool to be more efficient.

I doubt that creating two Connection objects is an issue in this case,
since
both would share the same connection pool. It wouldn't hurt to try and
just
use one. Pass the Exe's to the Dll. Again, doubt you would see much
change.
But continuously creating and destroying a Connection object will decrease
performance.

You mentioned a 'DSN'. Does this mean you are using an ODBC driver, or OLE
for OLEDB? If you are, then immediately change your connection to use an
OLE
DB provider. Depending on SQLServer version you may want to use the native
provider. Likely not a complete cure, but should help.

There is a performance issue with SQLServer in that it can only open one
for
ward-only server-side cursor per connection at one time. So multiple
requests can 'stack-up' so to speak. Not sure how this might relate your
problem. You might piddle around with different cursor types, etc. for
separate commands. You might be having locking issues.

Migrating to a MTS model is only useful if you are in fact using
Transactions.

But then how are you doing your inserts and updates? Some techniques are
more efficient than others. Just adopting more stored procedures is likely
to add a magnitude of improvement.

We need more details.

-ralph















.

Relevant Pages

  • Re: EventSink didt unload
    ... > After that the DLL did not unload after one minute. ... > I do a Closeon the created record and connection object. ... > More confusing is the fact that if I call Testafter that, the DLL ... Try to self-debug the way of thinking about solving problems. ...
    (microsoft.public.exchange2000.development)
  • Re: Close ports that stay open during loop of updates to db
    ... > I make "create object" to this dll from an simple exe. ... > When i do the updates in loop,the ... > Why the ports do not close after I set the object to nothing? ... Setting a connection object to Nothing does not close it, ...
    (microsoft.public.vb.database.ado)
  • Re: Prevent dll replacing
    ... > One option would be to digitally sign your dll file. ... If digital signature is ... I pass a connection object to some objects ...
    (microsoft.public.security)
  • Re: Prevent dll replacing
    ... One option would be to digitally sign your dll file. ... If digital signature is ... not what it is supposed to be, you can then terminate the program or notify ... > When several dlls are used, I pass a connection object to some objects ...
    (microsoft.public.security)
Loading