Извлечение n строк на группу

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

Например, мне может потребоваться перечислить «n» наивысших или самых низких значений заказа для каждого клиента.

В более сложных случаях количество строк в списке может варьироваться для каждой группы (определяется атрибутом группировки /родительской записи). Эта часть, безусловно, необязательна /для дополнительного кредита и не предназначена для того, чтобы отговаривать людей от ответа.

Каковы основные возможности для решения этих проблем в SQL Server 2005 и более поздних версиях? Каковы основные преимущества и недостатки каждого метода?

Примеры AdventureWorks (для ясности, необязательно)

  1. Перечислите пять последних последних дат и идентификаторов транзакций из таблицы TransactionHistory для каждого продукта, который начинается с буквы от M до R включительно.
  2. То же самое, но с n строками истории для каждого продукта, где n в пять раз превышает атрибут DaysToManufacture.
  3. То же самое, для частного случая, когда требуется одна строка истории для каждого продукта (единственная последняя запись с помощью TransactionDate), tie-break на TransactionID.
76 голосов | спросил Paul White 17 WedEurope/Moscow2014-12-17T17:33:48+03:00Europe/Moscow12bEurope/MoscowWed, 17 Dec 2014 17:33:48 +0300 2014, 17:33:48

6 ответов


56

Давайте начнем с основного сценария.

Если я хочу получить некоторое количество строк из таблицы, у меня есть два основных варианта: ранжирующие функции; или TOP.

Сначала рассмотрим весь набор из Production.TransactionHistory для конкретного ProductID:

SELECT h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
ГДЕ h.ProductID = 800;

Это возвращает 418 строк, и план показывает, что он проверяет каждую строку в таблице, которая ищет это - неограниченное кластерное сканирование индексов, с предикатом для предоставления фильтра. 797 читает здесь, что является уродливым.

Дорогое сканирование с

Итак, давайте будем честными и создадим индекс, который был бы более полезным. Наши условия требуют совпадения равенства на ProductID, а затем поиск последнего из TransactionDate. Нам понадобится возвращенный TransactionID, так что давайте перейдем к: CREATE INDEX ix_FindingMostRecent ON Production.TransactionHistory (ProductID, TransactionDate) INCLUDE (TransactionID);.

Сделав это, наш план значительно изменится и снизит показания до 3. Таким образом, мы уже улучшаем вещи более чем на 250 раз ...

Улучшенный план

Теперь, когда мы выровняли игровое поле, давайте посмотрим на верхние параметры ранжирования и TOP.

С номером AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER () OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
)
SELECT TransactionID, ProductID, TransactionDate
FROM Numbered
WHERE RowNum <= 5;

SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

Два плана - основной TOP \ RowNum

Вы заметите, что второй (TOP) запрос намного проще первого, как в запросе, так и в плане. Но очень важно, что оба они используют TOP, чтобы ограничить количество строк, фактически выведенных из индекса. Затраты - это только оценки и стоит игнорировать, но вы можете увидеть много сходства в двух планах, с версией ROW_NUMBER (), выполняющей крошечную дополнительную работу для присвоения чисел и фильтрации соответственно, и оба запроса заканчиваются тем, что делают всего 2 чтения для выполнения своей работы. Оптимизатор запросов, безусловно, распознает идею фильтрации в поле ROW_NUMBER (), понимая, что он может использовать оператор Top для игнорирования строк, которые не понадобятся. Оба этих запроса достаточно хороши - TOP не намного лучше, чем это стоит изменить код, но он проще и, вероятно, более понятен для новичков.

Итак, эта работа работает над одним продуктом. Но нам нужно подумать о том, что произойдет, если нам нужно сделать это через несколько продуктов.

