Оптимизируйте SQL с помощью Interbase

Меня вдохновили хорошие ответы на мой предыдущий вопрос о SQL. Сейчас этот SQL выполняется на БД с Interbase 2009. Его размер составляет около 21 ГБ.

SELECT DistanceAsMeters, AddrDistance.Bold_Id, AddrDistance.Created, AddressFrom.CityName_CO as FromCity, AddressTo.CityName_CO as ToCity
FROM AddrDistance
LEFT JOIN Address AddressFrom ON AddrDistance.FromAddress = AddressFrom.Bold_Id
LEFT JOIN Address AddressTo ON AddrDistance.ToAddress = AddressTo.Bold_Id
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
Order By Created Desc

Есть 840000 строк с AddrDistance, 190000 строк с Address и 4 с DistanceQueryTask.

Вопрос в том, можно ли это сделать быстрее? Я предполагаю, что один и тот же запрос выполняется много раз выберите жирный_id из DistanceQueryTask. Обратите внимание, что меня не интересуют хранимые процедуры, просто SQL :)

EDIT1 Вот текущий план выполнения:

Statement: SELECT DistanceAsMeters, AddrDistance.Bold_Id, AddrDistance.Created, AddressFrom.CityName_CO as FromCity, AddressTo.CityName_CO as ToCity
FROM AddrDistance
LEFT JOIN Address AddressFrom ON AddrDistance.FromAddress = AddressFrom.Bold_Id
LEFT JOIN Address AddressTo ON AddrDistance.ToAddress = AddressTo.Bold_Id
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
       and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask)
Order By Created Desc

PLAN (DISTANCEQUERYTASK INDEX (RDB$PRIMARY218))
PLAN SORT (JOIN (JOIN (ADDRDISTANCE NATURAL,ADDRESSFROM INDEX (RDB$PRIMARY234)),ADDRESSTO INDEX (RDB$PRIMARY234)))

И да, DistanceQueryTask предназначен для небольшого количества строк в базе данных.


sql delphi interbase
person Roland Bengtsson    schedule 16.03.2010    source источник
comment
Вы проверяли план выполнения запроса (это можно сделать с помощью компонентов IBX, IBExpert и, возможно, даже с помощью IBConsole)? Он показывает, есть ли естественные соединения вместо индексированных.   -  person mjn    schedule 17.03.2010
comment
Всегда ли DistanceQueryTask будет содержать небольшое количество записей?   -  person skamradt    schedule 17.03.2010

Ответы (4)


arrow_upward
2
arrow_downward

Использование левого соединения и подзапросов замедляет любой запрос.

Вы можете получить некоторые улучшения с правильными индексами (на Bold_id, DistanceMeters, PseudoDistanceAsCostKm), помните, что большее количество индексов увеличивает размер базы данных.

person Daniel Luyo    schedule 16.03.2010
comment
Самое большое улучшение произошло при индексации этих полей, спасибо. - person Roland Bengtsson; 17.03.2010

arrow_upward
2
arrow_downward

Я предполагаю, что bold_id является вашим ключом и, таким образом, правильно проиндексирован.
Тогда замена подвыборки и not...in на соединение может помочь оптимизатору.

SELECT DistanceAsMeters, Bold_Id, Created, AddressFrom.CityName_CO as FromCity, AddressTo.CityName_CO as ToCity
FROM AddrDistance
LEFT JOIN Address AddressFrom ON AddrDistance.FromAddress = AddressFrom.Bold_Id
LEFT JOIN Address AddressTo ON AddrDistance.ToAddress = AddressTo.Bold_Id
LEFT JOIN DistanceQueryTask ON AddrDistance.bold_id = DistanceQueryTask.bold_id
Where  DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0
  and DistanceQueryTask.bold_id is null
Order By Created Desc
person Francesca    schedule 16.03.2010
comment
Этот запрос дает 0 строк, вероятно, потому, что DistanceQueryTask.bold_id никогда не имеет значение null, поскольку это ключ для идентификации строк. Кроме того, bold_id для AddrDistance и DistanceQueryTask никогда не может совпадать. - person Roland Bengtsson; 17.03.2010
comment
@Roland: левое соединение в сочетании с where right_table.Key is null дает только записи левой таблицы, которые не совпадают с правой таблицей (те, где у вас были бы нулевые столбцы right_table без предложения where) - person Francesca; 17.03.2010

arrow_upward
2
arrow_downward

Создайте индекс для этой части: (DistanceAsMeters = 0 и PseudoDistanceAsCostKm = 0), потому что для нее выполняется (плохое) сканирование таблицы: ADDRDISTANCE NATURAL

И попробуйте использовать объединение вместо подвыборки, как заявил Франсуа.

person André    schedule 17.03.2010

arrow_upward
2
arrow_downward

Как предлагают Даниэль и Андре, очень помогает индекс.
Я бы предложил этот индекс (DistanceMeters, PseudoDistanceAsCostKm, Bold_id), поскольку первые 2 части индекса постоянны, а затем небольшая часть индекса необходима для читать.

Если существует факт, что FromAddress и/или ToAddress существуют, вы можете изменить LEFT JOIN на INNER JOIN, потому что это часто быстрее (оптимизатор запросов может сделать некоторые предположения).

person BennyBechDk    schedule 17.03.2010