Запрос SELECT INTO с вложенным запросом NOT IN занимает много времени / зависает

Question

Проблема в том, что этот запрос зависает или имеет бесконечные записи, которые я не знаю, как исправить с помощью MS ACCESS:

Ожидаемый пользовательский ввод:

User input Start Date: 1/15/2015
User input End Date: 11/15/2015
User input Upper Data Threshold in kB: 50

Исходная таблица:

[Master] Table in Access:
Invc Date  Mobile Nbr     PktDtVol   
---------  ----------     --------   
1/15/15   647-409-8206    48kB
2/15/15   647-409-8206    33kB
3/15/15   647-409-8206    8000kB
4/15/15   647-409-8206    20kB
5/15/15   647-409-8206    10kB
6/15/15   647-409-8206    0kB
7/15/15   718-500-2311    3kB
8/15/15   718-500-2311    45kB
9/15/15   718-500-2311    25kB
10/15/15  514-300-3311    33kB
11/15/15  514-300-3311    20kB

Вывод в [Temp_Table]:

Invc Date  Mobile Nbr     PktDtVol    Difference in Days 
---------  ----------     --------   -------------------
7/15/15    718-500-2311    3kB             304
8/15/15    718-500-2311    45kB            304
9/15/15    718-500-2311    25kB            304
10/15/15   514-300-3311    33kB            304
11/15/15   514-300-3311    20kB            304

Принято решение SQL для генерации вышеуказанного вывода:

PARAMETERS [Start Date] DateTime, [End Date] DateTime, [Upper Bound Usage in KB] IEEEDouble; 
SELECT m.[Invc Date], m.PktDtVol, m.[Mobile Nbr], DateDiff("d",[Start Date],[End Date]) AS [Difference in days] 
INTO Temp_Table FROM Master AS m 
WHERE (m.[Invc Date]>=[Start Date] And m.[Invc Date])<=[End Date] AND m.[Mobile Nbr] NOT IN 
(SELECT q.[Mobile Nbr] FROM Master AS q WHERE (q.PktDtVol>=[Upper Bound Usage in KB]));

Отсюда я попытался создать индекс для оптимизации запроса, изменив таблицу с помощью другого оператора SQL, но не работает:

 CREATE INDEX Index2 ON Master([Ttl Charges])

Запрос работает нормально, без зависаний с 10 записями в исходной таблице с несколькими записями с ожидаемым выводом. Но проблема возникает, когда в исходной таблице 56 000 записей с несколькими записями.

Answer 1

Индекс [Ttl Charges] не поможет вам, но индекс [PktDtVol] поможет. Я только что провел тест с 10 000 строк, и отсутствие индекса для [PktDtVol] определенно было узким местом в производительности:

Indexes:
  none
Time:
  170 seconds (just under 3 minutes)

Indexes:
  [Invc Date]
  [Mobile Number]
Time:
  (same as before)

Indexes:
  [Invc Date]
  [Mobile Number]
  [PktDtVol]
Time:
  36 seconds

Для дополнительного повышения производительности вы можете переформулировать запрос, чтобы использовать LEFT JOIN вместо предложения NOT IN с подзапросом, как вы упомянули в комментарии:

PARAMETERS [Start Date] DateTime, [End Date] DateTime, [Upper Bound Usage in KB] IEEEDouble; 
SELECT 
    [Master].[Invc Date], 
    [Master].PktDtVol, 
    [Master].[Mobile Nbr], 
    DateDiff("d",[Start Date],[End Date]) AS [Difference in days] 
INTO Temp_Table 
FROM 
    [Master] 
    LEFT OUTER JOIN 
    (
        SELECT DISTINCT q.[Mobile Nbr] FROM Master AS q 
        WHERE (q.PktDtVol>=[Upper Bound Usage in KB])
    ) s 
        ON [Master].[Mobile Nbr] = s.[Mobile Nbr] 
WHERE 
    [Master].[Invc Date] >= [Start Date]
        AND [Master].[Invc Date] <= [End Date]
        AND s.[Mobile Nbr] IS NULL;