I was recently called in to help troubleshoot a query that was being used as the record source of an MS Access form. The query’s execution plan looked fine and it executed quickly through SSMS. In Access, however, the query took about twice as long to run. I ran profiler, and to my surprise (and the developer’s), we found that the query was actually executing twice.
The culprit was the following lines of VBA code behind his button:
frm.RecordSource = sSQL
frm.requery
The developer’s intent was straightforward: he wanted to make sure the form had the latest data after the record source had changed. It was also unnecessary.
A bound control’s recordsource property connects it to a table or query. When an open control’s recordsource is changed, it automatically requeries the underlying data. The requery method also runs the query on which the control is based.
In the case above, the developer was unintentionally querying the source twice! Simply removing the extra requery was all it took to cut the runtime in half.
