ADP vs. MDB: Speed

[Neil]

Alex,

Thank you for your very helpful post. A few comments below.

the conclusion I
have drawn is this. If you want a database of < 100mb in size the MDB
method works very well, as soon as you are in a large database > 200mb
100+ tables 100+ forms then adp style projects seem to be better

The database file itself is about 200-270 mb; but it doesn't have that many
tables. There are two main tables, which make up most of the data; a few
medium-sized tables; and a slew of small lookup tables -- roughly 80 tables,
though I'd say that at any given time less than 10 are in use by any single
user.

as the
maximum underlying connections to a DB is 2048 and linked MDB/ODBC
tables require 2 connections for every table/combo box/and other table
style connections and ADP/SQL tables only require 1. I have only hit
this limitation 3 times in my 10+ years developing access applications,
but when I hit it with the MDB I have only one choice convert to an ADP
project.

As noted, there aren't a lot of tables in use at any given time. And our
most complex form has maybe 40 combo boxes. So we don't seem to be
approaching this limit.

But, be that as it may, the situation I noted in this thread regarded a view
which was fast in an ADP file, and used to be just as fast in an MDB file;
but now is slow in the MDB file. This situation occurs:

* when I use the database files on my unnetworked PC running MSDE with no
other apps running;

* when the MDB file is open without any forms or reports open;

* when the linked view is opened by itself from the database window without
a form.

As noted previously, it takes about 5 seconds just to bring up a screen of
about 20 records, and each time the scroll bar is clicked (even when moving
down a single record), the same or a similar delay occurs.

This same view was working fine until recently, and was used for several
years as the recordsource for a form that edited it in Continuous Forms
view. I added a few fields to the view, and things slowed down. I tried
taking the fields out, but the results are the same. (The adding of the
three fields may have been coincidental to something else that may have
caused the slowdown.)

Either way, the view is still fast in an ADP file, but is slow as molasses
in its original MDB file.

I
keep hearing of problems with ADP projects I have not had any major
problems with these and I have one site running an ADP (access
2000)/SQL7 application on 150+ terminals I have not had to fix or get
involved in anything for 4 years.

That's good to know. One of the things I've been discussing here is that
this database is going to be rebuilt from the ground up (was converted to
Access from an old database program by a developer, then was modified by
another developer who didn't understand principles of normalized design,
then was inherited by me). I am leaning towards redoing it in MDB,
especially given the many testimonies of problems people have had with ADP.
But your input is certainly significant here as well. So that's good to
know.

The most important thing to me is db
design with as much of the program logic held in the database and not
the front end.

Yeah, well, here the whole thing is in the back end. The whole idea is that
SQL Server does all the work and the MDB just has to get the data. But,
apparently, it has problems even with just that!

A couple of thing that I have done to improve query
performance is
never use 'Select * from' and to trim the field length of long fields
if the results are going to a list box 'SELECT
Convert(char(30),rsContacts.Name_Of_Solicitor)' and to add the
following to the end of those line 'FOR BROWSE' which seems to help
with locking on the server.

I didn't see a "FOR BROWSE" option in the SQL 7 BOL. Is that a SQL 2000
option?

Thanks,

Neil

 

[Neil]

Yeah, I sort of gave up on the discussion. But, if you want to post some
more, I'll go ahead and start a new thread. Will reply here again with the
name after I post it.

Thanks!

Neil