Query Performance, optimising for a network

[ImpMan]

I do not understand the following:
Query1 links many tables together and will show all the records
Query2 is a simple select query of query1 and only shows records related to a specific part

Query2 always runs faster (by a couple of seconds)than Query1. Why?

All the tables are in databases on the network file server. Surely the query is being executed on my local machine, therefore all the data will need to be brought down the network processed and then displayed. Therefore query2 should be slower than query1. Obviously there is a gap in my understanding.

Also if I have a mainform and a subform, when the subform loads how much data is dragged over the network? Are the enitre data sets pulled over or subsets? Does the subform only pull data over relating to the mainform, or does it pull over the entire data source.

Basically the network is slowing down my application to an unacceptabel level and I want to know how to tweak the database to speed things up. Any ideas?

Thanks in advance

 

[FirstAndGoal4]

Query2 resultset is a subset of Query1. The added "where" condition should make it run faster.

Try this for a speed fix... create a dummy table in the data database with 1 field (name it what ever you want). Create a global recordset variable in a module of your application database. When you first open the application database, open the global recordset on the dummy table and leave it open the entire session. Don't forget to close it when you exit.

 

[ImpMan]

Sorry to be a bit stupid but could you give me more details about this fix.

By the way, why does the 'where' clause speed up the execution of query2 considering that all the processing is done on the local machine?

Thanks