Поиск недостающих ключей

#sql #ms-access

Вопрос:

Могу ли я ускорить выполнение этой инструкции в Ms Access 2007?

 SELECT *
FROM (SELECT DISTINCT p.zipcode, p.place FROM p) AS tmp
WHERE NOT EXISTS 
  (SELECT * FROM zipcodes WHERE 
    (tmp.zipcode=zipcodes.zipcode) AND (tmp.place=zipcodes.place));

Я хочу найти в p таблице комбинации (почтовый индекс, место), которых нет в zipcodes таблице.

p Таблица довольно большая, но при сокращении до tmp содержит около 40 000 записей. zipcodes Таблица содержит около 15 000 записей. zipcodes Таблица индексируется по (почтовый индекс, место).

Для получения моего результата требуется более часа. Могу ли я получить это быстрее? Могу ли я увидеть план выполнения в Access 2007? (Я не обычный пользователь Access, а также не эксперт по SQL.)

С уважением, Карстен.

1. Нет причин делать NOT EXISTS (SELECT * ... при возврате NULL должно выполняться то же самое (вы просто проверяете, существует ли строка, которая соответствует, фактические данные, хранящиеся в строке, в конечном счете, не имеют значения).

2. Правильно ли я вас понимаю, что я мог бы удалить ключевое слово EXISTS, т.Е. WHERE NOT (SELECT *)... ?

3. Нет, вам нужно exists ключевое слово. Однако ваше select утверждение может быть просто select null , а не select * потому что на самом деле вас не интересует, что такое данные в строке, только то, что ни одна строка не соответствует вашим критериям.

4. Я не знаю о Access, но SQL Server не заботится о списке столбцов в EXISTS подзапросе (и не заботился в течение десятилетия?). Раньше людям приходилось использовать SELECT 1 or SELECT NULL для оптимизации, но в этом больше нет необходимости. Как я уже сказал, я не знаю о Access, хотя.

Ответ №1:

Вы можете СОЕДИНИТЬ две таблицы слева и исключить совпадающие строки из результирующего набора. С таблицей zipcodes, индексируемой в zipcode и place, это может быть намного быстрее, чем то, что у вас есть сейчас. Надеюсь, достаточно быстро, вам не нужно прибегать к созданию промежуточной временной таблицы.

 SELECT DISTINCT p.zipcode, p.place
FROM p LEFT JOIN zipcodes AS z ON (p.place = z.place) AND (p.zipcode = z.zipcode)
WHERE (((z.zipcode) Is Null));

Редактировать: Вы просили просмотреть план выполнения. Начните с этой статьи на TechRepublic: Используйте ShowPlan от Microsoft Jet для написания более эффективных запросов Вы также можете найти дополнительную информацию, выполнив поиск в Интернете по «Jet ShowPlan».

1. Это очень быстро и, на мой взгляд, наиболее элегантно. Спасибо!

2. ОБЪЕДИНЕНИЕ всегда предпочтительнее, чем НЕ В или НЕ СУЩЕСТВУЕТ, потому что в Jet / ACE объединения всегда используют индексы с обеих сторон сравнения, тогда как иногда с помощью NOT оно использует его только с одной стороны. Честно говоря, я использую подзапросы таким образом только тогда, когда мне нужен редактируемый набор записей — в противном случае я использую СОЕДИНЕНИЕ.

Ответ №2:

Во-первых, я бы создал «настоящую» таблицу tmp вместо того, чтобы использовать ее таким образом. Это само по себе уже может помочь (не уверен). Во-вторых, я бы убедился, что в таблице есть индекс почтового индекса p , а если это не поможет, также в таблице tmp . Если это все еще не помогает, также создайте индекс для комбинации (почтовый индекс, место) на zipcodes .