Database running so slow?

[Lin]

I have an access databse with 1 million records in my netwok server. the
problem with this database is when I run a Query or report from this
database, it is extremly slow, taking 15-20 minutes to display (If I running
a little longer one). Is there anything I can do to increase the speed of my
database. any help, really appreciated.

Thank you

 

[John W. Vinson]

I have an access databse with 1 million records in my netwok server. the
problem with this database is when I run a Query or report from this
database, it is extremly slow, taking 15-20 minutes to display (If I running
a little longer one). Is there anything I can do to increase the speed of my
database. any help, really appreciated.

Thank you

Take a look at Tony Toews'' suggestions at

http://www.granite.ab.ca/access/performancefaq.htm

Proper indexing of your tables is essential, especially for a million row
table! Are all fields used as criteria or for sorting indexed? Are all join
fields indexed?

John W. Vinson [MVP]

 

[david]

In order of importance:
1) Don't let your AV scan network drives
2) Turn off your AV
3) Disable network packet signing on your Workstation and on your Server.

(david)

 

[DanielWalters6]

When running an Access Database, it downloads ALL tables from server onto the
current workstation which performs the Query.

Large databases = lot of network traffic - If running a Make Table query,
you can litterally double the size of the database!

It may be worth copying the database onto local machine, running what you
need then replacing the copy on the network share with the one you want?

If you use Microsoft SQL Server - it performs the query remotely and only
returns records matching the criteria you set. - Instead of downloading all
records, then doing the query at your workstation and saving all the changes
remotely.

Hope this helps - Let us know how you get on!
Dan Walters

 

[John W. Vinson]

When running an Access Database, it downloads ALL tables from server onto the
current workstation which performs the Query.

That is absolutely UNTRUE.

If you must do a full table scan, then it will download that one table. If
your queries are (as they should be) properly indexed, it will download only
the records matched by the index.


John W. Vinson [MVP]

 

[Tony Toews [MVP]]

When running an Access Database, it downloads ALL tables from server onto the
current workstation which performs the Query.

WRONG, WRONG, WRONG. If a table is properly indexed Access only
pulls down the pages containing the appropriate index entries and data
records.

Large databases = lot of network traffic -
True.

If running a Make Table query,
you can litterally double the size of the database!
Yup.

It may be worth copying the database onto local machine, running what you
need then replacing the copy on the network share with the one you want?

Which can be quite impractical.

If you use Microsoft SQL Server - it performs the query remotely and only
returns records matching the criteria you set. - Instead of downloading all
records, then doing the query at your workstation and saving all the changes
remotely.

Sure, but at a cost of greater complexity and thus time and effort.

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[david]

also,

If you use Microsoft SQL Server - it performs the query remotely and
returns records matching the criteria you set. - Instead of downloading

Only if you are using pass-through queries or stored procedures. (Note
that OLEDB/SQL is pass-through).

ODBC queries are not just performed remotely unless they are very
simple. The ODBC specification defines a SQL (OLEDB defines an
OLE api without any kind of database language). An application which
uses ODBC SQL can connect to any ODBC driver. But the price is that
ODBC SQL is too simple to support ordinary database queries unless
they are very simple.

(david)