Optimizing MS Access Query

[mariost]

Hi all,
a rather basic problem I guess but I could not find something to optimize this "common type" of queries.
For my app, I have to export certain data from an Access DB to an Excel file. I am doing this with TransferSpreasheet.


The query must compile data from a Master table that includes information for a Production Order and a Detail table that has information about Customers Orders. One Production Order can include multiple Customer Orders for the same product. There is also a many-to-many table that lists which Customers Orders belong to which Production Orders (using the ProdOrderIDs add OrderIDs).
The problem is a calcuated field I have that concatenate all the Customer orders for each Production Order. Thus, the query returns some data from the Production Order table and in one field it concatenated all customer orders related to this Production Order. This field is populated via a VBA function which retrieves the relevant Customer orders info for the particular Production Order ID. The query returns around 1000 records as of now (to be grow up). The query takes more than 3 minutes in a FE and BE setup. The query and the function runs on the FE.
Any ideas on how to optimize this? It shall be a common problem but unfortunately I cannot solve it.
Thanks
Marios

[CJ_London]

obvious thing is to ensure all fields used for linking, criteria and sorting are indexed. Unlikely I this situation but if you query uses 'Like "*.... find another way, this will not use indexing

otherwise if your BE is on the network, it could be your network performance is the issue. create a copy of the BE on your local drive reconfigure a copy of the FE to link to it and run it to see what the performance is like.

beyond that, without seeing the sql to your query it becomes mere speculation - most obvious being use of domain functions (don't).

I seem to recall there is a query analyser function or facility for access (not sure about 2003) you'll need to google to find out more.

otherwise, try rebuilding your query step by step and test running it after each change until you find which bit is causing the performance hit

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...d.php?t=282850

I will withdraw since you are getting a lot of help on the other forum. Suggest you read this post http://www.excelguru.ca/content.php?184

[mariost]

duplicate post here

http://www.access-programmers.co.uk/...d.php?t=282850

I will withdraw since you are getting a lot of help on the other forum. Suggest you read this post http://www.excelguru.ca/content.php?184

Thanks for the info and you are right to what you are saying. I haven't thought about it.

Please accept my apologies.

Keep up the good work.

Marios