[RESOLVED] [ACCESS] Access Sending entire table accross network before filtering data.

[rack]

Question:
I am looing for ways to improve Query speed, reduce data retreived from table, and limit the amount of data required to send over the network.

I assume that if I was able to limit the data sent from the BackEnd Server Database BEFORE it gets sent to the MS Access FrontEnd, this would drastically help, as it seems I am sending too much, to most of the table back.

Do WHERE conditions check the table items on the server side, or client side? What ways filter or check information on the Server Side?

Information:

I haven't posted any queries yet, but if it is easier to durdge through 10-20 queries/Stack Queries I can do that, or a combination there of.

I have a report with 10 sub-reports. It is an overview of an employee's performance for a year. The MDB file is only 70megs. When I was using a VPN connection from home, I saw that I ended up transfering about 20-30 megs of data when trying to run the report, it also of course, took a long time.

I have been looking around and found various options on improving speed, but no one talks directly about the timeline, or order of opperation that access uses.

This is my guess as the timeline. Please post the correct timeline at which point the entire remaing table is sent over the network.

Query sets link to table.
Checks connections. (SELECT FROM)
Reviews relations. (Joins)
Retreives data (which includes sending it over the network)
Filters data (Where condition)
Groups data (Group By)
Re-filters data (having)
SortsData (Order By)

I have the JET DEBUG=on option on, and when reviewing the showplan.OUT file, I really have no clue what any of it means.

Also, it seems like the amount of data transfered is REDUCED if I remove the where condition from a query, why is that?

As I'm not sure if there is an end all solution I'll start with what I've tried, and what I am trying.

First I've been going through my queries trying to replace any VB Specific Functions with classic or true SQL commands, as one of the suggestions in the query speed up tricks.

I've been going through and creating Indexes on items used in where conditions.

I have been trying to use JOIN's as much as possible instead of where conditions or other odd ways.

Links I found:
Query Speed up tricks.
http://allenbrowne.com/QueryPerfIssue.html
Show Plan information
http://articles.techrepublic.com.com...1-5064388.html
MS Help and Support page
http://support.microsoft.com/kb/239527/EN-US/
Indexes
http://www.techonthenet.com/access/tables/indexes.php