Недостаточно написать код, который легко читать: он также должен работать быстро.
Существует три основных правила написания кода T-SQL, которые будут хорошо работать.
Они являются накопительными — соблюдение всех этих правил положительно скажется на коде.
Пропуск или изменение любого из них, скорее всего, окажет негативное влияние на производительность вашего кода.
- Пишите на основе структуры хранения данных: если вы храните данные datetime, используйте datetime, а не varchar или что-то еще.
- Пишите на основе наличия индексов: если для таблицы существуют индексы, и они должны быть там, напишите код так, чтобы он мог в полной мере использовать преимущества этих индексов.
Убедитесь, что кластеризованный индекс, который может быть только один на таблицу, используется наиболее эффективно.
- Пишите так, чтобы это помогало оптимизатору запросов: Оптимизатор запросов — замечательная часть СУБД.
К сожалению, вы можете очень усложнить его работу, написав запрос, который ему будет «трудно» разобрать, например, содержащий вложенные представления — когда одно представление получает данные от другого, а то и от третьего — и так далее.
Не торопитесь, чтобы понять, как работает оптимизатор, и пишите запросы так, чтобы он мог вам помочь, а не навредить.
Использование неправильных типов данных
Теоретически избежать этой ошибки очень просто, но на практике она встречается довольно часто.Например, вы используете какой-то тип данных в своей базе данных.
Используйте его в своих параметрах и переменных! Да, я знаю, что SQL Server может неявно приводить один тип данных к другому.
Но когда происходит неявное преобразование типов или вы сами приводите тип данных столбца к другому типу, вы выполняете преобразование всего столбца.
Когда вы выполняете это преобразование для столбца в предложении WHERE или в условии соединения, вы всегда будете видеть сканирование таблицы.
По этому столбцу можно было бы построить отличный индекс, но поскольку вы выполняете CAST для значений, хранящихся в этом столбце, чтобы сравнить, например, дату, хранящуюся в этом столбце, с типом char, который вы использовали в условии, индекс не будет использоваться.
Не верите мне? Давайте посмотрим на этот запрос:
Хорошо написано и очень просто.SELECT e.BusinessEntityID, e.NationalIDNumber FROM HumanResources.Employee AS e WHERE e.NationalIDNumber = 112457891;
Он должен быть покрыт индексом, созданным по этой таблице.
Но вот план выполнения: 
Этот запрос достаточно быстрый, а таблица небольшая, поэтому для сканирования индекса требуется всего четыре чтения.
Обратите внимание на маленький восклицательный знак в операторе SELECT. Если мы посмотрим на его свойства, то увидим: 
Верно.
Это предупреждение (новое в SQL Server 2012) о том, что выполняется преобразование типа, влияющее на план выполнения.
Короче говоря, это потому, что в запросе используется неправильный тип данных: SELECT e.BusinessEntityID,
e.NationalIDNumber
FROM HumanResources.Employee AS e
WHERE e.NationalIDNumber = '112457891';
И мы получаем такой план выполнения запроса: 
И здесь используются всего две операции чтения, вместо четырёх.
И да, я понимаю, что сделал и без того быстро выполняющийся запрос немного быстрее.
Но что произойдет, если в таблице будут храниться миллионы строк? Да, тогда бы я стал героем.
Используйте правильные типы данных.
Использование функций в условиях соединения и предложениях WHERE
Говоря о функциях, большинство функций, используемых в условиях соединения или предложениях WHERE, которым вы передаете столбец в качестве аргумента, мешают правильному использованию индексов.Вы увидите, насколько медленнее выполняются запросы при использовании функций, которые принимают столбцы в качестве аргументов.
Например: SELECT a.AddressLine1,
a.AddressLine2,
a.City,
a.StateProvinceID
FROM Person.Address AS a
WHERE '4444' = LEFT(a.AddressLine1, 4) ;
Эта функция LEFT принимает столбец в качестве аргумента, что приводит к следующему плану выполнения: 
В результате для поиска нужных данных требуется 316 операций чтения, а это занимает 9 миллисекунд (у меня очень быстрые накопители).
Это связано с тем, что «4444» необходимо сравнивать с каждой строкой, возвращаемой этой функцией.
SQL Server не может даже просто просканировать таблицу, ему нужно делать LEFT в каждой строке.
Однако вы можете сделать что-то вроде этого: SELECT a.AddressLine1,
a.AddressLine2,
a.City,
a.StateProvinceID
FROM Person.Address AS a
WHERE a.AddressLine1 LIKE '4444%' ;
И теперь мы видим совершенно другой план выполнения: 
Для выполнения запроса требуется 3 чтения и 0 миллисекунд. Ну или пусть это будет 1 миллисекунда, для объективности.
Это огромный прирост производительности.
А все потому, что я использовал функцию, которую можно использовать для поиска по индексу (ранее она называлась sargeable — непереводимое, вообще говоря, слово: SARG - Search Arguments -able, если функция SARGeable - можно передать ей столбец в качестве аргумента и Index Seek все равно будет использоваться, если не SARGeable - увы, Index Scan всегда будет б/у - ок.
переводчик ).
В любом случае не используйте функции в предложениях WHERE или условиях поиска или используйте только те, которые можно использовать в условиях поиска по индексу.
Использование многовыраженной UDF
Многооператорный UDF в русской редакции msdn переводится примерно как «Пользовательские функции, состоящие из нескольких инструкций», но это звучит, на мой взгляд, как-то странно, поэтому в заголовке и далее по тексту я постарался избежать перевода этого срок - ок..
переводчик По сути, они ловят вас.
На первый взгляд, этот замечательный механизм позволяет нам использовать T-SQL как настоящий язык программирования.
Вы можете создавать эти функции и вызывать их друг из друга, а код можно использовать повторно, в отличие от старых хранимых процедур.
Это потрясающе.
Пока вы не попытаетесь запустить этот код на большом объеме данных.
Проблема этих функций в том, что они построены на табличных переменных.
Табличные переменные — очень крутая штука, если использовать их по прямому назначению.
У них есть одно очевидное отличие от временных таблиц — по ним не строится статистика.
Эта разница может быть очень полезной, а может. убить вас.
Если у вас нет статистики, оптимизатор предполагает, что любой запрос, выполненный к табличной переменной или пользовательской функции, вернет только одну строку.
Одна (1) строка.
Это нормально, если они действительно возвращают несколько строк.
Но однажды они вернут сотни или тысячи строк, и вы решите присоединить одну UDF к другой.
Производительность упадет очень, очень быстро и очень, очень сильно.
Пример довольно большой.
Вот некоторые UDF: CREATE FUNCTION dbo.SalesInfo ()
RETURNS @return_variable TABLE
(
SalesOrderID INT,
OrderDate DATETIME,
SalesPersonID INT,
PurchaseOrderNumber dbo.OrderNumber,
AccountNumber dbo.AccountNumber,
ShippingCity NVARCHAR(30)
)
AS
BEGIN;
INSERT INTO @return_variable
(SalesOrderID,
OrderDate,
SalesPersonID,
PurchaseOrderNumber,
AccountNumber,
ShippingCity
)
SELECT soh.SalesOrderID,
soh.OrderDate,
soh.SalesPersonID,
soh.PurchaseOrderNumber,
soh.AccountNumber,
a.City
FROM Sales.SalesOrderHeader AS soh
JOIN Person.Address AS a
ON soh.ShipToAddressID = a.AddressID ;
RETURN ;
END ;
GO
CREATE FUNCTION dbo.SalesDetails ()
RETURNS @return_variable TABLE
(
SalesOrderID INT,
SalesOrderDetailID INT,
OrderQty SMALLINT,
UnitPrice MONEY
)
AS
BEGIN;
INSERT INTO @return_variable
(SalesOrderID,
SalesOrderDetailId,
OrderQty,
UnitPrice
)
SELECT sod.SalesOrderID,
sod.SalesOrderDetailID,
sod.OrderQty,
sod.UnitPrice
FROM Sales.SalesOrderDetail AS sod ;
RETURN ;
END ;
GO
CREATE FUNCTION dbo.CombinedSalesInfo ()
RETURNS @return_variable TABLE
(
SalesPersonID INT,
ShippingCity NVARCHAR(30),
OrderDate DATETIME,
PurchaseOrderNumber dbo.OrderNumber,
AccountNumber dbo.AccountNumber,
OrderQty SMALLINT,
UnitPrice MONEY
)
AS
BEGIN;
INSERT INTO @return_variable
(SalesPersonId,
ShippingCity,
OrderDate,
PurchaseOrderNumber,
AccountNumber,
OrderQty,
UnitPrice
)
SELECT si.SalesPersonID,
si.ShippingCity,
si.OrderDate,
si.PurchaseOrderNumber,
si.AccountNumber,
sd.OrderQty,
sd.UnitPrice
FROM dbo.SalesInfo() AS si
JOIN dbo.SalesDetails() AS sd
ON si.SalesOrderID = sd.SalesOrderID ;
RETURN ;
END ;
GO
Отличная структура.
Он позволяет создавать очень простые запросы.
Ну, например, здесь: SELECT csi.OrderDate,
csi.PurchaseOrderNumber,
csi.AccountNumber,
csi.OrderQty,
csi.UnitPrice
FROM dbo.CombinedSalesInfo() AS csi
WHERE csi.SalesPersonID = 277
AND csi.ShippingCity = 'Odessa' ;
Одна очень простая просьба.
Вот план его выполнения, тоже очень простой: 
Но он выполняется за 2,17 секунды, возвращает 148 строк и использует 1456 операций чтения.
Обратите внимание, что наша функция имеет нулевую стоимость, и на стоимость запроса влияет только сканирование таблицы (табличная переменная).
Хм, это правда? Давайте попробуем увидеть, что скрывается за оператором выполнения UDF с нулевой стоимостью.
Этот запрос получит план выполнения функции из кеша: SELECT deqp.query_plan,
dest.text,
SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,
(deqs.statement_end_offset - deqs.statement_start_offset)
/ 2 + 1) AS actualstatement
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqp.objectid = OBJECT_ID('dbo.CombinedSalesInfo');
И вот что там на самом деле происходит: 
Ого, похоже, здесь скрывается еще несколько таких маленьких функций и сканирований таблиц, которые почти, но не совсем, ничего не стоят. Плюс оператор соединения Hash Match, который записывает данные в базу данных tempdb и требует значительных затрат на выполнение.
Давайте посмотрим на план выполнения другой UDF: 
Здесь! И теперь мы видим Clustered Index Scan, который сканирует большое количество строк.
Это уже не здорово.
В общем, во всей этой ситуации UDF кажется всё менее привлекательным.
А что, если мы, ну, я точно не знаю, просто попытаемся получить доступ к таблицам напрямую.
Вот так, например: SELECT soh.OrderDate,
soh.PurchaseOrderNumber,
soh.AccountNumber,
sod.OrderQty,
sod.UnitPrice
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Person.Address AS ba
ON soh.BillToAddressID = ba.AddressID
JOIN Person.Address AS sa
ON soh.ShipToAddressID = sa.AddressID
WHERE soh.SalesPersonID = 277
AND sa.City = 'Odessa' ;
Теперь, выполнив этот запрос, мы получим точно те же данные, но всего за 310 миллисекунд, а не за 2170. Кроме того, SQL Server выполнит только 911 операций чтения, а не 1456. Честно говоря, очень легко получить проблемы с производительностью, используя UDF.
Включение параметра «Быстрее работайте»: использование «грязного чтения»
Возвращаясь в прошлое, к старым компьютерам с 286 процессорами на борту, можно вспомнить, что по ряду причин на передней панели у них была кнопка «Турбо».Если вы его случайно «выжали», компьютер тут же начинал безумно тормозить.
Итак, вы понимаете, что некоторые вещи всегда следует включать, чтобы обеспечить максимальную пропускную способность.
Аналогично, многие люди рассматривают уровень изоляции READ_UNCOMMITTED и подсказку NO_LOCK как турбо-кнопку для SQL Server. Используя их, будьте уверены, что практически любой запрос и вся система в целом станет быстрее.
Это связано с тем, что во время чтения не будут применяться или проверяться никакие блокировки.
Меньше закупорок означает более быстрые результаты.
Но… Когда вы используете READ_UNCOMMITTED или NO_LOCK в своих запросах, вы получаете грязное чтение.
Все понимают, что это значит, что можно читать «собака», а не «кошка», если операция обновления идет, но еще не завершилась.
Но вы также можете получить больше или меньше строк, чем есть на самом деле, а также повторяющиеся строки, поскольку страницы данных могут перемещаться во время выполнения вашего запроса, и вы не устанавливаете никаких блокировок, чтобы избежать этого.
Не знаю, как вы, но большинство компаний, в которых я работал, ожидали, что большинство запросов в большинстве систем вернут согласованные данные.
Один и тот же запрос с теми же параметрами, выполненный с тем же набором данных, должен давать один и тот же результат. Нет, если вы используете NO_LOCK. Чтобы убедиться в этом, советую вам прочитать эта почта .
Необоснованное использование подсказок в запросах
Люди слишком быстро решаются на использование подсказок.Самая распространенная ситуация, когда подсказка помогает решить очень редкую проблему по одному из запросов.
Но когда люди видят значительный прирост производительности по этому запросу.
они сразу же начинают распространять его повсюду.
Например, многие люди считают, что LOOP JOIN — лучший способ объединения таблиц.
Они приходят к такому выводу, потому что он чаще всего встречается в небольших и быстрых запросах.
Поэтому они решают заставить SQL Server использовать LOOP JOIN. Это совсем не сложно: SELECT s.[Name] AS StoreName,
p.LastName + ', ' + p.FirstName
FROM Sales.Store AS s
JOIN sales.SalesPerson AS sp
ON s.SalesPersonID = sp.BusinessEntityID
JOIN HumanResources.Employee AS e
ON sp.BusinessEntityID = e.BusinessEntityID
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
OPTION (LOOP JOIN);
Этот запрос выполняется 101 миллисекунду и совершает 4115 операций чтения.
В целом неплохо, но если убрать эту подсказку, тот же запрос будет выполняться за 90 миллисекунд и выдавать всего 2370 чтений.
Чем более загружена система, тем очевиднее будет эффективность запроса без использования подсказки.
Вот еще один пример.
Люди часто создают индекс для таблицы, ожидая, что он решит проблему.
Итак, у нас есть запрос: SELECT *
FROM Purchasing.PurchaseOrderHeader AS poh
WHERE poh.PurchaseOrderID * 2 = 3400;
Проблема снова в том, что при преобразовании столбца ни один индекс не будет использоваться должным образом.
Производительность снижается из-за сканирования кластерного индекса.
Итак, когда люди видят, что их индекс не используется, они делают следующее: SELECT *
FROM Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID))
WHERE poh.PurchaseOrderID * 2 = 3400;
И теперь они получают сканирование выбранного индекса, а не кластеризованного, поэтому индекс «используется», верно? Но меняется производительность запроса — теперь вместо 11 операций чтения выполняется 44 (время выполнения обеих около 0 миллисекунд, так как у меня действительно быстрые диски).
«Быть использованным» означает, что он используется, но совсем не по назначению.
Решение этой проблемы — переписать запрос следующим образом: SELECT *
FROM Purchasing.PurchaseOrderHeader poh
WHERE PurchaseOrderID = 3400 / 2;
Теперь количество чтений упало до двух, потому что используется поиск по индексу — индекс используется корректно.
Подсказки в запросах всегда следует использовать в последнюю очередь, после того как все другие возможные варианты были опробованы и потерпели неудачу.
Использование обработки «Ряд за мучительным рядом»
Построчная обработка выполняется с использованием курсоров или операций в цикле WHILE вместо операций с множествами.При их использовании производительность очень и очень низкая.
Курсоры обычно используются по двум причинам.
Первые из них — разработчики, привыкшие использовать в своем коде построчную обработку, а вторые — разработчики, пришедшие из Oracle и считающие, что курсоры — это хорошая вещь.
Какой бы ни была причина, курсоры убивают производительность.
Вот типичный пример неправильного использования курсора.
Нам необходимо обновить цвет товаров, выбранных по определенному критерию.
Он не выдуман — он основан на коде, который мне когда-то пришлось оптимизировать.
BEGIN TRANSACTION
DECLARE @Name NVARCHAR(50) ,
@Color NVARCHAR(15) ,
@Weight DECIMAL(8, 2)
DECLARE BigUpdate CURSOR
FOR SELECT p.[Name]
,p.Color
,p.[Weight]
FROM Production.Product AS p ;
OPEN BigUpdate ;
FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Weight < 3
BEGIN
UPDATE Production.Product
SET Color = 'Blue'
WHERE CURRENT OF BigUpdate
END
FETCH NEXT FROM BigUpdate INTO @Name, @Color, @Weight ;
END
CLOSE BigUpdate ;
DEALLOCATE BigUpdate ;
SELECT *
FROM Production.Product AS p
WHERE Color = 'Blue' ;
ROLLBACK TRANSACTION
На каждой итерации мы выполняем две операции чтения, а количество продуктов, соответствующих нашим критериям, исчисляется сотнями.
На моей машине без нагрузки время выполнения больше секунды.
Это совершенно неприемлемо, тем более что переписать этот запрос очень просто: BEGIN TRANSACTION
UPDATE Production.Product
SET Color = 'BLUE'
WHERE [Weight] < 3 ;
ROLLBACK TRANSACTION
Теперь выполняется всего 15 чтений и время выполнения составляет всего 1 миллисекунду.
Не смейся.
Люди часто пишут такой код и даже хуже.
Курсоров следует избегать и использовать только там, где без них нельзя обойтись — например, в задачах обслуживания, где нужно «пробежаться» по разным таблицам или базам данных.
Ненадлежащее использование вложенных представлений
Представления, ссылающиеся на представления, соединяющиеся с представлениями, ссылающимися на другие представления, соединяющиеся с представлениями.Представление — это просто запрос.
Но поскольку с ними можно обращаться как с таблицами, люди могут начать думать о них как о таблицах.
Но тщетно.
Что происходит, когда вы соединяете одно представление с другим, связываетесь с третьим представлением и т. д.? Вы просто создаете чертовски сложный план запроса.
Оптимизатор постарается упростить его.
Он попробует планы, в которых не используются все таблицы, но время для работы над выбором плана у него ограничено, и чем сложнее план, тем меньше вероятность, что в итоге у него получится довольно простой план выполнения.
И проблемы с производительностью будут практически неизбежны.
Вот, например, последовательность простых запросов, определяющих представления: CREATE VIEW dbo.SalesInfoView
AS
SELECT soh.SalesOrderID,
soh.OrderDate,
soh.SalesPersonID,
soh.PurchaseOrderNumber,
soh.AccountNumber,
a.City AS ShippingCity
FROM Sales.SalesOrderHeader AS soh
JOIN Person.Address AS a
ON soh.ShipToAddressID = a.AddressID ;
CREATE VIEW dbo.SalesDetailsView
AS
SELECT sod.SalesOrderID,
sod.SalesOrderDetailID,
sod.OrderQty,
sod.UnitPrice
FROM Sales.SalesOrderDetail AS sod ;
CREATE VIEW dbo.CombinedSalesInfoView
AS
SELECT si.SalesPersonID,
si.ShippingCity,
si.OrderDate,
si.PurchaseOrderNumber,
si.AccountNumber,
sd.OrderQty,
sd.UnitPrice
FROM dbo.SalesInfoView AS si
JOIN dbo.SalesDetailsView AS sd
ON si.SalesOrderID = sd.SalesOrderID ;
А вот автор текста забыл указать просьбу, но дает ее в комментариях (прим.
переводчика): SELECT csi.OrderDate
FROM dbo. CominedSalesInfoView csi
WHERE csi.SalesPersonID = 277
В результате выполнение нашего запроса занимает 155 миллисекунд и используется 965 операций чтения.
Вот план его исполнения: 
Выглядит неплохо, тем более что строк у нас получается 7000, так что вроде всё в порядке.
Но что, если мы попытаемся выполнить такой запрос: SELECT soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesPersonID = 277 ;
Теперь запрос выполняется за 3 миллисекунды и использует 685 операций чтения — существенная разница.
А вот его план исполнения: 
Как видите, оптимизатор не может выбросить все лишние таблицы в рамках процесса упрощения запроса.
Поэтому в первом плане выполнения есть две дополнительные операции — Index Scan и Hash Match, которые собирают данные вместе.
Вы можете сэкономить SQL Server дополнительную работу, написав этот запрос без использования представлений.
И помните — этот пример очень простой, большинство запросов в реальной жизни гораздо сложнее и приводят к гораздо большим проблемам с производительностью.
В комментариях к этой статье есть небольшой спор, суть которого в том, что Грант (автор статьи), похоже, выполнял свои запросы не к стандартной базе данных AdventureWorks, а к аналогичной базе данных, но с несколько иной Структура запроса в последнем разделе отличается от того, что вы могли бы увидеть, проведя эксперимент самостоятельно.
Примечание переводчика Если где-то я слишком косноязычен (а это может быть) и текст сложен для понимания, или вы можете предложить мне более качественную формулировку чего-либо, я буду рад выслушать все комментарии.
Теги: #sql #t-sql #разработка #ошибки #разработка веб-сайтов #sql #Microsoft SQL Server
-
Сервис Замены Паролей Для Разных Аккаунтов
19 Oct, 24 -
Замечательный Премайн В Криптовалютах Dag
19 Oct, 24 -
Веб-Серверы На Солнечных Батареях
19 Oct, 24