Итеративный программист рассмотрит идею цикла через интересующие вас продукты и вызовет этот запрос несколько раз, и нам действительно удастся написать запрос в этой форме - не используя курсоры, но используя ОТНОСИТЬСЯ. Я использую OUTER APPLY, полагая, что мы можем вернуть Product с NULL, если для него нет транзакций.

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
ИЗ
Производство.Продукт p
ВНЕШНЕЕ ПРИМЕНЕНИЕ (
    SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name> = 'M' И p.Name <'S';

План для этого - метод итеративных программистов - вложенный цикл, выполняющий операцию «Вверх» и «поиск» (те, которые мы читали ранее) для каждого продукта. Это дает 4 чтения против Продукта и 360 против TransactionHistory.

APPLY plan

Используя ROW_NUMBER (), метод должен использовать PARTITION BY в предложении OVER, чтобы мы перезапустили нумерацию для каждого продукта , Затем его можно отфильтровать, как и раньше. План заканчивается совсем другим. Логические чтения примерно на 15% ниже в TransactionHistory, с полным сканированием индекса, чтобы вывести строки.

С номером AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER () OVER (PARTITION by h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name> = 'M' И p.Name <'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

Значительно, однако, этот план имеет дорогостоящий оператор сортировки. Объединение Merge не поддерживает порядок строк в TransactionHistory, данные должны использоваться, чтобы находить рябины. Это меньше читает, но этот блокирующий Сорт может чувствовать себя болезненным. Используя APPLY, вложенная петля очень быстро вернет первые строки после нескольких чтений, но с помощью Sort, ROW_NUMBER () будет возвращать строки только после того, как большая часть работа завершена.

Интересно, что если запрос ROW_NUMBER () использует INNER JOIN вместо LEFT JOIN, тогда появляется другой план.

ROW_NUMBER () с INNER JOIN

В этом плане используется вложенный цикл, как и при использовании APPLY. Но нет оператора «Топ», поэтому он тянет все транзакции для каждого продукта и использует намного больше чтений, чем раньше - 492 читается против TransactionHistory. Для этого нет веской причины не выбирать вариант объединения слиянием, поэтому я предполагаю, что план был рассмотрен как «Достаточно хорошо». Тем не менее - он не блокируется, что приятно - просто не так хорошо, как APPLY.

В обоих случаях столбец PARTITION BY, который я использовал для ROW_NUMBER (), был h.ProductID, потому что я хотел дать QO - возможность получения значения RowNum перед присоединением к таблице Product. Если я использую p.ProductID, мы видим тот же план фигуры, что и в варианте INNER JOIN.

С номером AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER () OVER (PARTITION by p.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name> = 'M' И p.Name <'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

Но оператор Join говорит «Left Outer Join» вместо «Inner Join». Количество считываний по-прежнему составляет чуть менее 500 прочтений в таблице TransactionHistory.

PARTITION BY на p.ProductID вместо h.ProductID

В любом случае - вернемся к вопросу ...

Мы ответили на вопрос 1 , с двумя вариантами, которые вы могли бы выбрать. Лично мне нравится опция APPLY.

Чтобы расширить это, чтобы использовать переменное число ( вопрос 2 ), необходимо изменить соответствующий код 5. О, и я добавил еще один индекс, так что был указатель на Production.Product.Name, который включал столбец DaysToManufacture.

С номером AS
(
SELECT p.Name, p.ProductID, p.DaysToManufacture, h.TransactionID, h.TransactionDate, ROW_NUMBER () OVER (PARTITION by h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name> = 'M' И p.Name <'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
ИЗ
Производство.Продукт p
ВНЕШНЕЕ ПРИМЕНЕНИЕ (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name> = 'M' И p.Name <'S';

И оба плана почти идентичны тем, чем они были раньше!

Переменные строки

Опять же, проигнорируйте сметные затраты - но мне все еще нравится сценарий TOP, поскольку он намного проще, и в плане нет оператора блокировки. Чтения на TransactionHistory меньше, из-за большого количества нулей в DaysToManufacture, но в реальной жизни я сомневаюсь, что мы будем выбирать эту колонку. ;)

Один из способов избежать блока - разработать план, который обрабатывает бит ROW_NUMBER () справа (в плане) соединения. Мы можем убедить это в том, что мы делаем соединение за пределами CTE.

С номером AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER () OVER (PARTITION BY ProductID ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
)
SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM Production.Product p
LEFT JOIN Numbered t ON t.ProductID = p.ProductID
    И t.RowNum <= 5 * p.DaysToManufacture
WHERE p.Name> = 'M' И p.Name <'S';

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

Соединение за пределами CTE

Обратите внимание на Compute Scalar, который извлекает данные из ПродуктаТаблица. Это выработает значение 5 * p.DaysToManufacture. Это значение не передается в ветку, которая извлекает данные из таблицы TransactionHistory, она используется в Merge Join. Как остаточный.

Sneaky Residual!

Таким образом, Merge Join потребляет ВСЕ строки, а не только первые, но многие из них необходимы, но все они, а затем выполняют остаточную проверку. Это опасно по мере увеличения количества транзакций. Я не поклонник этого сценария - остаточные предикаты в Merge Joins могут быстро перерасти. Еще одна причина, по которой я предпочитаю сценарий APPLY /TOP.

В частном случае, когда это ровно одна строка, для вопроса 3 , мы можем, очевидно, использовать одни и те же запросы, но с 1 вместо 5. Но тогда у нас есть дополнительная опция, которая заключается в использовании регулярных агрегатов.

SELECT ProductID, MAX (TransactionDate)
FROM Production.TransactionHistory
GROUP BY ProductID;

Подобный запрос был бы полезным началом, и мы могли бы легко изменить его, чтобы вытащить TransactionID также для целей тай-брейка (используя конкатенацию, которая затем будет разбита), но мы либо рассмотрим все индекс, или мы погружаемся в продукт по продукту, и мы не очень сильно улучшаем то, что было в этом сценарии.

Но я должен отметить, что мы рассматриваем конкретный сценарий здесь. С реальными данными и с помощью стратегии индексирования, которая может быть не идеальной, пробег может значительно отличаться. Несмотря на то, что мы видели, что APPLY здесь сильный, в некоторых ситуациях он может быть медленнее. Он редко блокируется, хотя, поскольку он имеет тенденцию использовать вложенные петли, которые многие люди (включая меня) находят очень привлекательными.

Я не пытался исследовать параллелизм здесь или погрузился в вопрос 3, который я рассматриваю как особый случай, который люди редко хотят, основываясь на усложнении конкатенирования и расщепления. Главное здесь рассмотреть, что эти два варианта очень сильны.

Я предпочитаю APPLY. Понятно, что он хорошо использует оператор Top, и он редко вызывает блокировку.

ответил Rob Farley 22 MonEurope/Moscow2014-12-22T14:28:40+03:00Europe/Moscow12bEurope/MoscowMon, 22 Dec 2014 14:28:40 +0300 2014, 14:28:40
40

Типичный способ сделать это в SQL Server 2005 и выше - использовать функции CTE и windowing. Для первой группы на группу вы можете просто использовать ROW_NUMBER () с предложением PARTITION и фильтровать его во внешнем запросе. Так, например, верхние 5 самых последних заказов для каждого клиента могут отображаться следующим образом:

DECLARE @top INT;
SET @top = 5;

; С grp AS
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER () OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   От dbo.Orders
)
SELECT CustomerID, OrderID, OrderDate
  FROM grp
  WHERE rn <= @top
  ORDER BY CustomerID, OrderDate DESC;

Вы также можете сделать это с помощью CROSS APPLY:

DECLARE @top INT;
SET @top = 5;

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
КРЕСТ ПРИМЕНЯЕТСЯ
(
    SELECT TOP (@top) OrderID, OrderDate
    ОТ dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

С дополнительной опцией, описанной Paul, скажем, таблица Customers имеет столбец, указывающий, сколько строк должно быть включено для каждого клиента:

; WITH grp AS
(
   SELECT CustomerID, OrderID, OrderDate,
     rn = ROW_NUMBER () OVER
     (PARTITION BY CustomerID ORDER BY OrderDate DESC)
   От dbo.Orders
)
SELECT c.CustomerID, grp.OrderID, grp.OrderDate
  FROM grp
  INNER JOIN dbo.Customers AS c
  ON grp.CustomerID = c.CustomerID
  AND grp.rn <= c.Number_of_Recent_Orders_to_Show
  ORDER BY c.CustomerID, grp.OrderDate DESC;

И снова, используя CROSS APPLY и включив добавленную опцию, чтобы количество строк для клиента было продиктовано некоторым столбцом в таблице клиентов:

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
КРЕСТ ПРИМЕНЯЕТСЯ
(
    SELECT TOP (c.Number_of_Recent_Orders_to_Show) OrderID, OrderDate
    ОТ dbo.Orders AS o
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) AS o
ORDER BY c.CustomerID, o.OrderDate DESC;

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

Лично я предпочитаю CTE и оконные решения над CROSS APPLY /TOP, потому что они лучше разделяют логику и более интуитивно понятны (для меня). В целом (как в этом случае, так и в моем общем опыте) подход CTE дает более эффективные планы (примеры ниже), но это не следует воспринимать как универсальную истину - вы всегда должны проверять свои сценарии, особенно если индексы изменились или данные значительно искажены.


Примеры AdventureWorks - без каких-либо изменений

  
  1. Перечислите пять последних последних дат и идентификаторов транзакций из таблицы TransactionHistory для каждого продукта, который начинается с буквы от M до R включительно.
  2.   
- CTE /OVER ()

; С историей AS
(
  SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
    rn = ROW_NUMBER () OVER
    (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
  FROM Production.Product AS p
  INNER JOIN Production.TransactionHistory AS t
  ON p.ProductID = t.ProductID
  WHERE p.Name> = N'M 'И p.Name <N'S»
)
SELECT ProductID, Name, TransactionID, TransactionDate
ОТ истории
ГДЕ rn <= 5;

- КРЕСТ ПРИМЕНЯЕТСЯ

SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
FROM Production.Product AS p
КРЕСТ ПРИМЕНЯЕТСЯ
(
  SELECT TOP (5) TransactionID, TransactionDate
  FROM Production.TransactionHistory
  WHERE ProductID = p.ProductID
  ORDER BY TransactionDate DESC
) AS t
WHERE p.Name> = N'M 'И p.Name <N'S ';

Сравнение этих двух показателей времени выполнения:

введите описание изображения здесь

CTE /OVER () план:

введите описание изображения здесь

CROSS APPLY:

введите описание изображения здесь>> </p>

<p> План CTE выглядит более сложным, но на самом деле он намного эффективнее. Не обращайте внимания на оценочные затраты% номеров, но сосредоточьтесь на более важных  фактических  наблюдениях, таких как гораздо меньшее количество чтений и гораздо более низкая продолжительность. Я также управлял ими без параллелизма, и это не было разницей. Метрики времени выполнения и план CTE (план <code>CROSS APPLY</code> остался прежним): </p>

<p> <img src =

  • То же самое, но с n строками истории для каждого продукта, где n в пять раз большеПользовательский атрибут DaysToManufacture.
  •   

    Здесь требуются незначительные изменения. Для CTE мы можем добавить столбец во внутренний запрос и фильтровать внешний запрос; для CROSS APPLY, мы можем выполнить вычисление внутри коррелированного TOP. Вы могли бы подумать, что это приведёт к эффективности решения CROSS APPLY, но в этом случае этого не произойдет. Запросы:

    - CTE /OVER ()
    
    ; С историей AS
    (
      SELECT p.ProductID, p.Name, p.DaysToManufacture, t.TransactionID, t.TransactionDate,
        rn = ROW_NUMBER () OVER
        (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC)
      FROM Production.Product AS p
      INNER JOIN Production.TransactionHistory AS t
      ON p.ProductID = t.ProductID
      WHERE p.Name> = N'M 'И p.Name <N'S»
    )
    SELECT ProductID, Name, TransactionID, TransactionDate
    ОТ истории
    WHERE rn <= (5 * DaysToManufacture);
    
    - КРЕСТ ПРИМЕНЯЕТСЯ
    
    SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
    FROM Production.Product AS p
    КРЕСТ ПРИМЕНЯЕТСЯ
    (
      SELECT TOP (5 * p.DaysToManufacture) TransactionID, TransactionDate
      FROM Production.TransactionHistory
      WHERE ProductID = p.ProductID
      ORDER BY TransactionDate DESC
    ) AS t
    WHERE p.Name> = N'M 'И p.Name <N'S ';
    

    Результаты выполнения:

    введите описание изображения здесь

    Параллельный CTE /OVER () план:

    введите описание изображения здесь>> </p>

<p> Однопоточный код CTE /<code>OVER ()</code>: </p>

<p> <img src =

  • То же самое, для частного случая, когда требуется одна строка истории для каждого продукта (единственная последняя запись с помощью TransactionDate), tie-break на TransactionID.
  •   

    Опять же, незначительные изменения здесь. В решении CTE добавьте TransactionID в предложение OVER () и измените внешний фильтр на rn = 1. Для CROSS APPLY мы изменим TOP на TOP (1) и добавим TransactionID во внутренний < code> ORDER BY.

    - CTE /OVER ()
    
    ; С историей AS
    (
      SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate,
        rn = ROW_NUMBER () OVER
        (PARTITION BY t.ProductID ORDER BY t.TransactionDate DESC, TransactionID DESC)
      FROM Production.Product AS p
      INNER JOIN Production.TransactionHistory AS t
      ON p.ProductID = t.ProductID
      WHERE p.Name> = N'M 'И p.Name <N'S»
    )
    SELECT ProductID, Name, TransactionID, TransactionDate
    ОТ истории
    ГДЕ rn = 1;
    
    - КРЕСТ ПРИМЕНЯЕТСЯ
    
    SELECT p.ProductID, p.Name, t.TransactionID, t.TransactionDate
    FROM Production.Product AS p
    КРЕСТ ПРИМЕНЯЕТСЯ
    (
      SELECT TOP (1) TransactionID, TransactionDate
      FROM Production.TransactionHistory
      WHERE ProductID = p.ProductID
      ORDER BY TransactionDate DESC, TransactionID DESC
    ) AS t
    WHERE p.Name> = N'M 'И p.Name <N'S ';
    

    Результаты выполнения:

    введите здесь описание изображения>> </p>

<p> Параллельный CTE /<code>OVER ()</code> план: </p>

<p> <img src = один Павел упомянул в комментарии , но со вторым и третьим столбцами заказано DESC:

    СОЗДАТЬ УНИКАЛЬНЫЙ НЕПРЕРЫВНЫЙ ИНДЕКС UQ3 ON Production.TransactionHistory
      (ProductID, TransactionDate DESC, TransactionID DESC);
    

    На самом деле вы получите гораздо более выгодные планы, и показатели будут отражаться на методе CROSS APPLY во всех трех случаях:

    введите описание изображения здесь

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


    В SQL Server 2000 это было намного уродливее, которое не поддерживало APPLY или предложение OVER ().

    ответил Aaron Bertrand 17 WedEurope/Moscow2014-12-17T17:55:41+03:00Europe/Moscow12bEurope/MoscowWed, 17 Dec 2014 17:55:41 +0300 2014, 17:55:41
    20

    В СУБД, таких как MySQL, которые не имеют оконных функций или CROSS APPLY, способ сделать это - использовать стандартный SQL (89). Медленным путем будет треугольное крест-соединение с совокупностью. Более быстрый способ (но все же и, вероятно, не такой эффективный, как использование cross apply или function row_number) будет тем, что я называю . Было бы интересно сравнить этот запрос с остальными:

    Предположение: Orders (CustomerID, OrderDate) имеет ограничение UNIQUE:

    DECLARE @top INT;
    SET @top = 5;
    
    SELECT o.CustomerID, o.OrderID, o.OrderDate
      FROM dbo.Customers AS c
        ПРИСОЕДИНЯЙТЕСЬ dbo.Orders AS o
          ON o.CustomerID = c.CustomerID
          И o.OrderID IN
              (SELECT TOP (@top) oi.OrderID
                FROM dbo.Orders AS oi
                WHERE oi.CustomerID = c.CustomerID
                ORDER BY oi.OrderDate DESC
              )
      ORDER BY CustomerID, OrderDate DESC;
    

    Для дополнительной задачи настраиваемых верхних строк для каждой группы:

    SELECT o.CustomerID, o.OrderID, o.OrderDate
      FROM dbo.Customers AS c
        ПРИСОЕДИНЯЙТЕСЬ dbo.Orders AS o
          ON o.CustomerID = c.CustomerID
          И o.OrderID IN
              (SELECT TOP (c.Number_of_Recent_Orders_to_Show) oi.OrderID
                FROM dbo.Orders AS oi
                WHERE oi.CustomerID = c.CustomerID
                ORDER BY oi.OrderDate DESC
              )
      ORDER BY CustomerID, OrderDate DESC;
    

    Примечание. В MySQL вместо AND o.OrderID IN (SELECT TOP (@top) oi.OrderID ...) можно использовать AND o.OrderDate> ; = (SELECT oi.OrderDate ... LIMIT 1 OFFSET (@top - 1)). SQL-Server добавил синтаксис FETCH /OFFSET в версии 2012 года. Запросы здесь были скорректированы с помощью IN (TOP ...) для работы с более ранними версиями.

    ответил ypercubeᵀᴹ 17 WedEurope/Moscow2014-12-17T18:47:27+03:00Europe/Moscow12bEurope/MoscowWed, 17 Dec 2014 18:47:27 +0300 2014, 18:47:27
    16

    Я использовал несколько иной подход, главным образом для того, чтобы увидеть, как этот метод будет сравниваться с другими, потому что, если параметры хороши, правильно?

    Тестирование

    Почему бы нам не начать, просто посмотрев, как различные методы сложены друг против друга. Я выполнил три набора тестов:

    1. Первый набор выполнялся без изменений БД
    2. Второй набор запускался после создания индекса для поддержки TransactionDate запросов на основе Production.TransactionHistory.
    3. Третий набор сделал несколько другое предположение. Поскольку все три теста выполнялись против одного и того же списка продуктов, что делать, если мы кэшировали этот список? Мой метод использует кеш в памяти, в то время как другие методы используют эквивалентную таблицу temp. Поддерживающий индекс, созданный для второго набора тестов, по-прежнему существует для этого набора тестов.

    Дополнительные данные теста:

    • Тесты выполнялись с помощью AdventureWorks2012 на SQL Server 2012, SP2 (Developer Edition).
    • Для каждого теста я помечен, на чей ответ я взял запрос и какой именно запрос был.
    • Я использовал параметр «Отменить результаты после выполнения» в параметрах запроса | Результаты.
    • Обратите внимание, что для первых двух наборов тестов RowCounts для моего метода кажется «выключенным». Это связано с тем, что мой метод представляет собой ручную реализацию того, что делает CROSS APPLY: он запускает начальный запрос с Production.Product и получает 161 строку назад, которую затем использует для запросы на Production.TransactionHistory. Следовательно, значения RowCount для моих записей всегда на 161 больше, чем другие записи. В третьем наборе тестов (с кешированием) подсчет строк одинаковый для всех методов.
    • Я использовал SQL Server Profiler для захвата статистики вместо того, чтобы полагаться на планы выполнения. Аарон и Микаэль уже проделали отличную работу, показывая планы своих запросов, и нет необходимости воспроизводить эту информацию. И цель моего метода - уменьшить запросы до такой простой формы, что это не имеет большого значения. Существует дополнительная причина использования Profiler, но это будет упомянуто ниже.
    • Вместо использования Name> = N'M 'AND Name <N'S ', я решил использовать Name LIKE N' [M-R]% ', а SQL Server относится к ним одинаково.

    Результаты

    Нет поддерживающего индекса

    Это, по сути, готовое приложение AdventureWorks2012. Во всех случаях мой метод явно лучше, чем некоторые другие, но не так хорош, как верхние 1 или 2 метода.

    Тест 1 Тест 1 Результаты - без индекса
    CTA, безусловно, является победителем здесь.

    Тест 2 Результаты теста 2 без индекса
    CTE (снова) Aaron (снова) и второй метод Mikael применить метод row_number () - это вторая секунда.

    Тест 3 Результаты теста 3 без индекса
    Победителем является КЭР Аарона (снова).

    Заключение
    Когда индекс TransactionDate не поддерживается, мой метод лучше, чем стандартный CROSS APPLY, но тем не менее, использование метода CTE, очевидно, способ. р>

    С индексом поддержки (без кэширования)

    Для этого набора тестов я добавил очевидный индекс в TransactionHistory.TransactionDate, поскольку все запросы сортируются в этом поле. Я говорю «очевидный», так как большинство других ответов также согласны с этим. И поскольку запросы все хотят самые последние даты, поле TransactionDate должно быть заказано DESC, поэтому я просто захватил инструкцию CREATE INDEX в нижней части ответа Микаэля и добавил явный FILLFACTOR:

    CREATE INDEX [IX_TransactionHistoryX]
        ON Production.TransactionHistory (ProductID ASC, TransactionDate DESC)
        С (FILLFACTOR = 100);
    

    Как только этот индекс окажется на месте, результаты немного изменились.

    Тест 1 Тест 1 Результаты - с индексом поддержки
    На этот раз это мой метод, который выходит вперед, по крайней мере, с точки зрения логических чтений. Метод CROSS APPLY, ранее худший исполнитель для теста 1, выигрывает по длительности и даже превосходит метод CTE для логических чтений.

    Тест 2 Результаты теста 2 с индексом поддержки
    На этот раз первый метод Mikael применяет метод row_number (), который является победителем при просмотре Reads, тогда как ранее он был одним из худших исполнителей. И теперь мой метод приходит на очень близкое второе место, когда смотрит на Чтение. Фактически, вне метода CTE все остальные довольно близки по показаниям чтения.

    Тест 3
    Здесь CTE по-прежнему остается победителем, но теперь разница между другими методами едва заметна по сравнению с резкой разницей, существовавшей до создания индекса.

    Заключение
    Применимость моего метода теперь более очевидна, хотя она менее устойчива к тому, чтобы не иметь надлежащих индексов.

    С индексом поддержки и кэшированием

    Для этого набора тестов я использовал кеширование, потому что, ну почему бы и нет? Мой метод позволяет использовать кэширование в памяти, к которому другие методы не могут получить доступ. Поэтому, чтобы быть справедливым, я создал следующую временную таблицу, которая использовалась вместо Product.Product для всех ссылок в этих других методах во всех трех тестах. Поле DaysToManufacture используется только в Test Number 2, но было проще быть последовательным в сценариях SQL, чтобы использовать ту же таблицу, и это не помешало ему там.

    CREATE TABLE #Products
    (
        ProductID INT NOT NULL PRIMARY KEY,
        Имя NVARCHAR (50) NOT NULL,
        DaysToManufacture INT NOT NULL
    );
    
    INSERT INTO #Products (ProductID, Name, DaysToManufacture)
        SELECT p.ProductID, p.Name, p.DaysToManufacture
        FROM Production.Product p
        WHERE p.Name> = N'M 'И p.Name <N'S»
        И СУЩЕСТВУЮЩИЕ (
                        ВЫБРАТЬ  *
                        FROM Production.TransactionHistory th
                        WHERE th.ProductID = p.ProductID
                    );
    
    ALTER TABLE #Products REBUILD WITH (FILLFACTOR = 100);
    

    Тест 1 Тест 1 Результаты - с индексом поддержки и кэшированием
    Все методы, похоже, в равной степени выигрывают от кеширования, и мой метод все еще выходит вперед.

    Тест 2 Результаты теста 2 с индексом поддержки и кэшированием
    Здесь мы видим разницу в линейке, так как мой метод выходит едва впереди, только 2 Reads лучше, чем первый метод метода Mikael's применить метод row_number (), тогда как без кэширования мой метод отставал на 4 чтения.

    Тест 3 Результат теста 3 - с индексом поддержки и кешированием
    См. обновление в нижней части (ниже линия) . Здесь мы снова видим некоторую разницу. «Параметрированный» вкус моего метода теперь едва ли уступает 2-м чинам по сравнению с методом AROON CROSS APPLY (без кеширования они были равны). Но действительно странно, что в первый раз мы видим метод, на который негативно влияет кеширование: метод CTE Aaron (который ранее был лучшим для Test Number 3). Но я не собираюсь брать кредит там, где это не из-за, и поскольку без кэширования метод CTE Aaron все еще быстрее, чем мой метод здесь с кешированием, лучшим подходом для этой конкретной ситуации является метод CTE от Aaron.

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

    Метод

    Вообще

    Я отделил запрос «header» (т.е. получив ProductID s), а в одном случае также DaysToManufacture, на основе Name начиная с определенных букв) из «подробных» запросов (т. е. получения TransactionID s и TransactionDate s)). Концепция заключалась в том, чтобы выполнять очень простые запросы и не позволять оптимизатору запутываться при подключении к ним. Понятно, что это не всегда , а также оптимизирует оптимизатор. Но, как мы видели в результатах, в зависимости от типа запроса этот метод имеет свои достоинства.

    Разница между различными вариантами этого метода:

    • Константы: Присылайте любые заменяемые значения как встроенные константы вместо параметров. Это будет ссылаться на ProductID во всех трех тестах, а также на количество строк, возвращаемых в Test 2, поскольку это функция «в пять раз превышает атрибут Product DayToManufacture». Этот под-метод означает, что каждый ProductID получит свой собственный план выполнения, что может быть полезно, если существует широкий разброс в распределении данных для ProductID. Но если в распределении данных мало изменений, стоимость генерации дополнительных планов, вероятно, не будет стоить того.

    • Параметрированный: Присылайте как минимум ProductID в качестве @ProductID, что позволяет кэшировать и повторно использовать план выполнения. Существует также дополнительная тестовая опция, которая также обрабатывает переменное число строк для возврата для теста 2 какПараметр.

    • Оптимизировать Неизвестно: При ссылке ProductID на @ProductID, если существует широкий выбор распределения данных, тогда можно кеш-план, который отрицательно влияет на другие значения ProductID, поэтому было бы полезно знать, помогает ли этот подсказку в запросе.

    • Продукты кэша: Вместо того, чтобы каждый раз запрашивать таблицу Production.Product, только чтобы получить тот же список, выполните запрос один раз (и пока мы на нем, отфильтруйте любой ProductID s, который даже не находится в таблице TransactionHistory, поэтому мы не тратим туда никаких ресурсов) и кешем этого списка. Список должен включать поле DaysToManufacture. С помощью этой опции есть несколько более высокий начальный удар по логическим считываниям для первого выполнения, но после этого запрашивается только таблица TransactionHistory.

    Конкретно

    Хорошо, но так, гм, как можно выдать все подзапросы в виде отдельных запросов без использования CURSOR и сбросить каждый результирующий набор во временную таблицу или переменную таблицы? Очевидно, что использование метода CURSOR /Temp Table вполне очевидно отразится на Reads and Writes. Ну, используя SQLCLR :). Создав хранимую процедуру SQLCLR, я смог открыть набор результатов и по существу передать результаты каждого подзапроса на него, как непрерывный набор результатов (а не несколько наборов результатов). Вне информации о продукте (т.е. ProductID, Name и DaysToManufacture)), ни один из результатов подзапроса не должен храниться нигде (память или диск), и только что прошел через основной результирующий набор хранимой процедуры SQLCLR. Это позволило мне сделать простой запрос, чтобы получить информацию о продукте, а затем прокрутить ее, выдавая очень простые запросы в отношении TransactionHistory.

    И вот почему мне пришлось использовать SQL Server Profiler для сбора статистики. Хранимая процедура SQLCLR не вернула план выполнения, либо установив опцию «Включить фактический вариант выполнения», либо выпустив SET STATISTICS XML ON;.

    Для кэширования информации о продукте я использовал readonly static общий список (т. е. _GlobalProducts в коде ниже). Похоже, что добавление в коллекции не нарушает параметр readonly, поэтому этот код работает, когда сборка имеет PERMISSON_SET of SAFE :), даже если это противоречит интуиции.

    Сгенерированные запросы

    Запросы, созданные этой хранимой процедурой SQLCLR, следующие:

    Информация о продукте

    Номера тестов 1 и 3 (без кэширования)

     SELECT prod1.ProductID, prod1.Name, 1 AS [DaysToManufacture]
    FROM Production.Product prod1
    WHERE prod1.Name LIKE N '[M-R]%';
    

    Номер теста 2 (без кэширования)

     ; WITH cte AS
    (
        SELECT prod1.ProductID
        FROM Production.Product prod1 WITH (INDEX (AK_Product_Name))
        WHERE prod1.Name LIKE N '[M-R]%'
    )
    SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
    FROM Production.Product prod2
    INNER JOIN cte
            ON cte.ProductID = prod2.ProductID;
    

    Номера тестов 1, 2 и 3 (Кэширование)

     ; WITH cte AS
    (
        SELECT prod1.ProductID
        FROM Production.Product prod1 WITH (INDEX (AK_Product_Name))
        WHERE prod1.Name LIKE N '[M-R]%'
        И СУЩЕСТВУЮЩИЕ (
                    ВЫБРАТЬ *
                    FROM Production.TransactionHistory th
                    WHERE th.ProductID = prod1.ProductID
                      )
    )
    SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
    FROM Production.Product prod2
    INNER JOIN cte
            ON cte.ProductID = prod2.ProductID;
    

    Информация о транзакции

    Номера тестов 1 и 2 (константы)

     SELECT TOP (5) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = 977
    ORDER BY th.TransactionDate DESC;
    

    Номера тестов 1 и 2 (с параметрами)

     SELECT TOP (5) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = @ProductID
    ORDER BY th.TransactionDate DESC
    ;
    

    Номера тестов 1 и 2 (параметризованные + OPTIMIZE UNKNOWN)

     SELECT TOP (5) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = @ProductID
    ORDER BY th.TransactionDate DESC
    ВАРИАНТ (ОПТИМИЗАЦИЯ ДЛЯ (@ProductID UNKNOWN));
    

    Номер теста 2 (с параметрами обоих)

     SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = @ProductID
    ORDER BY th.TransactionDate DESC
    ;
    

    Тестовый номер 2 (Параметрированный обе + OPTIMIZE UNKNOWN)

     SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = @ProductID
    ORDER BY th.TransactionDate DESC
    ВАРИАНТ (ОПТИМИЗАЦИЯ ДЛЯ (@ProductID UNKNOWN));
    

    Тестовый номер 3 (константы)

     SELECT TOP (1) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = 977
    ORDER BY th.TransactionDate DESC, th.TransactionID DESC;
    

    Номер теста 3 (параметризованный)

     SELECT TOP (1) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = @ProductID
    ORDER BY th.TransactionDate DESC, th.TransactionID DESC
    ;
    

    Номер теста 3 (параметризованный + OPTIMIZE UNKNOWN)

     SELECT TOP (1) th.TransactionID, th.TransactionDate
    FROM Production.TransactionHistory th
    WHERE th.ProductID = @ProductID
    ORDER BY th.TransactionDate DESC, th.TransactionID DESC
    ВАРИАНТ (ОПТИМИЗАЦИЯ ДЛЯ (@ProductID UNKNOWN));
    

    Код

    с использованием System;
    используя System.Collections.Generic;
    используя System.Data;
    используя System.Data.SqlClient;
    используя System.Data.SqlTypes;
    с использованием Microsoft.SqlServer.Server;
    
    public class ObligatoryClassName
    {
        частный класс ProductInfo
        {
            public int ProductID;
            public string Name;
            public int DaysToManufacture;
    
            public ProductInfo (int ProductID, string Name, int DaysToManufacture)
            {
                this.ProductID = ProductID;
                this.Name = Name;
                this.DaysToManufacture = DaysToManufacture;
    
                вернуть;
            }
        }
    
        private static readonly List <ProductInfo> _GlobalProducts = новый список <ProductInfo> ();
    
        private static void PopulateGlobalProducts (SqlBoolean PrintQuery)
        {
            if (_GlobalProducts.Count> 0)
            {
                if (PrintQuery.IsTrue)
                {
                    SqlContext.Pipe.Send (String.Concat («Я уже знаю», _GlobalProducts.Count,
                                «записи :)»));
                }
    
                вернуть;
            }
    
            SqlConnection _Connection = новое SqlConnection («Контекстное соединение = true;»);
            SqlCommand _Command = новый SqlCommand ();
            _Command.CommandType = CommandType.Text;
            _Command.Connection = _Connection;
            _Command.CommandText = @ "
       ; C cte AS
       (
         SELECT prod1.ProductID
         FROM Production.Product prod1 WITH (INDEX (AK_Product_Name))
         WHERE prod1.Name LIKE N '[M-R]%'
         И СУЩЕСТВУЮЩИЕ (
                         ВЫБРАТЬ *
                         FROM Production.TransactionHistory th
                         WHERE th.ProductID = prod1.ProductID
                       )
       )
       SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
       FROM Production.Product prod2
       INNER JOIN cte
               ON cte.ProductID = prod2.ProductID;
    «;
    
            SqlDataReader _Reader = null;
    
            пытаться
            {
                _Connection.Open ();
    
                _Reader = _Command.ExecuteReader ();
    
                while (_Reader.Read ())
                {
                    _GlobalProducts.Add (новый ProductInfo (_Reader.GetInt32 (0), _Reader.GetString (1),
                                                        _Reader.GetInt32 (2)));
                }
            }
            поймать
            {
                бросить;
            }
            в конце концов
            {
                if (_Reader! = null &! _Reader.IsClosed)
                {
                    _Reader.Close ();
                }
    
                if (_Connection! = null & & _Connection.State! = ConnectionState.Closed)
                {
                    _Connection.Close ();
                }
    
                if (PrintQuery.IsTrue)
                {
                    SqlContext.Pipe.Send (_Command.CommandText);
                }
            }
    
            вернуть;
        }
    
    
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void GetTopRowsPerGroup (SqlByte TestNumber,
            SqlByte ParameterizeProductID, SqlBoolean OptimizeForUnknown,
            SqlBoolean UseSequentialAccess, SqlBoolean CacheProducts, SqlBoolean PrintQueries)
        {
            SqlConnection _Connection = новое SqlConnection («Контекстное соединение = true;»);
            SqlCommand _Command = новый SqlCommand ();
            _Command.CommandType = CommandType.Text;
            _Command.Connection = _Connection;
    
            Список & л; ProductInfo > _Products = null;
            SqlDataReader _Reader = null;
    
            int _RowsToGet = 5; //значение по умолчанию для Test Number 1
            строка _OrderByTransactionID = "";
            строка_OptimizeForUnknown = "";
            CommandBehavior _CmdBehavior = CommandBehavior.Default;
    
            if (OptimizeForUnknown.IsTrue)
            {
                _OptimizeForUnknown = "ОПЦИЯ (ОПТИМИЗАЦИЯ ДЛЯ (@ProductID UNKNOWN))";
            }
    
            if (UseSequentialAccess.IsTrue)
            {
                _CmdBehavior = CommandBehavior.SequentialAccess;
            }
    
            if (CacheProducts.IsTrue)
            {
                PopulateGlobalProducts (PrintQueries);
            }
            еще
            {
                _Products = новый List <ProductInfo> ();
            }
    
    
            if (TestNumber.Value == 2)
            {
                _Command.CommandText = @ "
       ; C cte AS
       (
         SELECT prod1.ProductID
         FROM Production.Product prod1 WITH (INDEX (AK_Product_Name))
         WHERE prod1.Name LIKE N '[M-R]%'
       )
       SELECT prod2.ProductID, prod2.Name, prod2.DaysToManufacture
       FROM Production.Product prod2
       INNER JOIN cte
               ON cte.ProductID = prod2.ProductID;
    «;
            }
            еще
            {
                _Command.CommandText = @ "
         SELECT prod1.ProductID, prod1.Name, 1 AS [DaysToManufacture]
         FROM Production.Product prod1
         WHERE prod1.Name LIKE N '[M-R]%';
    «;
                if (TestNumber.Value == 3)
                {
                    _RowsToGet = 1;
                    _OrderByTransactionID = ", th.TransactionID DESC";
                }
            }
    
            пытаться
            {
                _Connection.Open ();
    
                //Заполните список продуктов для этого запуска, если не используете кэш продукта
                if (! CacheProducts.IsTrue)
                {
                    _Reader = _Command.ExecuteReader (_CmdBehavior);
    
                    while (_Reader.Read ())
                    {
                        _Products.Add (новый ProductInfo (_Reader.GetInt32 (0), _Reader.GetString (1),
                                                      _Reader.GetInt32 (2)));
                    }
    
                    _Reader.Close ();
    
                    if (PrintQueries.IsTrue)
                    {
                        SqlContext.Pipe.Send (_Command.CommandText);
                    }
                }
                еще
                {
                    _Products = _GlobalProducts;
                }
    
                SqlDataRecord _ResultRow = новый SqlDataRecord (
                    новый SqlMetaData [] {
                        новый SqlMetaData («ProductID», SqlDbType.Int),
                        новые SqlMetaData («Имя», SqlDbType.NVarChar, 50),
                        новый SqlMetaData («TransactionID», SqlDbType.Int),
                        новый SqlMetaData («TransactionDate», SqlDbType.DateTime)
                    });
    
                SqlParameter _ProductID = новый SqlParameter ("@ ProductID", SqlDbType.Int);
                _Command.Parameters.Add (_ProductID);
                SqlParameter _RowsToReturn = новый SqlParameter ("@ RowsToReturn", SqlDbType.Int);
                _Command.Parameters.Add (_RowsToReturn);
    
                SqlContext.Pipe.SendResultsStart (_ResultRow);
    
                for (int _Row = 0; _Row <_Products.Count; _Row ++)
                {
                    //Тесты 1 и 3 используют ранее установленные статические значения для _RowsToGet
                    if (TestNumber.Value == 2)
                    {
                        if (_Products [_Row] .DaysToManufacture == 0)
                        {
                            Продолжать; //не используется при выдаче запроса SELECT TOP (0)
                        }
    
                        _RowsToGet = (5 * _Products [_Row] .DaysToManufacture);
                    }
    
                    _ResultRow.SetInt32 (0, _Products [_Row] .ProductID);
                    _ResultRow.SetString (1, _Products [_Row] .Name);
    
                    переключатель (ParameterizeProductID.Value)
                    {
                        случай 0x01:
                            _Command.CommandText = String.Format (@ "
       SELECT TOP ({0}) th.TransactionID, th.TransactionDate
       FROM Production.TransactionHistory th
       WHERE th.ProductID = @ProductID
       ORDER BY th.TransactionDate DESC {2}
       {1};
    ", _RowsToGet, _OptimizeForUnknown, _OrderByTransactionID);
    
                            _ProductID.Value = _Products [_Row] .ProductID;
                            ломать;
                        случай 0x02:
                            _Command.CommandText = String.Format (@ "
       SELECT TOP (@RowsToReturn) th.TransactionID, th.TransactionDate
       FROM Production.TransactionHistory th
       WHERE th.ProductID = @ProductID
       ORDER BY th.TransactionDate DESC
       {0};
    ", _OptimizeForUnknown);
    
                            _ProductID.Value = _Products [_Row] .ProductID;
                            _RowsToReturn.Value = _RowsToGet;
                            ломать;
                        по умолчанию:
                            _Command.CommandText = String.Format (@ "
       SELECT TOP ({0}) th.TransactionID, th.TransactionDate
       FROM Production.TransactionHistory th
       WHERE th.ProductID = {1}
       ORDER BY th.TransactionDate DESC {2};
    ", _RowsToGet, _Products [_Row] .ProductID, _OrderByTransactionID);
                            ломать;
                    }
    
    
                    _Reader = _Command.ExecuteReader (_CmdBehavior);
    
                    while (_Reader.Read ())
                    {_ResultRow.SetInt32 (2, _Reader.GetInt32 (0));
                        _ResultRow.SetDateTime (3, _Reader.GetDateTime (1));
    
                        SqlContext.Pipe.SendResultsRow (_ResultRow);
                    }
                    _Reader.Close ();
                }
    
            }
            поймать
            {
                бросить;
            }
            в конце концов
            {
                if (SqlContext.Pipe.IsSendingResults)
                {
                    SqlContext.Pipe.SendResultsEnd ();
                }
    
                if (_Reader! = null &! _Reader.IsClosed)
                {
                    _Reader.Close ();
                }
    
                if (_Connection! = null & & _Connection.State! = ConnectionState.Closed)
                {
                    _Connection.Close ();
                }
    
                if (PrintQueries.IsTrue)
                {
                    SqlContext.Pipe.Send (_Command.CommandText);
                }
            }
    
    
        }
    }
    

    Тестовые запросы

    Здесь не хватает места для размещения тестов, поэтому я найду другое место.

    Заключение

    В некоторых сценариях SQLCLR может использоваться для управления некоторыми аспектами запросов, которые не могут быть выполнены в T-SQL. И есть возможность использовать память для кеширования вместо временных таблиц, хотя это нужно делать осторожно и осторожно, так как память автоматически не возвращается в систему. Этот метод также не является чем-то, что поможет ad hoc-запросам, хотя можно сделать его более гибким, чем показано здесь, просто добавив параметры для настройки дополнительных аспектов выполняемых запросов.


    UPDATE

    Дополнительный тест
    Мои первоначальные тесты, которые включали индекс поддержки в TransactionHistory, использовали следующее определение:

    ProductID ASC, TransactionDate DESC
    

    Я решил в то время отказаться от кода TransactionId DESC в конце, считая, что хотя это может помочь Test Number 3 (который указывает на разрыв tie для самого последнего TransactionId - ну, «последнее» считается, так как не указано явно, но все, похоже, согласны с этим допущением), скорее всего, не будет достаточно связей, чтобы иметь значение.

    Но тогда Аарон повторно протестировал с индексом поддержки, который включил TransactionId DESC, и обнаружил, что метод CROSS APPLY стал победителем всех трех тестов. Это отличалось от моего тестирования, в котором указывалось, что метод CTE лучше всего подходит для Test Number 3 (когда кеширование не использовалось, что отражает тест Аарона). Было ясно, что существует дополнительная вариация, которая должна быть проверена.

    Я удалил текущий поддерживающий индекс, создал новый с помощью TransactionId и очистил кэш плана (просто чтобы убедиться):

    DROP INDEX [IX_TransactionHistoryX] ON Production.TransactionHistory;
    
    CREATE UNIQUE INDEX [UIX_TransactionHistoryX]
        ON Production.TransactionHistory (ProductID ASC, TransactionDate DESC, TransactionID DESC)
        С (FILLFACTOR = 100);
    
    DBCC FREEPROCCACHE с NO_INFOMSGS;
    

    Я повторил тестовый номер 1, и результаты были такими же, как и ожидалось. Затем я повторно запустил тестовый номер 3, и результаты действительно изменились:

    Результаты теста 3 - с индексом поддержки (с TransactionId DESC)
    Вышеуказанные результаты приведены для стандартного теста без кэширования. На этот раз не только CROSS APPLY избили CTE (как указано в тесте Aaron), но SQLCLR proc взял на себя инициативу на 30 Reads (woo hoo).

    Результаты теста 3 - с индексом поддержки (с TransactionId DESC) и кэшированием
    Вышеприведенные результаты для теста с включенным кешированием. На этот раз производительность CTE не ухудшилась, хотя CROSS APPLY все еще бьет его. Однако теперь SQLCLR proc возглавляет 23 чтения (woo hoo, again).

    Take Aways

    1. Существуют различные варианты использования. Лучше попробовать несколько, так как каждый из них имеет свои сильные стороны. Проведенные здесь тесты показывают довольно небольшое отклонение как от чтения, так и от продолжительности между лучшими и худшими исполнителями во всех тестах (с индексом поддержки); изменение в Reads составляет около 350, а длительность - 55 мс. В то время как SQLCLR-процесс действительно выигрывал во всех тестах, кроме одного (с точки зрения чтения), сохранение только нескольких Reads обычно не стоит затрат на обслуживание перехода по маршруту SQLCLR. Но в AdventureWorks2012 таблица Product имеет только 504 строки, а TransactionHistory имеет только 113 443 строки. Разница в производительности этих методов, вероятно, становится более выраженной по мере увеличения количества строк.

    2. Хотя этот вопрос был специфическим для получения определенного набора строк, не следует упускать из виду, что самым важным фактором в производительности является индексирование, а не конкретный SQL. Хороший индекс должен быть установлен до определения того, какой метод действительнолучше всего.

    3. Самый важный урок, который здесь вы найдете, - это не CROSS APPLY vs CTE и SQLCLR: речь идет о TESTING. Не предполагайте. Получите идеи от нескольких людей и испытайте как можно больше сценариев.

    ответил Solomon Rutzky 25 ThuEurope/Moscow2014-12-25T12:39:48+03:00Europe/Moscow12bEurope/MoscowThu, 25 Dec 2014 12:39:48 +0300 2014, 12:39:48
    14

    APPLY TOP или ROW_NUMBER ()? Что может быть, возможно, больше сказать по этому поводу?

    Краткий обзор различий и действительно короткое замыкание Я покажу только планы для варианта 2, и я добавил индекс в Production.TransactionHistory.

    создать индекс IX_TransactionHistoryX on
      Production.TransactionHistory (ProductID, TransactionDate)
    

    Запрос row_number ():.

    с C как
    (
      выберите T.TransactionID,
             T.TransactionDate,
             P.DaysToManufacture,
             row_number () over (раздел по порядку P.ProductID от T.TransactionDate desc) как rn
      от Production.Product как P
        внутреннее соединение Production.TransactionИстория как T
          на P.ProductID = T.ProductID
      где P.Name> = N'M 'и
            P.Name <N'S»
    )
    выберите C.TransactionID,
           C.TransactionDate
    от C
    где C.rn <= 5 * C.DaysToManufacture;
    

    введите описание изображения здесь

    применить верхнюю версию:

    выберите T.TransactionID,
           T.TransactionDate
    от Production.Product как P
      крест применяется (
                  выберите top (cast (5 * P.DaysToManufacture as bigint))
                    T.TransactionID,
                    T.TransactionDate
                  из Production.TransactionHistory как T
                  где P.ProductID = T.ProductID
                  заказать по T.TransactionDate desc
                  ) в виде T
    где P.Name> = N'M 'и
          P.Name <N'S ';
    

    введите описание изображения здесь>> </p>

<p> Основное отличие состоит в том, что <code>apply top</code> фильтры в верхнем выражении ниже вложенных циклов объединяются, где фильтры версии <code>row_number</code> после объединения. Это означает, что есть больше чтений из <code>Production.TransactionHistory</code>, чем это действительно необходимо. </p>

<p> Если существовал способ переназначить операторы, ответственные за перечисление строк до нижней ветви перед соединением, более эффективная версия <code>row_number</code>. </p>

<p> Итак, введите <code>примените версию row_number ()</code>. </p>

<pre><code>выберите T.TransactionID,
       T.TransactionDate
от Production.Product как P
  крест применяется (
              выберите T.TransactionID,
                     T.TransactionDate
              из (
                   выберите T.TransactionID,
                          T.TransactionDate,
                          row_number () over (порядок от T.TransactionDate desc) как rn
                   из Production.TransactionHistory как T
                   где P.ProductID = T.ProductID
                   ) в виде T
              где T.rn <= cast (5 * P.DaysToManufacture as bigint)
              ) в виде T
где P.Name> = N'M 'и
      P.Name <N'S ';
</code></pre>

<p> <img src = APPLY - ROW_NUMBER (Затронуто 961 ряд строк) Таблица «История транзакций». Число сканирования 115, логическое считывание 230, физическое чтение 0, чтение вперед 0, логическое считывание логических чисел 0, физическое чтение lob 0, чтение с чтением lob 0. Таблица «Продукт». Число сканирования 1, логическое чтение 15, физическое чтение 0, чтение вперед 0, логическое чтение lob 0, физическое чтение lob 0, чтение с чтением lob 0. ПРИМЕНИТЬ - ТОП (Затронуто 961 ряд строк) Таблица «История транзакций». Число сканирования 115, логическое считывание 268, физическое чтение 0, чтение вперед 0, логическое чтение логических чисел 0, физическое чтение lob 0, чтение с чтением lob 0. Таблица «Продукт». Число сканирования 1, логическое чтение 15, физическое чтение 0, чтение вперед 0, логическое чтение lob 0, физическое чтение lob 0, чтение с чтением lob 0.

    Пока я нахожусь, я мог бы также добавить вторую версию row_number (), которая в некоторых случаях может быть способом. Эти определенные случаи были бы тогда, когда вы ожидаете, что вам действительно понадобится большая часть строк из Production.TransactionHistory, потому что здесь вы получаете объединение слияния между Production.Product и перечисляемым Production.TransactionHistory.

    с C как
    (
      выберите T.TransactionID,
             T.TransactionDate,
             T.ProductID,
             row_number () over (раздел по порядку T.ProductID по T.TransactionDate desc) как rn
      из Production.TransactionHistory как T
    )
    выберите C.TransactionID,
           C.TransactionDate
    от C
     внутреннее соединение. Производство. Продукт как P
          на P.ProductID = C.ProductID
    где P.Name> = N'M 'и
          P.Name <N'S»а также
          C.rn <= 5 * P.DaysToManufacture;
    

    введите описание изображения здесь>> </p>

<p> Чтобы получить вышеуказанную форму без оператора сортировки, вам также нужно изменить индекс поддержки на порядок по убыванию <code>TransactionDate</code>. </p>

<pre><code>создать индекс IX_TransactionHistoryX on
  Production.TransactionHistory (ProductID, TransactionDate desc)
</code></pre>

<hr>
<p> <sup> * </sup> Изменить: дополнительные логические чтения связаны с <a href = вложенные петли prefetching , используемые с приложением-top. Вы можете отключить это с помощью undoc'd TF 8744 (и /или 9115 на более поздних версиях), чтобы получить одинаковое количество логических чтений. Предварительная выборка может быть преимуществом альтернативы «сверху вниз» при правильных обстоятельствах. - Пол Уайт

    ответил Mikael Eriksson 22 MonEurope/Moscow2014-12-22T18:36:14+03:00Europe/Moscow12bEurope/MoscowMon, 22 Dec 2014 18:36:14 +0300 2014, 18:36:14
    8

    Обычно я использую комбинацию CTE и функций окна. Вы можете получить этот ответ, используя что-то вроде следующего:

    ; WITH GiveMeCounts
    В ВИДЕ (
        SELECT CustomerID
            ,Дата заказа
            , TotalAmt
    
            , ROW_NUMBER () OVER (
                РАЗДЕЛЕНИЕ ПО ИСПОЛЬЗОВАНИЮ CustomerID ORDER BY
                - Вы можете изменить следующее поле или порядок сортировки на все, что вы хотите заказать.
                TotalAmt desc
                ) AS MySeqNum
        )
    SELECT CustomerID, OrderDate, TotalAmt
    FROM GiveMeCounts
    - Установить для каждой группы здесь
    где MySeqNum <= 10
    

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

    + ------- + ----------- +
    | Государство | MaxSeqnum |
    + ------- + ----------- +
    | AK | 10 |
    | NY | 5 |
    | NC | 23 |
    + ------- + ----------- +
    

    Чтобы достичь этого, когда значения могут быть разными, вам нужно будет присоединиться к CTE к таблице состояний, подобной этой:

    SELECT [CustomerID]
        ,[Дата заказа]
        , [TotalAmt]
        ,[Состояние]
    FROM GiveMeCounts gmc
    INNER JOIN StateTable st ON gmc. [State] = st. [State]
        И gmc.MySeqNum <= st.MaxSeqNum
    
    ответил Kris Gruttemeyer 17 WedEurope/Moscow2014-12-17T18:16:43+03:00Europe/Moscow12bEurope/MoscowWed, 17 Dec 2014 18:16:43 +0300 2014, 18:16:43

    Похожие вопросы

    Популярные теги

    security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132