Optimizing Access db running over a network

[AndyCotgreave]

Hi,
My company has an Access 2003 database running over a network. The
database is split into front and back end (each user has a copy of the
front end on their own machines, linking to the tables in the central
back end).

It's all getting incredibly slow. Speed is fine when ONE user is
accessing the database, but as soon as we get everyone (20 users)
accessing it, it runs slowly.

I am now going to try optimize wherever I can.

1. Indexes - I read somewhere about how "well-designed indexes" can
speed up a database. I've got indexes on all the commonly searched
fields. Are there any other tricks to designing indexes?

2. Record size. Our main table, Companies, has grown to 75 fields.
Normalisation has been a bit lost in the history of this database. I've
never seen it explicitly said, but I would assume that if you reduce
the size of a table record, things get quicker? The main bugbear for
users is that when they search for a table (the most common task), it
is taking up to ten seconds to report back. If I reduced the size of a
company record by, say, 20%, would it make a difference?

I would appreciate any pointers to correctly designing indexes and the
impact of record size on searching speeds.

At what point should we be thinking of migrating to a better database
solution (SQL Server?)

Andy

 

[Jeff Boyce]

Andy

Comments in-line...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hi,
My company has an Access 2003 database running over a network. The
database is split into front and back end (each user has a copy of the
front end on their own machines, linking to the tables in the central
back end).

It's all getting incredibly slow. Speed is fine when ONE user is
accessing the database, but as soon as we get everyone (20 users)
accessing it, it runs slowly.

I am now going to try optimize wherever I can.

1. Indexes - I read somewhere about how "well-designed indexes" can
speed up a database. I've got indexes on all the commonly searched
fields. Are there any other tricks to designing indexes?

Indexes can help on the fields you use as criteria, the fields you use in
joins in queries (unless you've already use relationships to pre-join), and
on the fields you use to sort.

2. Record size. Our main table, Companies, has grown to 75 fields.
Normalisation has been a bit lost in the history of this database. I've
never seen it explicitly said, but I would assume that if you reduce
the size of a table record, things get quicker? The main bugbear for
users is that when they search for a table (the most common task), it
is taking up to ten seconds to report back. If I reduced the size of a
company record by, say, 20%, would it make a difference?

Access is designed to be used as a relational database. If your table
design is more like a spreadsheet (and it might be, with 75 fields), you
won't be getting the best performance. Take another look at normalization,
post back to the tablesdbdesign newsgroup.

I would appreciate any pointers to correctly designing indexes and the
impact of record size on searching speeds.

At what point should we be thinking of migrating to a better database
solution (SQL Server?)

You didn't indicate the size of the data file, the speed of the network, the
half/full duplex of the NICs, the horsepower of the PCs, the primary use
(data entry vs. looking up records) ... these are factors, too.

Your "better database solution" doesn't, in and of itself, result in any
speed increase if these other factors are involved. And it could even cost
you performance until you've re-tuned your front-end!

 

[aaron.kempf]

Access MDB doesn't run efficiently or effectively, or reliably over a
network.

Spit on people that use MDB.

Access Data Projects _SLAUGHTERS_ silly little MDB. but these dorks
are stuck in the '90s.

-Aaron
ADP Nationalist

 

[aaron.kempf]

Andy

you should have migrated a LONG time ago.

MDB is for crack babies.

Access Data Projects kick ass.


-Aaron
ADP Nationalist

 

[Tony Toews]

It's all getting incredibly slow. Speed is fine when ONE user is
accessing the database, but as soon as we get everyone (20 users)
accessing it, it runs slowly.

Aha, the dreaded second user in and things grind and grind problem
which an always open bound form will fix.

For more information on these, less likely causes, other tips and
links to MS KB articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[AndyCotgreave]

Hi Jeff et al,

Thanks for the replies. Very useful stuff.

The front end is 65MB; it has 230 objects (forms, tables, queries,
forms, macros, reports). The back end is 90MB, it only has tables.

Is this close to the theoretical limit?

2. Normalisation
This has fallen apart a little in recent times. It's my focus for the
next steps towards speeding it up again.

3. Numbers/network
- network speed: 100mbps.
- NICs are full duplex
- primary use is both entry and searching

Your "better database solution" doesn't, in and of itself, result in any
speed increase if these other factors are involved. And it could even cost
you performance until you've re-tuned your front-end!

Ultimately, this is the most salient point - i need to work on the
optimization before jumping into SQL Server, I guess?

Andy

 

[Jeff Boyce]

Andy

Not even close! Open Access HELP and search for "specifications" for
theoretical limits.

If you do decide to migrate the data to SQL-Server, you will have (probably
will have) a new set of 'optimizations' to perform.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[AndyCotgreave]

Not even close! Open Access HELP and search for "specifications" for
theoretical limits.

Cor blimey, you're right. I can't imagine a database with so many
tables, objects or queries.

So, given that I'm miles away from limits, and our network is pretty
fast, it's optimisation that's most likely our culprit?

Andy

 

[Tony Toews]

So, given that I'm miles away from limits, and our network is pretty
fast, it's optimisation that's most likely our culprit?

No. See my other posting in this thread.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

The front end is 65MB; it has 230 objects (forms, tables, queries,
forms, macros, reports).

This is fairly large for 230 objects. I had a database with 450
forms, 350 reports and 1200 queries and it was about 38 Mb. Or do you
have some graphics, such as logos, embedded on forms or reports?

Also have you decompiled recently?

Decompile or how to reduce Microsoft Access MDB/MDE size and decrease
start-up times
http://www.granite.ab.ca/access/decompile.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[AndyCotgreave]

This is fairly large for 230 objects. I had a database with 450

forms, 350 reports and 1200 queries and it was about 38 Mb. Or do you
have some graphics, such as logos, embedded on forms or reports?

Also have you decompiled recently?

Hi Tony,
Yes, I've decompiled - it takes the backend down to about 75MB and the
front end down to about 60MB. There is a lot of VBA code in the
database (approx 7500 lines of code) which may make a difference. The
main Companies table has 12000 records in it.

There are no graphics. Our biggest table, Companies, with it's 74
fields, has 13 memo fields in it.

I've been through the granite.com recommendations to no avail, so it
does begin to point to the design of the database...

I have identified lots of ways to normalise the main Companies table,
which will reduce the number of fields significantly. Hopefully this
will help.

I'll post in a new thread if anything crops up, but otherwise, thanks
for all your advice, everyone.

Andy

 

[aaron.kempf]

tired of workarounds?

TRY ADP

-Aaron
ADP Nationalist

 

[AndyCotgreave]

tired of workarounds?

TRY ADP

yes, i know about ADP. One needs to move to SQL Server, though (at
least,that's my understanding). Right now, my boss can't stomach the
idea. Once these optimisations fail, then I'll have a justification for
ADP.

How else can I sell the idea to him?

 

[aaron.kempf]

Andy;

SQL Server is free in a flavor called MSDE.
It is night and day-- 10 times easier to manage; 10 times more
powerful; 10 times more secure.

I would stick on that until you upgrade to Access 2007; at which time
you can upgrade to sql 2005 express.


MDB is for babies.

-Aaron
ADP Nationalist

 

[dbahooker]

Tony

your webpages is obsolete and ridiculous because it doesn't reccomend
people to use ADP.

MDB is for babies.

Lose the training wheels, script kiddie!

-Aaron
ADP Nationalist

 

[dbahooker]

DONT RUN MDB ACROSS A NETWORK; IT IS SLOW AS MOLASSES!!!

not for a single record and a single user.

use ADP.
Spit on MDB kids... Lose the training wheels.

-Aaron