У меня есть следующий запрос:
SELECT COUNT(*)
FROM Address adr INNER JOIN
Audit a on adr.UniqueId = a.UniqueId
Запрос берет довольно долго для завершения. Я чувствую себя немым, но есть ли какой-либо способ оптимизировать его? Я хочу считать все записи адреса, которые имеют базовое auditable.
Править: все Ваши исходные данные очень ценятся, вот еще некоторые детали:
Поскольку у вас есть два набора данных, упорядоченных по одному и тому же значению ... пробовали ли вы объединение слиянием вместо соединение вложенного цикла?
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(*)
FROM Address adr INNER JOIN
Auditable a on adr.UniqueId = a.UniqueId
OPTION (LOOP JOIN)
SELECT COUNT(*)
FROM Address adr INNER JOIN
Auditable a on adr.UniqueId = a.UniqueId
OPTION (MERGE JOIN)
SELECT COUNT(*)
FROM Address adr INNER JOIN
Auditable a on adr.UniqueId = a.UniqueId
OPTION (HASH JOIN)
Редактировать:
Эти объяснения являются концептуальными. SQL Server может выполнять более сложные операции, чем показывают мои примеры. Это концептуальное понимание, сопоставленное с измерением времени и логического ввода-вывода с помощью команд SET STATISTICS и изучением планов выполнения запросов, составляет основу моей техники оптимизации запросов (выросшей за четыре года). Пусть он послужит вам так же хорошо, как и мне.
Подготовка
NestedLoop
Алгоритм вложенного цикла выполняет итерацию по родительскому набору данных, а затем выполняет поиск в дочернем наборе данных один раз для каждого родителя, что приводит к затратам: m * log (n)
Слияние
Алгоритм объединения слиянием выполняет итерацию родительского набора данных один раз и дочернего набора данных один раз, в результате чего стоимость: m + n. Он полагается на заказываемые данные. Если вы попросите объединить неупорядоченные данные, вам придется выполнить операцию упорядочивания! В результате стоимость составит (m * log (m)) + (n * log (n)) + m + n. В некоторых случаях даже это может быть лучше, чем вложенный цикл.
Хэш
Алгоритм хэш-соединения выполняет итерацию родительского набора данных один раз и дочернего набора данных один раз, в результате чего стоимость: m + n. Он полагается на наличие достаточно большой карточной таблицы, чтобы вместить все содержимое родительского набора данных.
Настоящая проблема - соединение с вложенным циклом. Для каждых 1,4 миллиона строк в таблице адресов вы выполняете поиск индекса в таблице Auditble. Это означает 1,4 млн чтений корневого блока, блока ветвления и конечного блока, всего 4,2 млн чтений блока. Весь индекс, вероятно, состоит всего из 5К блоков или около того ... он должен выполнять хеш-соединение, чтобы он считал оба индекса один раз и хешировал их.
Если вы думаете, что эти таблицы большие, я предполагаю, что это небольшая коробка без большого количества памяти. Вы должны убедиться, что у вас достаточно памяти, выделенной для размещения всего индекса в памяти, чтобы сделать хеш-соединение эффективным.
Я бы сказал, что отсутствует индекс по внешнему ключу.
1.4 миллиона и 4 миллиона - это не большие таблицы, они маленькие. Скажите масштабно, когда вы просматриваете 500 миллионов записей, пожалуйста.
Для реального ответа нам нужен план выполнения / план запроса, чтобы мы могли видеть, что происходит.
И было бы неплохо узнать, что такое «Long» в вашем мире (учитывая, что вы думаете, что 4 миллиона строк - это много). На этот вопрос никогда не ответят за 1 секунду - так чего же вы ожидаете и что происходит?
Но держу пари, что у вас отсутствует индекс. Короче говоря, я бы начал указывать на оборудование (потому что я тоже видел в этом причину плохой производительности).
Выполнение предложения EXISTS обходится дешевле, чем INNER JOIN.
select COUNT(adr.UniqueId)
from Addresses adr
where EXISTS (
select 1
from Auditables aud
where aud.UniqueId = adr.UniqueId
)
Это соответствует вашим потребностям?
Примечание. Гиды очень дороги для движка базы данных.
Не уверен, будет ли это быстрее, но вы можете попробовать следующее
SELECT COUNT(adr.UniqueID) FROM Address adr INNER JOIN Auditable a on adr.UniqueId = a.UniqueId
Это должно дать вам такое же количество, потому что unqieieid никогда не будет нулевым.
Для больших таблиц, таких как эти, вы можете захотеть разделить данные для увеличения производительности запросов. Также, если вы еще не сделали этого, попробуйте запустить Tuning Advisor, чтобы узнать, есть ли дополнительные индексы, которые могут оказаться полезными. Кроме того, проводили ли вы в последнее время реорганизацию кластерных индексов - является ли это задачей, входящей в пакет технического обслуживания? Во многих случаях это также значительно повышает производительность.
Является ли Auditable.UniqueID ссылкой внешнего ключа на Address.UniqueID, что означает, что в Auditable нет значений, которые также не существуют в Address?
Если да, то это может работать и может быть быстрее:
SELECT COUNT(DISTINCT Auditable.UniqueID)
FROM Auditable
Примечание: Это также предполагает, что UniqueID уникален (/первичный ключ) в таблице Address, но не уникален в таблице Auditable
.Если этот запрос выполняется часто и он должен быть очень быстрым, создайте его материализованное индексированное представление. Будут небольшие накладные расходы на INSERT/UPDATE/DELETEs, но этот запрос будет почти мгновенным. Агрегаты могут быть предварительно вычислены и сохранены в индексе, чтобы свести к минимуму дорогостоящие вычисления во время выполнения запроса.
Повышение производительности с помощью индексированных представлений SQL Server 2005