Using filter by form in Access taking way longer than usual
We have Access with Microsoft 365 Apps for business as the frontend to SQL Server 2017.
One of the forms in Access is routinely searched by using filter by form (Ribbon > Home > Sort & Filter > Advanced > Filter by Form). Now performing these steps is taking extraordinarily long and it's not clear to me that anything has changed other than the number of records returned.
- Advanced > Clear All Filters
- Advanced > Filter by Form
- Enter filter criteria into (bound) text boxes, for instance
- Toggle Filter
The RecordSource of the form is a SQL statement that's long and contains 9 left joins. It's not pretty, but it has previously worked more quickly.
Unfiltered, the recordset is ~17,500 records. In SSMS the query takes 2 seconds to return. With Filter by Form it's now taking 2+ minutes.
The VM on which SQL Server runs shows performance as normal through Task Manager.
Thoughts? Much appreciated!