Почему в моем плане выполнения есть вид?

У меня есть запрос sql ниже, который работает очень медленно. Я просмотрел план выполнения и заявляет, что сортировка по Files.OrderId - это операция с наивысшей стоимостью (53%). Почему это происходит, если я не заказываю OrderId нигде? Могу ли я создать индекс для File.OrderId?

План выполнения, если кому-то это интересно.

with custOrders as
(
    SELECT c.firstName + ' ' + c.lastname as Customer, c.PartnerId , c.CustomerId,o.OrderId,o.CreateDate, c.IsPrimary
    FROM Customers c
    LEFT JOIN CustomerRelationships as cr
        ON c.CustomerId = cr.PrimaryCustomerId
    INNER JOIN Orders as o
       ON c.customerid = o.customerid 
           OR (cr.secondarycustomerid IS NOT NULL AND o.customerid = cr.secondarycustomerid)
    where c.createdate >= @FromDate + ' 00:00' 
       AND c.createdate <= @ToDate + ' 23:59' 
),
 temp as
(
SELECT Row_number() 
         OVER ( 
           ORDER BY c.createdate DESC)                    AS 'row_number', 
       c.customerid as customerId, 
       c.partnerid as partnerId, 
       c.Customer, 
       c.orderid as OrderId, 
       c.createdate as CreateDate, 
       Count(f.orderid)                                   AS FileCount, 
       dbo.Getparentcustomerid(c.isprimary, c.customerid) AS ParentCustomerId, 
       au.firstname + ' ' + au.lastname                   AS Admin, 
       '' as blank, 
       0  as zero
FROM   custOrders c 
       INNER JOIN files f 
               ON c.orderid = f.orderid 
       INNER JOIN admincustomers ac 
               ON c.customerid = ac.customerid 
       INNER JOIN adminusers au 
               ON ac.adminuserid = au.id 
       INNER JOIN filestatuses s 
               ON f.statusid = s.statusid 
WHERE  ac.adminuserid IS NOT NULL 
       AND f.statusid NOT IN ( 5, 6 ) 
GROUP  BY c.customerid, 
          c.partnerid, 
          c.Customer, 
          c.isprimary, 
          c.orderid, 
          c.createdate, 
          au.firstname, 
          au.lastname 
)

Ответ 1

SQL Server имеет три алгоритма выбора, когда нужно объединить две таблицы. Nested-Loops-Join, Hash-Join и Sort-Merge-Join. Какой из них он выбирает, он основывается на сметах. В этом случае он полагал, что на основе имеющейся у него информации Сорт-Merge-Join был правильным выбором.

В планах выполнения SQL Server Sort-Merge разделяется на два оператора: сортировка и объединение-объединение, поскольку операция сортировки может не понадобиться, например, если данные уже отсортированы.

Для получения дополнительной информации о присоединениях ознакомьтесь с моей серией ссылок здесь: http://sqlity.net/en/1146/a-join-a-day-introduction/ Статья о Sort-Merg-Join находится здесь: http://sqlity.net/en/1480/a-join-a-day-the-sort-merge-join/

Чтобы сделать запрос быстрее, я сначала посмотрю на индексы. В запросе у вас есть куча кластерных индексов. Если вы можете заменить несколько из них поисками, вам, скорее всего, будет лучше. Также проверьте, соответствуют ли оценки SQL Server фактическим подсчетам строк в фактическом плане выполнения. Если они далеки, SQL Server часто делает неправильный выбор. Поэтому предоставление более качественных статистических данных также поможет вам выполнить запрос производительности.

Ответ 2

SQL Server выполняет сортировку, чтобы включить объединение слияния между набором данных справа от этого оператора сортировки и записей в таблице Orders. Объединение слияния является очень эффективным способом объединения всех записей в наборе данных, но для этого требуется, чтобы каждый набор данных был соединен в соответствии с ключами соединения и в том же порядке.

