SQL — избегайте полного сканирования при объединении архивных таблиц

У меня возникли некоторые проблемы с производительностью из-за того, что для отчета выполняется полное сканирование некоторых больших таблиц. Я сузил список до этого раздела запроса, но не могу понять, как избежать сканирования без изменения результатов.

Чтобы объяснить, у нас есть система архивации данных, которая ежедневно копирует данные из реальной таблицы в архивную. Данные не удаляются из активной таблицы до тех пор, пока не пройдет определенный период времени. Это приводит к состоянию, когда в активной таблице и в архивной таблице будут одни и те же строки, но данные в строках могут не совпадать.

Это исключает запрос 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

SQL — избегайте полного сканирования при объединении архивных таблиц

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
3
0
74
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Предикат фильтрации COALESCE(RegOD.OrderIdent,ArcOD.OrderIdent) = 717010 убивает производительность и заставляет движок сначала выполнять полное сканирование, а затем фильтровать данные.

Вариант 1. Перефразируйте функцию COALESCE().

Перефразируйте функцию 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

Вариант 2. Комбинируйте левое соединение с правым антисоединением вместо использования полного соединения.

Если движок не оптимизирует вариант № 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

Мне это нравится. Вариант № 1 по-прежнему приводил к полному сканированию, но вариант № 2 использовал поиск.

Spivonious 29.03.2022 21:24

Для варианта № 2, поскольку второй запрос получает все только в архиве, не должен ли я переключить первый запрос, чтобы просто смотреть на основную таблицу?

Spivonious 29.03.2022 21:33

@Spivonious Я так не думаю. Полное внешнее соединение рассматривает обе таблицы отдельно. Вот почему вариант № 2 описывает оптимизацию для движка, поэтому оба подзапроса можно запускать отдельно и объединять в конце.

The Impaler 29.03.2022 22:38

Вам нужны все строки для 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);

Мне тоже нравится этот, но может быть трудно включить. Запрос, который я разместил, вложен внутри представления.

Spivonious 29.03.2022 21:28

Я предполагаю, что обе таблицы имеют общий первичный ключ 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 на самом деле лишний. Опять же, я сомневаюсь, что вы заметите =)

deroby 25.03.2022 20:27

Хорошее предложение, но, к сожалению, этот запрос скрыт в представлении, поэтому временные таблицы не являются реалистичным вариантом (хотя я полагаю, что TVF может вступить в игру).

Spivonious 29.03.2022 21:24

@Spivonious Как я уже сказал, вы можете легко переместить часть временной таблицы в выражение общей таблицы или даже в подзапрос, если хотите; таким образом, включение этого в существующее представление не должно быть слишком сложным. Вам нужно будет отказаться от курса CREATE INDEX, но, учитывая количество данных здесь, это не должно быть проблемой.

deroby 31.03.2022 13:30

Другие вопросы по теме