This query executes very slowly and I wonder if it can be improved?
We have an Access database split to front end / back end with around 50 users. BE is in a network folder and FE is on users' hard drives.
Data entered into the FE is stored in FE tables until the user has finished inputting all the required data for a record. They then click a button to send the data to the BE in one go, to identical tables as in the FE. In the sql below the BE table is suffixed with '_Share'.
The table contains 2 keys: QuoteID and OptionID. There is a one-to-many between the two e.g.:
As they user works they are creating data for new Options to go with existing Quotes. What the code is doing is to check whether a QuoteID on the BE already has an OptionID created by the user on the FE, if not then the data for that OptionID is appended to the BE.
INSERT INTO T_Option_Category_Benefits_Share
SELECT T_Option_Category_Benefits.*, *
NOT IN (SELECT T_Option_Category_Benefits_Share.OptionID FROM T_Option_Category_Benefits_Share WHERE T_Option_Category_Benefits_Share.QuoteID=1971)));
The key fields are not indexed. The BE table contains 18 columns with around 100k rows. The network is generally quite slow at busy times but time of day doesn't have much effect. Amount of rows to append is only around 300 on average. We're using Office 365 on Windows 10.