Lara Lara - 1 year ago 62
SQL Question

Access Query is forever executing

I am trying to optimize a legacy application which uses Access as database. The issue with some of the queries are that, they are not returning anything after running hours. I am trying to figure out the issue by adding joins one by one. Now I got struck with one of the query as it is not returning anything and executing forever..
The query is behaving like this after adding Left Join which is using SubQueries to get the data.

Any suggestions would be heartily appreciated..

Answer Source

Joining on an aggregated column (MaxOfSTF_END_EFF_DT) probably doesn't work well.

I suggest: insert the result of [qry List ASDT tbl ASDT P1T_AGENT_STAFF Active MAX END DATE] into a temp table. Join that table instead in your problematic query.

You can use a SELECT INTO query for that (and delete the old temp table beforehand), or an INSERT INTO query (and empty the temp table beforehand).

Once you have it working in principle, I suggest using an Append query (INSERT INTO) instead of a Make Table query.

So your temp table stays in place, and you can add indexes on the fields you use for JOINs, to further improve performance.

To open the main query, you then do in VBA:

' Empty temp table
CurrentDb.Execute "DELETE * FROM Temptable"
' Run Append query
CurrentDb.Execute "NameOfYourAppendQuery"
' Open main query
DoCmd.OpenQuery "MainQuery"