MS SQL Server ODBC - ADP vs. MDB: Speed

Author

ADP vs. MDB: Speed

Neil

2005-04-22, 3:23 am

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),rsC
ontacts.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

>
> Just my thoughts.
>
> Alex
>
>
>
>
>
>
>
> Neil wrote:
> would be
> the
> across
> message
> is only
> is, when
> option
> record
> or
> record
> SQL
> help:
> ODBC,
> SQL
> Access
> displayed
> the
> it's
> eventually work
> records
> records
> brought in.
>
> noted, this
> of a
> my
> form
> and the
> Recordset
> the
> you
> until
> that mean
> level. And
> well as
> turned on.
> than
>

Neil

2005-04-22, 3:23 am

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

"david epsom dot com dot au" < david@epsomdotcomdot
au> wrote in message
news:udHkREjPFHA.2136@TK2MSFTNGP14.phx.gbl...
>I don't see that you have reposted as a I suggested?
> I don't think that the MDB subject is exhausted, but I
> don't want to continue posting in an ADP thread if you
> have start a newer, more relevant thread.
>
> (david)
>
>
>
> "Neil" <njones@pxdy.com> wrote in message
> news:1Z_4e.1943$An2.669@newsread2.news.pas.earthlink.net...
>
>

Sponsored Links