Retrieval Performance Questions

[Don Wiss]

My Access databases are only used as data repositories for Excel to call.
Tables are either built by importing a Worksheet, or importing a text file.
As the calls to the database are many, I have a bunch questions on how to
make them as fast as possible.

I'm using Dao.DBEngine.36 to retrieve. Would ADO be faster?

The mdb files are in 2002 file format. They are much larger than if in 97
file format. Would the retrieval be faster, or slower, if I convert back to
97? The smaller 97 format would make it easier to propagate the files to
our branches.

All the tables together are 28 MB. But one of the tables has 40,000 lines
and is 27 MB of this. Some tables are tiny, with just a single record with
two columns. Is there a performance hit when retrieving the small tables
when the big table is in the same database?

I gave the big table a column that is Indexed (No Duplicates). All
retrieves will be for a single record on the indexed key. Would adding a
primary key make such calls faster?

Some tables are Indexed (Duplicates OK). Any call to these tables will be
to the indexed columns, and will want all the records with that key. Would
adding a primary key make such calls faster?

On the 27 MB table sometimes I just want the value from a single field. The
entire table has 15 fields (plus the key field). Would it be faster if I
had another table that only had that single field?

Thanks, Don <donwiss at panix.com>.

 

[Douglas J. Steele]

Answers in-line

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My Access databases are only used as data repositories for Excel to call.
Tables are either built by importing a Worksheet, or importing a text
file.
As the calls to the database are many, I have a bunch questions on how to
make them as fast as possible.

I'm using Dao.DBEngine.36 to retrieve. Would ADO be faster?

It's doubtful that ADO would be faster. DAO was designed specifically for
Jet databases, whereas ADO is a generic approach (which means more layers of
abstraction)

The mdb files are in 2002 file format. They are much larger than if in 97
file format. Would the retrieval be faster, or slower, if I convert back
to
97? The smaller 97 format would make it easier to propagate the files to
our branches.

Again, it's doubtful that changing would speed things up. Since Access 2000,
text is stored in Unicode, so it takes 2 bytes per character, rather than
the 1 byte per character that Access 97 and previous used. If you want to
try switching to 97 format, be sure to use DAO 3.5x rather than 3.6. How are
you propagating the files? MDB files tend to zip to a significantly smaller
size.

All the tables together are 28 MB. But one of the tables has 40,000 lines
and is 27 MB of this. Some tables are tiny, with just a single record with
two columns. Is there a performance hit when retrieving the small tables
when the big table is in the same database?

Not sure of the relevance of this. If a table is big, you can't make it
smaller. Denormalizing or splitting tables will lead to far more problems
than it will solve.

I gave the big table a column that is Indexed (No Duplicates). All
retrieves will be for a single record on the indexed key. Would adding a
primary key make such calls faster?

I believe all tables should have primary keys. However, an index that's set
as Indexes (No Duplicates) should perform the same as a Primary Key. And
anytime you have an index that doesn't allow duplicates, it's certainly a
candidate key.

Some tables are Indexed (Duplicates OK). Any call to these tables will be
to the indexed columns, and will want all the records with that key.
Would
adding a primary key make such calls faster?

Again, I don't see the relevance of this. The only way adding a primary key
would help would be if you looked up by the primary key. If you're currently
looking up by specific fields that are not sufficient to be a primary key,
you really have no choice but to do your lookups that way!

On the 27 MB table sometimes I just want the value from a single field.
The
entire table has 15 fields (plus the key field). Would it be faster if I
had another table that only had that single field?

Doubtful.

You're far better off having a correct data model than trying to manipulate
your model to try and gain nanoseconds of response time.

 

[Don Wiss]

Again, it's doubtful that changing would speed things up. Since Access 2000,
text is stored in Unicode, so it takes 2 bytes per character, rather than
the 1 byte per character that Access 97 and previous used. If you want to
try switching to 97 format, be sure to use DAO 3.5x rather than 3.6.

The problem with 3.5 is it is not present on new Office XP installations. I
had been using it, and got into trouble with them. I did ask that it be
added to new Office XP installs, but since decisions like that are made in
Zurich, I doubt it.

Why 3.5 over 3.6 for 97 files? I do distribute one database still in 97,
just in case someone loads up an old spreadsheet which calls 3.5. But this
being a new database would never be called by an old spreadsheet.

How are
you propagating the files? MDB files tend to zip to a significantly smaller
size.

Very true, but after the IT fellow pushes them out over a slow WAN there is
no way to expand them. It isn't a big issue. He can just let the job
process for a while.

Thanks for the response.

Don <donwiss at panix.com>.

 

[Douglas J. Steele]

3.6 assumes the text is unicode, so it would (unnecessarily) trying to do
conversions, therefore being slower.