Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

speeding up large query

Status
Not open for further replies.

amillia

Programmer
Nov 14, 2001
124
US
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.
 
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]
 
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?
 
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
 
yes, your right. I fixed that. Unfortunately it is still not returning any records.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top