Поскольку ключ PK_Orders уже упорядочен OrderID, SQL Server решил воспользоваться этим, сортируя другой конец соединения (другой материал справа от сортировки), чтобы два набора данных могли быть объединены в этот момент в плане. Общей альтернативой объединению соединения является хеш-соединение, но это не поможет вам, потому что вместо этого вы вместо этого должны использовать дорогостоящий оператор хеш-соединения вместо сортировки и слияния. Оптимизатор запросов определил, что сортировка и слияние будут более эффективными в этом случае.

Коренной причиной дорогостоящего этапа плана является необходимость объединения всех записей из таблицы заказов в набор данных. Есть ли способ ограничить записи, поступающие из таблицы files? Индекс на files.statusid может оказаться полезным, если записи не в 5,6 меньше 10% от общего размера таблицы.

QO считает, что большая часть записей будет отфильтрована в конце. Попытайтесь вытолкнуть столько условий фильтра обратно в источники записи, чтобы в середине плана было обработано меньше записей.

EDIT: Я забыл упомянуть, очень полезно иметь план выполнения, на который мы можем смотреть. Есть ли способ получить реальный результат выполнения фактического, чтобы увидеть реальное количество записей, проходящих через эти операторы? Иногда подсчитанные подсчеты записей могут немного отличаться.

РЕДАКТИРОВАТЬ: Глядя глубже в поле предиката оператора фильтра от 2-го до последнего фильтра, суммируется:

c.CustomerId=o.CustomerId
OR o.CustomerId=cr.SecondaryCustomerId AND cr.SecondaryCustomerId IS NOT NULL

Похоже, что SQL Server создает кросс-соединение между всеми возможными совпадающими записями между Orders и Customers до этой точки в запросе (план справа от второго до последнего оператора фильтра), а затем просматривает каждая запись с этим условием, чтобы убедиться, что она действительно соответствует. Обратите внимание, как линия, попадающая в фильтр, действительно жирная, а линия выходит очень тонкая? Это связано с тем, что подсчитанное количество строк идет от 21k до 4 после этого оператора. Забудьте о том, что я сказал ранее, это, вероятно, главная проблема в плане. Даже если в этих столбцах есть индексы, SQL Server не может их использовать, потому что условие соединения слишком сложное. Это заставляет план объединить все записи вместе вместо того, чтобы искать только те, которые вам нужны, потому что он не может сразу использовать полный предикат соединения.

Моя первая мысль состоит в том, чтобы перефразировать CTE custOrders как объединение двух наборов данных: один с помощью CustomerId и один с помощью SecondaryCustomerId для соединения. Это будет дублировать работу остальной части CTE, но если это позволит правильно использовать индексы, это может стать большой победой.

Ответ 3

Я думаю, что для этого соединения происходит сортировка:

FROM   custOrders c 
       INNER JOIN files f 
               ON c.orderid = f.orderid

Я бы создал индекс для файлов, который включает в себя порядковый номер столбца и statusid, поскольку запрос также использует столбец statusid.

Вы также можете рассмотреть следующие изменения:

Вам не нужно "ac.adminuserid IS NOT NULL", поскольку это покрывается внутренним соединением между администраторами и администраторами
Измените тест "f.statusid NOT IN (5, 6)" на положительное условие (например, In), поскольку отрицательные условия дороже обрабатывать.

Ответ 4

Я знаю, что этот вопрос довольно старый, однако у меня была эта же проблема, и я понял, что совершенно другая причина, по которой мои таблицы внезапно замедлились. Симптомы были одинаковыми, медленными, чтобы обновлять представления, которые ранее были молниеносно. "Сортировка" дает стоимость 40%. Это решение может оказаться полезным для кого-то, и это просто. При подключении к столам убедитесь, что вы присоединяетесь к принципу "вроде как". Я присоединился к двум таблицам по ID. Однако в одной таблице мой идентификатор был установлен как int, а другой - как nvarchar. Я исправил это, чтобы они оба были определены как один и тот же тип, и представление вернулось к молниеносной скорости.

Мы надеемся, что это поможет кому-то еще не потратить неделю на то, чтобы выяснить, что случилось с SQL, когда это действительно момент PEBKAC.

(Проблема между клавиатурой и стулом)