Re: ado connection object in vb6

Ah, Jan might not have read my book(s) on ADO and managing connections. There is nothing wrong with using a single connection per application. As a matter of fact, unless this is a web site, you'll get (slightly) better performance and far more flexibility as to the application design alternatives.

However, using ODBC is not such a great idea. While there are far better approaches in use today (the SqlClient .NET native provider) OLE DB is the preferred interface for VB6 and ADO classic.

I would also look again at the application design if I was doing a code review. When someone says "performance" and "INSERT" in the same sentence I wonder if the application is trying to do something it shouldn't. Consider that ADO classic was never designed to act as a bulk operations interface (it's a query interface). If you're moving (inserting) a lot of rows, I suggest an SSIS or bulk copy approach. If you're doing a lot of synchronization work, I suggest a replication approach. VB.NET supports both of these in ways that are dramatically simpler than ever before. Even without VB.NET you can still implement these more sophisticated technologies to let SQL Server do what it knows how to do without reinventing the wheel.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"wb" <none> wrote in message news:uclYg7opIHA.2636@xxxxxxxxxxxxxxxxxxxxxxx
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: Trapping ADO Connection Errors
    ... classes for the database function, common methods, one long procedure. ... Are you aware of the Error collection used by ADO: ... > 1) I create ADO Connection object and successfully log into SQL Server. ...
    (microsoft.public.vb.general.discussion)
  • Re: Trapping ADO Connection Errors
    ... classes for the database function, common methods, one long procedure. ... Are you aware of the Error collection used by ADO: ... > 1) I create ADO Connection object and successfully log into SQL Server. ...
    (microsoft.public.vb.database)
  • Re: Trapping ADO Connection Errors
    ... classes for the database function, common methods, one long procedure. ... Are you aware of the Error collection used by ADO: ... > 1) I create ADO Connection object and successfully log into SQL Server. ...
    (microsoft.public.vb.database.ado)
  • Re: ODBC/OLE DB Connection Pool
    ... > connection be kept open for the application as this will serialize all ... threads ONCE they are returned to the pool. ... > Tips for ADO Users ... > The ADO Connection object implicitly uses IDataInitialize. ...
    (microsoft.public.data.ado)
  • Re: ODBC/OLE DB Connection Pool
    ... > connection be kept open for the application as this will serialize all ... threads ONCE they are returned to the pool. ... > Tips for ADO Users ... > The ADO Connection object implicitly uses IDataInitialize. ...
    (microsoft.public.inetserver.asp.db)
Loading