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

On Tue, 4 Dec 2007 14:09:03 -0800, Lin <Lin@discussions.microsoft.com> wrote:

>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]

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)

"Lin" <Lin@discussions.microsoft.com> wrote in message 
news:DAABBFB2-B9E6-4781-801A-60393A33C8BC@microsoft.com...
>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 

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

On Wed, 5 Dec 2007 01:26:02 -0800, DanielWalters6
<DanielWalters6@discussions.microsoft.com> wrote:

>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]

DanielWalters6 <DanielWalters6@discussions.microsoft.com> wrote:

>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/

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)



"DanielWalters6" <DanielWalters6@discussions.microsoft.com> wrote in message 
news:1D8FF08F-42AD-4BBA-92DD-62A0E68BC880@microsoft.com...

> 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