speeding up large query

[amillia]

I have a huge update query, that goes and gets ids from many look up tables about 20. Should i use pass through queries instead of putting it all together in the update query? do you know of an example? I can post an example of the query i am working with but it is rather huge.

 

[dhookom]

A pass-through query will almost always perform much faster than an Access query. This requires that all tables be accessed from the remote database.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[amillia]

SELECT AWEB_B_REQUIREMENT.DISCR_DISPLAY, AWEB_B_REQUIREMENT.REQ_ID, AWEB_B_REQUIREMENT.REQ_NO, AWEB_C_MAINTENANCE_HISTORY_INFO.IND_DATE, AWEB_B_SPECIFICATION.RETIRE_DATE
FROM ((AWEB_C_MAINTENANCE_HISTORY_INFO INNER JOIN (((AWEB_B_REQUIREMENT INNER JOIN AWEB_B_SPECIFICATION ON AWEB_B_REQUIREMENT.SPEC_ID = AWEB_B_SPECIFICATION.SPEC_ID) INNER JOIN AWEB_C_LOV_ACFT ON AWEB_B_SPECIFICATION.ACFT_ID = AWEB_C_LOV_ACFT.ACFT_ID) INNER JOIN AWEB_C_DISCREPANCY_INFO ON AWEB_B_REQUIREMENT.REQ_ID = AWEB_C_DISCREPANCY_INFO.REQ_ID) ON AWEB_C_MAINTENANCE_HISTORY_INFO.INSP_ID = AWEB_C_DISCREPANCY_INFO.INSP_ID) INNER JOIN AWEB_C_BUNO_INFO ON (AWEB_C_LOV_ACFT.ACFT_ID = AWEB_C_BUNO_INFO.ACFT_ID) AND (AWEB_C_MAINTENANCE_HISTORY_INFO.BUNO_ID = AWEB_C_BUNO_INFO.BUNO_ID)) INNER JOIN FDDR_Discrepancy_Details ON (AWEB_B_REQUIREMENT.REQ_NO = FDDR_Discrepancy_Details.REQ_NO) AND (AWEB_C_MAINTENANCE_HISTORY_INFO.JON = FDDR_Discrepancy_Details.JON)
WHERE (((AWEB_B_REQUIREMENT.DISCR_DISPLAY)=True) AND ((AWEB_C_MAINTENANCE_HISTORY_INFO.IND_DATE)<[AWEB_B_SPECIFICATION]![RETIRE_DATE] And (AWEB_C_MAINTENANCE_HISTORY_INFO.IND_DATE)>[AWEB_B_SPECIFICATION]![RETIRE_DATE])) OR (((AWEB_B_SPECIFICATION.RETIRE_DATE) Is Null));

okay, this query is running very slow and not returning records. do you see any obvious problems?

 

[PHV]

Your where clause seems strange to me:
WHERE (((AWEB_B_REQUIREMENT.DISCR_DISPLAY)=True) AND ([!](AWEB_C_MAINTENANCE_HISTORY_INFO.IND_DATE)<[AWEB_B_SPECIFICATION]![RETIRE_DATE] And (AWEB_C_MAINTENANCE_HISTORY_INFO.IND_DATE)>[AWEB_B_SPECIFICATION]![RETIRE_DATE][/!])) OR (((AWEB_B_SPECIFICATION.RETIRE_DATE) Is Null))

The red part will always evaluate to false.

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

 

[amillia]

yes, your right. I fixed that. Unfortunately it is still not returning any records.