Performance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I know this is a really stupid question but I'm trying to improve the
performance of our database and I've read many of your posts. They all seem
to agree on the three main problems.

However I can not seem to find the settings concerning Track Name
AutoCorrect or Sub Datasets. Can somebody point me in the right direction
please?

Many thanks

Ian
 
Hi Ian,

You can disable NameAutocorrupt by clicking on Tools > Options | General Tab
This option is stored on a per-database basis. It is also set by default on
all new JET databases created in Access.

SubDatasheets are those little "+" symbols that you'll see when you create a
relationship with a many side table. You can set these to [None] manually, by
opening the table in design view, and then clicking on View > Properties. A
more efficient method, especially if you have a lot of tables present, is to
copy the code shown in this KB article:

BUG: Slow performance on linked tables in Access 2002 and Office Access 2003
http://support.microsoft.com/?id=275085

Have you seen my article on improving performance?
Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi,

I know this is a really stupid question but I'm trying to improve the
performance of our database and I've read many of your posts. They all seem
to agree on the three main problems.

However I can not seem to find the settings concerning Track Name
AutoCorrect or Sub Datasets. Can somebody point me in the right direction
please?

Many thanks

Ian
 
Tom answered your main questions. I'll put some more food for thought on your
plate.

The #1 performance problem with any relational database is poor design. Not
have the data properly normalized causes a developer to have to jump through
complex code and SQL where a simple, fast SQL would do the job.

You tables should be linked in the Relationship Window with Referential
Integrity selected. If you can't get RI to work, see the previous paragraph.

Index all foreign key fields. Access automatically creates an index (or
two!) for primary keys but does not do so for FKs.

Index fields used in query criteria. They are know as Where clauses in SQL.

Conversely remove any indexes not for a PK, FK, Where clause and maybe even
Order By clauses.
 
NoviceIan said:
I know this is a really stupid question but I'm trying to improve the
performance of our database and I've read many of your posts. They all seem
to agree on the three main problems.
Click to expand...

There are quite a few more problems. See Access Performance FAQ page
at http://www.granite.ab.ca/access/performancefaq.htm
However I can not seem to find the settings concerning Track Name
AutoCorrect or Sub Datasets. Can somebody point me in the right direction
please?
Click to expand...

Name Autocorrect has been known to cause lots of problems and
corruption. But I've been sporadically using in A2003 and it hasn't
caused me any problems yet.

Tom has handled the sub datasheets question.

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
 
Hi Jerrry,
Index all foreign key fields. Access automatically creates an index (or
two!) for primary keys but does not do so for FKs.
Click to expand...

You do not need to index foreign key fields when you use Tools >
Relationships to establish a relationship to a primary key field. Please see
the information under the Use indexes section of my multi-user document. You
can get duplicate indexes on the primary key field if you have the default
configuration under Tools > Options to Auto Index keys. This is a very poor
option in my opinion. Please see the following "Gem Tip":

http://www.access.qbuilt.com/html/gem_tips.html#AutoIndex

Index fields used in query criteria.
Click to expand...
And for sorting.
...and maybe even Order By clauses.
Click to expand...
Not recommended. An Order By is a sort.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Tom answered your main questions. I'll put some more food for thought on your
plate.

The #1 performance problem with any relational database is poor design. Not
have the data properly normalized causes a developer to have to jump through
complex code and SQL where a simple, fast SQL would do the job.

You tables should be linked in the Relationship Window with Referential
Integrity selected. If you can't get RI to work, see the previous paragraph.

Index all foreign key fields. Access automatically creates an index (or
two!) for primary keys but does not do so for FKs.

Index fields used in query criteria. They are know as Where clauses in SQL.

Conversely remove any indexes not for a PK, FK, Where clause and maybe even
Order By clauses.
 
Back
Top