4
\$\begingroup\$

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.:

QuoteID   OptionID

1234      1
1234      2

3333      1
3333      2
3333      3

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.*, * 
FROM T_Option_Category_Benefits 
WHERE (((T_Option_Category_Benefits.QuoteID)=1971) 
AND ((T_Option_Category_Benefits.OptionID) 
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.

\$\endgroup\$
5
  • \$\begingroup\$ Sorry I cannot resist, telling that the central database should not be MS-Access, and there should be indexes. Even if you then need a programming language to shovel from client's MS-Access to the central database. Maybe NOT IN is not needed? And/or instead of INSERT SELECT just a SELECT INTO? \$\endgroup\$
    – Joop Eggen
    Commented Jan 17, 2020 at 12:53
  • \$\begingroup\$ Legacy DB, no choice about the tools, will get into SQL Server next year but stuck with it for now. What's the advantage of select into? \$\endgroup\$
    – Absinthe
    Commented Jan 17, 2020 at 14:39
  • \$\begingroup\$ What does very slowly mean? Do you have different measurements for when the network is busy vs not busy? How many rows do you normally expect to insert into the Shared table? \$\endgroup\$ Commented Jan 22, 2020 at 13:55
  • \$\begingroup\$ @Dannnno Question updated. Avg is around 60 seconds during busy times, not much improvement at quieter times, only 300 rows appended on average \$\endgroup\$
    – Absinthe
    Commented Jan 22, 2020 at 16:40
  • \$\begingroup\$ I'm not super familiar with MS Access; is there a way to get a query plan for how the query is being executed? I found this, if it is helpful techrepublic.com/article/… \$\endgroup\$ Commented Jan 22, 2020 at 19:03

1 Answer 1

2
+25
\$\begingroup\$

Here are a few tips:

  1. Do not use select *, instead type out all the columns you need to insert into. It should be done in both your select and insert statements.

  2. For the subquery in the where clause, you can store the result from that query in a temp table and then use that temp table for the where clause so it does not need to check while inserting the data.

  3. Try to add an index to your tables which are unique so you can do faster and more efficient queries.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.