Access Query performance help

[dojones]

Using Access 2003:

The db is split between a front end/back end and in this case is querying one table called students with about 10k records

When the form is first loaded a subform in gridsheet view executes a query.

SELECT Students.State, Students.Zip, Students.ID
FROM Students
WHERE 1=2

Why does this query take about 90 secs to return no rows and cause 6-8MB of network traffic.

When using the below query it take only seconds to return results and the network traffic is barely noticeable.
SELECT Students.State, Students.Zip, Students.ID
FROM Students
WHERE lastname = "Jones"

Ultimately I'm trying to minimize the time to load the subform when this form is first opened and before the user provides their search filters.

Thanks.

 

[PHV]

Replace this:
WHERE 1=2
with something like this:
WHERE lastname = "I want nothing"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rascelon]

dojones:
Are you saying that the query always takes awhile to load when you first open the form or have you tried opening the form using both queries and get quicker results with the 2nd query?
If it is only that the initial loading of the form is slow, then it seems to me that that would be a result of your front end connecting with your back end.
I've used code (received from Tek-Tips) to open a connection to my back end when I open my front end so that I won't have to go through the initial recordset slow down when I open a form bound to a table in the back end.
Am I making sense?
Vince

 

[dojones]

>Are you saying that the query always takes awhile to load >when you first open the form or have you tried opening the >form using both queries and get quicker results with the >2nd query?

Yes, quicker results with the 2nd query and network traffic is less then 500k. When using the "where 1 = 2" it always seems to download the entire table since i can see over 6MB being received from the remote backend. Ultimately i'm trying to dynamically set the subform's record source to one query with the lighter net traffic and setting record source to a different query when the search button on the parent form is clicked (and the user has supplied values to better filter the results). In the code I can't seem be able to do this.

 

[Ed2020]

<snip> Yes, quicker results with the 2nd query and network traffic is less then 500k. When using the "where 1 = 2" it always seems to download the entire table since i can see over 6MB being received from the remote backend. <snip>

I believe this is always the case with the Jet database engine which is why Access generates to much network traffic. Jet will always retrieve all the data and run the query locally, dumping the data you're not interested in.

Ed Metcalfe.

Please do not feed the trolls.....