У меня возникли некоторые проблемы с производительностью из-за того, что для отчета выполняется полное сканирование некоторых больших таблиц. Я сузил список до этого раздела запроса, но не могу понять, как избежать сканирования без изменения результатов.
Чтобы объяснить, у нас есть система архивации данных, которая ежедневно копирует данные из реальной таблицы в архивную. Данные не удаляются из активной таблицы до тех пор, пока не пройдет определенный период времени. Это приводит к состоянию, когда в активной таблице и в архивной таблице будут одни и те же строки, но данные в строках могут не совпадать.
Это исключает запрос UNION (который устраняет полное сканирование). Требования к отчету для отображения данных в реальном времени, поэтому я также не могу запросить только архивную таблицу.
Любые идеи? Вот запрос. Первичные ключи обеих таблиц — это DetailIdent, но у меня есть индекс для OrderIdent, так как это внешний ключ обратно к родительской таблице. Как видите, мы берем результаты основной таблицы, если они существуют, в противном случае мы возвращаемся к архивным данным.
SELECT COALESCE(RegOD.OrderIdent, ArcOD.OrderIdent) AS OrderIdent,
COALESCE(RegOD.Quantity, ArcOD.Quantity) AS Quantity,
COALESCE(RegOD.LoadQuan, ArcOD.LoadQuan) AS LoadQuan,
COALESCE(RegOD.ShipQuan, ArcOD.ShipQuan) AS ShipQuan,
COALESCE(RegOD.RcvdQuan, ArcOD.RcvdQuan) AS RcvdQuan,
COALESCE(RegOD.UOM, ArcOD.UOM) AS UOM,
COALESCE(RegOD.SkidType, ArcOD.SkidType) AS SkidType,
COALESCE(RegOD.Product, ArcOD.Product) AS Product,
COALESCE(RegOD.PkgCode, ArcOD.PkgCode) AS PkgCode
FROM OrderDetail RegOD
FULL JOIN dbo.ArcOrderDtl ArcOD
ON ArcOD.DetailIdent = RegOD.DetailIdent
WHERE COALESCE(RegOD.OrderIdent, ArcOD.OrderIdent) = 717010
Предикат фильтрации COALESCE(RegOD.OrderIdent,ArcOD.OrderIdent) = 717010
убивает производительность и заставляет движок сначала выполнять полное сканирование, а затем фильтровать данные.
Перефразируйте функцию COALESCE()
и дайте двигателю сделать свою работу. Если повезет, движок будет достаточно умен, чтобы найти оптимизацию. В этом случае запрос может иметь вид:
SELECT
COALESCE(RegOD.OrderIdent,ArcOD.OrderIdent) AS OrderIdent,
COALESCE(RegOD.Quantity,ArcOD.Quantity) AS Quantity,
COALESCE(RegOD.LoadQuan,ArcOD.LoadQuan) AS LoadQuan,
COALESCE(RegOD.ShipQuan,ArcOD.ShipQuan) AS ShipQuan,
COALESCE(RegOD.RcvdQuan,ArcOD.RcvdQuan) AS RcvdQuan,
COALESCE(RegOD.UOM,ArcOD.UOM) AS UOM,
COALESCE(RegOD.SkidType,ArcOD.SkidType) AS SkidType,
COALESCE(RegOD.Product,ArcOD.Product) AS Product,
COALESCE(RegOD.PkgCode,ArcOD.PkgCode) AS PkgCode
FROM OrderDetail RegOD
FULL JOIN dbo.ArcOrderDtl ArcOD ON ArcOD.DetailIdent = RegOD.DetailIdent
WHERE RegOD.OrderIdent = 717010 or ArcOD.OrderIdent = 717010
Если движок не оптимизирует вариант № 1 выше, вы все равно можете попробовать объединить левое соединение с правым антисоединением вместо написания полного соединения (они эквивалентны). Это определенно более многословно, но в данном случае ясно показывает движку, что делать. Этот запрос может выглядеть так:
SELECT -- left join here
COALESCE(RegOD.OrderIdent,ArcOD.OrderIdent) AS OrderIdent,
COALESCE(RegOD.Quantity,ArcOD.Quantity) AS Quantity,
COALESCE(RegOD.LoadQuan,ArcOD.LoadQuan) AS LoadQuan,
COALESCE(RegOD.ShipQuan,ArcOD.ShipQuan) AS ShipQuan,
COALESCE(RegOD.RcvdQuan,ArcOD.RcvdQuan) AS RcvdQuan,
COALESCE(RegOD.UOM,ArcOD.UOM) AS UOM,
COALESCE(RegOD.SkidType,ArcOD.SkidType) AS SkidType,
COALESCE(RegOD.Product,ArcOD.Product) AS Product,
COALESCE(RegOD.PkgCode,ArcOD.PkgCode) AS PkgCode
FROM OrderDetail RegOD
LEFT JOIN dbo.ArcOrderDtl ArcOD ON ArcOD.DetailIdent = RegOD.DetailIdent
WHERE RegOD.OrderIdent = 717010
UNION ALL
SELECT -- right anti-join here
OrderIdent,
Quantity,
LoadQuan,
ShipQuan,
RcvdQuan,
UOM,
SkidType,
Product,
PkgCode
FROM dbo.ArcOrderDtl ArcOD
LEFT JOIN OrderDetail RegOD ON ArcOD.DetailIdent = RegOD.DetailIdent
WHERE ArcOD.OrderIdent = 717010 and RegOD.DetailIdent IS NULL
Для варианта № 2, поскольку второй запрос получает все только в архиве, не должен ли я переключить первый запрос, чтобы просто смотреть на основную таблицу?
@Spivonious Я так не думаю. Полное внешнее соединение рассматривает обе таблицы отдельно. Вот почему вариант № 2 описывает оптимизацию для движка, поэтому оба подзапроса можно запускать отдельно и объединять в конце.
Вам нужны все строки для OrderIdent, но строки (определяемые DetailIdent) могут быть либо в OrderDetail, либо в ArcOrderDtl, либо в обоих. Вы хотите отдать приоритет строкам OrderDetail, если они существуют.
Следовательно, одна из идей состоит в том, чтобы выбрать все строки, а затем ранжировать их, присвоив OrderDetail более высокий ранг, чем ArcOrderDtl. Затем используйте TOP WITH TIES
, чтобы получить все строки с лучшим ранжированием и отклонить остальные.
SELECT TOP(1) WITH TIES
OrderIdent, Quantity, LoadQuan, ShipQuan, RcvdQuan, UOM, SkidType, Product, PkgCode
FROM
(
SELECT
DetailIdent, OrderIdent, Quantity, LoadQuan, ShipQuan, RcvdQuan, UOM, SkidType,
Product, PkgCode, 1 AS priority
FROM OrderDetail
WHERE OrderIdent = 717010
UNION ALL
SELECT
DetailIdent, OrderIdent, Quantity, LoadQuan, ShipQuan, RcvdQuan, UOM, SkidType,
Product, PkgCode, 2 AS priority
FROM dbo.ArcOrderDtl
WHERE OrderIdent = 717010
) unioned
ORDER BY RANK() (PARTITION BY DetailIdent ORDER BY priority);
Мне тоже нравится этот, но может быть трудно включить. Запрос, который я разместил, вложен внутри представления.
Я предполагаю, что обе таблицы имеют общий первичный ключ OrderIdent
и DetailIdent
(или, по крайней мере, уникальный индекс для этих полей). Если да, то сначала вычисляем все ключи из архивной таблицы, которых нет в живой таблице, а затем получаем из обеих таблиц те, которые нас интересуют.
Вы эффективно пробежитесь по обеим таблицам дважды, но индексы (и кэширование) сделают это достаточно быстрым, а операции очень простыми.
SELECT DetailIdent
INTO #archiveRows
FROM ArcOrderDtl ArcOD
WHERE OrderIdent = 717010
EXCEPT
SELECT DetailIdent
FROM OrderDetail RegOD
WHERE OrderIdent = 717010
CREATE UNIQUE CLUSTERED INDEX uq0_archiveRows ON #archiveRows (DetailIdent) WITH (FILLFACTOR = 100)
SELECT -- live
OrderIdent,
Quantity,
LoadQuan,
ShipQuan,
RcvdQuan,
UOM,
SkidType,
Product,
PkgCode,
FROM OrderDetail RegOD
WHERE RegOD.OrderIdent = 717010
UNION ALL
SELECT -- archive
OrderIdent,
Quantity,
LoadQuan,
ShipQuan,
RcvdQuan,
UOM,
SkidType,
Product,
PkgCode
FROM dbo.ArcOrderDtl ArcOD
JOIN #archiveRows t
ON t.DetailIdent = ArcOD.DetailIdent
WHERE ArcOD.OrderIdent = 717010
PS: Если вы по какой-то причине не можете использовать временные таблицы, я думаю, вы могли бы вместо этого поместить их в CTE; судя по небольшому количеству возвращенных фактических строк, которые, я думаю, тоже должны работать нормально. (В основном я склонен «продвигать» временные таблицы, потому что их легко читать, их можно индексировать, а оптимизатор создаст по ним статистику и использует ее для следующих шагов!)
Просто перечитайте OP и обратите внимание, что PK просто на DetailIdent
, что означает, что последний WHERE ArcOD.OrderIdent = 717010
на самом деле лишний. Опять же, я сомневаюсь, что вы заметите =)
Хорошее предложение, но, к сожалению, этот запрос скрыт в представлении, поэтому временные таблицы не являются реалистичным вариантом (хотя я полагаю, что TVF может вступить в игру).
@Spivonious Как я уже сказал, вы можете легко переместить часть временной таблицы в выражение общей таблицы или даже в подзапрос, если хотите; таким образом, включение этого в существующее представление не должно быть слишком сложным. Вам нужно будет отказаться от курса CREATE INDEX, но, учитывая количество данных здесь, это не должно быть проблемой.
Мне это нравится. Вариант № 1 по-прежнему приводил к полному сканированию, но вариант № 2 использовал поиск.