Update Query Performance Issue

[Amber_1977]

Hello – The below update query takes over an hour to run so I need some help improving the performance.

When I run the same query as a select, it only takes a few minutes to complete and I can easily use the record selector to move to the last record.

I have already created an index on Sellable_Placeable_Starts.ACCESS_MEDIUM_NO and AGENCY_FINAL.cracct_no but the performance is still poor.

Does anyone have any suggestions?

Thank you!

(Both tables are local)
AGENCY_FINAL = 103,294 records
SELLABLE_PLACEABLE_STARTS = 106,193 records.



DoCmd.RunSQL "UPDATE Sellable_Placeable_Starts INNER JOIN AGENCY_FINAL ON Sellable_Placeable_Starts.ACCESS_MEDIUM_NO = AGENCY_FINAL.cracct_no SET Sellable_Placeable_Starts.MODEL_RANK2 = [Rank], Sellable_Placeable_Starts.MODEL_SCORE2 = [PayScore]"

[NTC]

where do [Rank] and [PayScore] come from? I will guess a value calculated or entered in a form?

I would do this;

put [Rank] and [PayScore] values in control boxes in a form if they are not already.

make a plain old select query similar to your first part of that sql statement that has the purpose of just collecting the correct records into one record set. Name this qrySelectedRecords and save. make sure when you run this that the correct records appear.

then make another query - use query design beginning as a select query; put qrySelectedRecords up as a table source, and drag down the columns to be updated into the query design - - then change the query to an update query; in the 'update to' field of each column put the form/control name that holds the rank & payscore info as appropriate for that column. the form must be open of course. i.e.
Forms!FormName.ControlName

run that update query. it should update pretty quick.

hope this helps.

[Amber_1977]

NTC Thank you for your reply!

I'm sorry for the confusion, [Rank] and [PayScore] are fields from the AGENCY_FINAL table.

The query is still performing poorly, does anyone have an idea for how to improve it?


The query should look like this:

DoCmd.RunSQL "UPDATE Sellable_Placeable_Starts INNER JOIN AGENCY_FINAL ON Sellable_Placeable_Starts.ACCESS_MEDIUM_NO = AGENCY_FINAL.cracct_no SET Sellable_Placeable_Starts.MODEL_RANK2 = AGENCY_FINAL![Rank], Sellable_Placeable_Starts.MODEL_SCORE2 = AGENCY_FINAL![PayScore]"