Query is very slow with Form

[rocky09]

Hi All,

I have a Query, which is designed on linked Tables, the tables are located at remote servers. When I run the query (only), I am the getting the results very quickly but if I use the Query with a Form (as data source), I am getting the results after 5 - 10 minutes.

Is there any way to tweak this?

Best Regards,
Rocky

 

[Guus2005]

Are you sure that the query you run standalone is the exact match as the one attached to the form?

It is difficult to say without the actual form.
Perhaps you use one or more comboboxes on your form, based on a query?

HTH

 

[jdraw]

Please show the sql for the query.
What exactly is the purpose of the Form....How does it fit in with the query?

 

[Galaxiom]

Where a query runs without an Order By, Access will load the first records to be returned as soon as they become available.

Once an Order By is included, either in the query or the form itself, the results must all be returned before any will be displayed because the complete set of records is required before they can be ordered. I expect you have an Order By on the form.

You need to find why your query is slow. There are several common areas that cause problems.

Check that fields used for select criteria are indexed. Without the index the engine must read every record.

Are functions being applied to every record before the criteria can be tested? A common mistake by inexperienced developers is to convert a date in the records to a month and year and then select on those derived fields. Every record must be processed by the engine. The efficient way to select is to generate a date range criteria and test the indexed date field against that criteria.

 

[rocky09]

Thank you very much for your replies. Basically, I have search Box, a Button and a List box. So, I linked the Query Results to the List Box. When I enter the Text in the Search box and click Ok, then in the list box, the results will display. It is ok, if I just run the Query itself. But, when I tried to do with the Form. It is getting slow.

Here is the Query :

SELECT EC_Bike.NAME AS [Bike Name], EC_ENGINE.SERIALNUMBER AS Engine, EC_ENGINE.POSITION AS [Engine Position], EC_COMPANY.NAME AS [Company Name], Max(EC_ENGINEDATA.DATADATE) AS [Last Record]
FROM ((EC_COMPANY INNER JOIN ((EC_Bike INNER JOIN EC_ENGINE ON EC_Bike.ID = EC_ENGINE.BikeID) INNER JOIN EC_ENGINEDATA ON EC_ENGINE.ID = EC_ENGINEDATA.ENGINEID) ON EC_COMPANY.ID = EC_Bike.COMPANYID) INNER JOIN EC_AIRLINECOMPANY ON EC_COMPANY.SPECIFICCOMPANYID = EC_AIRLINECOMPANY.ID) INNER JOIN EC_COMPANY AS EC_COMPANY_1 ON EC_AIRLINECOMPANY.SERVICECOMPANYID = EC_COMPANY_1.ID
GROUP BY EC_Bike.NAME, EC_ENGINE.SERIALNUMBER, EC_ENGINE.POSITION, EC_COMPANY.NAME
HAVING (((EC_Bike.NAME) Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"))
ORDER BY EC_ENGINE.POSITION;