[AccessD] Terrible performance like I have never seen before

Now that we are on the subject of connection databases and I am not
interested in the bound or unbound discussion, but in defense of ADO-OLE
over ODBC.

A little preamble; 2001 and the company I was worked for crashed and burned
but I bid on a excellent project of connecting Oracle to 80-90 desktops
worth up go 100k. I used the standard ODBC connection. Performance was
acceptable for ADD and UPDATE but SELECT was so slow... Needless to say, no
excuses, I lost the contract. I fortunately, I got a second chance, with
another department and this time, after a bit of research won the contract.
The contract was for a F/T programming project which extended for almost 3
years. This time I used ADO-OLE and the performance was superior to any
application out there... nearly twice as fast as Oracle's own desktop
product.

ADO is one of the most under-rated connection products. It is free and comes
on all Windows OS since Win95, requires no installs as everyone, no matter
how far away, has it. There is none of this going from one desktop to
another to set things up. ADO also has a lot of built-in functionality; it
has the Shape object that works like a Transpose matrix... great for super
fast cross-tabs. Then the Stream object for transferring pictures, documents
and video (though I never tried the streaming video in a client's app)... at
the time it was fastest streaming method available and it could make your
network look like it had a Flash Server. ADO synchronization so that data
could be coming for multiple sources and be 'unioned' together... great for
cross-referencing data. There are many other objects that very impressive
and again they are very lean and very fast.

I must admit that ODBC is initially easier to setup but with a big or
long-term project ADO is superior in every way.

Jim

     

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, July 08, 2010 2:31 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Terrible performance like I have never seen before

Alternatively,  just upsize the data, link to the SQL Server tables by ODBC
and carry on as 
usual for a start.  Then you can work your way through the new system
upgrading a piece at 
a time.

I don't want to get the bound/unbound arguments stirred up again, but make
sure that the 
gains are worth it before you go to the dark side. :-)

-- 
Stuart

On 8 Jul 2010 at 9:10, Doug Steele wrote:

> Hi Jim:
> 
> Thanks for the details.  All my forms (300+) are bound, and I have a
couple
> of thousand queries :(   I guess the logical first step is to start
> unbinding the forms...
> 
> Doug
> 
> On Thu, Jul 8, 2010 at 8:11 AM, Jim Lawrence <accessd at shaw.ca> wrote:
> 
> > Hi Doug:
> >
> > My approach was rather straight forward but there is not real quick way
to
> > do it right hence the nearly month long odyssey to get the system moved.
> > Fortunately the client was on a monthly contract so I could spread the
work
> > out over a couple of months as they are not likely to just accept a 10K
> > bill...but as they are on a monthly contract that is exactly what they
will
> > pay. ;-)
> >
> > I installed a MS SQL on the client's site and used the Upsizing wizard
to
> > construct all the tables on the SQL and rebuilt all the queries in
Stored
> > Procedures. (I have done this before so I have all basics are pre-built
> > like: Add, Delete, Update, first, next, previous, last, goto-record...
with
> > their associated UDF (User defined function... same as a function in
> > Access)) The queries have to be rebuilt to compile with SP standards.
> >
> > The application usually has to under go a few changes.
> > No bound... that is what a SQL server is for; it does the data
management.
> > I
> > populate all the forms, reports, list and combo boxes via recordsets. I
> > usually populate a form one record at a time as it is usually so fast
that
> > the client doesn't notice any delays.
> >
> > Those recordsets are assembled through a SQL interface module. Rather
than
> > go into a page by page explanation suffice to say its sole purpose is to
> > connection with the SQL, retrieve and update recordsets through an ADO
OLE
> > interface. The only tricky parts can be from dealing with combo boxes
and
> > reports but newer versions of Access take a lot of the grunt work out.
> >
> > If you want to know more I will answer you questions one at a time.
> >
> > Jim
> >
> >
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com