SQL Server. Используйте TOP вместо SET ROWCOUNT

SQL Server. Используйте TOP вместо SET ROWCOUNT

Перевод статьи — Use TOP instead of SET ROWCOUNT

Оба TOP and SET ROWCOUNT метода ограничения вывода строк имеют право на существование, но они сильно отличаются. TOP ограничивает одну инструкцию T-SQL, в то время как SET ROWCOUNT ограничивает все инструкции в текущем подключении.

Если у вас несколько запросов, в каждом из которых вы хотите вернуть только по 10 строк, то кажется, что лучше использовать SET ROWCOUNT:

SET ROWCOUNT 10
 
SELECT * FROM HumanResources.Department
SELECT * FROM HumanResources.Employee
SELECT * FROM HumanResources.EmployeeDepartmentHistory
SELECT * FROM HumanResources.EmployeePayHistory
SELECT * FROM HumanResources.JobCandidate
SELECT * FROM HumanResources.Shift
 
SET ROWCOUNT 0

против

SELECT TOP 10 * FROM HumanResources.Department
SELECT TOP 10 * FROM HumanResources.Employee
SELECT TOP 10 * FROM HumanResources.EmployeeDepartmentHistory
SELECT TOP 10 * FROM HumanResources.EmployeePayHistory
SELECT TOP 10 * FROM HumanResources.JobCandidate
SELECT TOP 10 * FROM HumanResources.Shift

Так почему же я рекомендую использовать TOP вместо SET ROWCOUNT? В первую очередь по той причине, что TOP более предсказуемый. SET ROWCOUNT ограничивает ВСЕ запросы, включая триггеры.

Вот пример с триггером на тестовой БД AdventureWorks2008. Я создаю триггер, который удаляет данные из связанных таблиц.

-- Create temporary tables to test with to avoid the existing foreign keys
-- Create a temp SalesOrderHeader table
SELECT TOP 20 * INTO TempSalesOrderHeader FROM Sales.SalesOrderHeader

-- Create a temp SalesOrderDetail table
SELECT * INTO TempSalesOrderDetail 
FROM Sales.SalesOrderDetail
WHERE SalesOrderId IN (SELECT SalesOrderId FROM TempSalesOrderHeader)
GO

-- Create Trigger
CREATE TRIGGER dbo.tr_dl_SalesOrderDetail
ON TempSalesOrderHeader
AFTER DELETE AS
BEGIN
    DELETE FROM TempSalesOrderDetail
    FROM TempSalesOrderDetail
    JOIN deleted
        ON TempSalesOrderDetail.SalesOrderId = 
            deleted.SalesOrderId
END
GO
-- Run my delete using SET ROWCOUNT
SET ROWCOUNT 10
 
DELETE FROM TempSalesOrderHeader
 
SET ROWCOUNT 0
GO
-- Show orphaned OrderDetail rows
SELECT *
FROM TempSalesOrderDetail
WHERE SalesOrderId NOT IN (SELECT SalesOrderId FROM TempSalesOrderHeader)
GO
-- Cleanup code
DROP TABLE TempSalesOrderHeader
DROP TABLE TempSalesOrderDetail

Благодаря SET ROWCOUNT из таблицы TempSalesOrderHeader было удалено только 10 строк, как и ожидалось. Но из таблицы TempSalesOrderDetail было так же удалено 10 строк, что противоречит нашей логике. Если бы я использовал TOP, были бы удалены все 109 строк из TempSalesOrderDetail. Будьте осторожны!

Сейчас, в BOL есть предупреждение, что SET ROWCOUNT не будет поддерживаться для команд DELETE, INSERT и UPDATE. Если вы используете SET ROWCOUNT для удаления и понимаете как оно работает, рекомендуется поменять его на TOP, чтобы избежать проблем в будущем.

Оптимизация

Ещё хотелось бы поделиться вырезкой из BOL:

Для ограничения числа возвращаемых строк пользуйтесь TOP (или OFFSET и FETCH), а не SET ROWCOUNT. Эти методы предпочтительнее, чем SET ROWCOUNT, по следующим причинам:

  • Как часть инструкции SELECT, оптимизатор запросов может принимать значение выражение в предложениях TOP или FETCH во время оптимизации запроса. Поскольку SET ROWCOUNT используется вне инструкции, выполняющей запрос, его значение не может быть учтено при создании плана запроса.

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

Личный опыт

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

Запись опубликована в рубрике Оптимизация с метками . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: