Uderzenie wydajności przy użyciu CAST w T-SQL

Mamy generator SQL, który generalnie generuje instrukcje warunkowe SQL dla określonych pól (które dla celów dyskusji: oznaczymy jako myField).

Jeśli myFieldjest typu NVARCHAR, możemy zrobić porównanie wspomnianej dziedzinie przeciwko ciąg tak: myField = 'foo'.

Nie działa to jednak w przypadku pól typu NTEXT. Zatem musimy zrobić porównanie z obsadą: CAST(myField as NVARCHAR(MAX)) = 'foo'. To faktycznie zadziała, jeśli myFieldjest typu NVARCHARlub NTEXT.

Jaka jest skuteczność w wykonaniu wspomnianej obsady na polu, które jest już typu NVARCHAR? Mam nadzieję, że SQL Server jest wystarczająco inteligentny, aby dynamicznie rozpoznawać, że myFieldjest już tego typu NVARCHAR(skutecznie zmieniając go CASTw brak możliwości).

Jeśli obsada kolumny ma dokładnie ten sam typ danych i długość, a predykat wyszukiwania jest dosłowny, rzeczywiście wydaje się, że go lekceważy lub traktuje jako brak możliwości i szuka indeksu na zasadzie równości.

Seek Keys[1]: Prefix: [tempdb].[dbo].[#test].name = Scalar Operator(N'rpc')

Jeśli rzutowanie kolumny jest na ten sam typ danych, ale o większej długości, a predykatem wyszukiwania jest literał ciąg, powoduje skanowanie indeksu. Tego oczywiście należy unikać.

Jeśli rzutowanie kolumny jest tego samego typu danych i tej samej lub większej długości, a predykatem wyszukiwania jest zmienna lokalna, dodaje on obliczeniowy operator skalarny do planu wykonania. To wywołuje GetRangeThroughConverti generuje zakres.

Ten zakres służy do wyszukiwania indeksu i wydaje się dość wydajny

Seek Keys[1]: 
Start: [tempdb].[dbo].[#test].name > Scalar Operator([Expr1006]), 
End: [tempdb].[dbo].[#test].name < Scalar Operator([Expr1007])

Kod testowy

SELECT *
 INTO #test
  FROM [master].[dbo].[spt_values]

CREATE NONCLUSTERED INDEX [ixname] ON #test
(
    [name] ASC
)

DECLARE @name NVARCHAR(MAX)

SET @name = 'rpc'

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))= @name --Cast the same and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))=@name --Cast to longer and local variable

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(35))='rpc' --Cast the same and literal

SELECT name
FROM #test
WHERE CAST(name AS NVARCHAR(MAX))='rpc' --Cast to longer and literal

Ogólnie rzecz biorąc, CASTzabije wydajność, ponieważ unieważnia jakiekolwiek użycie indeksów, jak pokazuje ostatni przykład Martina Smitha. Przesyłanie na nvarchar(max)inną długość lub na inną oznacza inny typ danych: fakt, że wszystko nvarcharjest nieistotne.

Ponadto ważny jest również typ danych po prawej stronie porównania. Jeśli jest to zmienna lokalna lub parametr o innej długości, jedna strona będzie domyślnie przypisana CASTdo najszerszego z 2 typów danych (patrz pierwszeństwo typu danych ).

Zasadniczo, jeśli masz generalnego CASTna nvarchar(max)to Bollix rzeczy. Zastanowiłbym się nad poprawieniem użycia, ntextzanim dodam CASTwszystko.

Konwersja może nie być wyświetlana w planie zapytań. Zobacz artykuł na blogu Paula White'a

Tylko uwaga: przesyłanie w ten sposób, gdy Datecreated jest datetime

 Cast (Datecreated as date) = cast(@MydatetimeValue as date)

Nie ogranicza zdolności SQL do używania indeksów, jeśli indeksy istnieją, a jeśli nie istnieją, może spowodować zapisanie brakującego indeksu.

Podobnie, podczas rzucania od intcelu tinyintlub bigintdo intetc, funkcja obsada nie zatrzymuje SQL z użyciem indeksów IF Optymalizator wie, że operacja cast nie zmienia porządek z 2 porównywalnych typów danych.

Oto kilka testów, które możesz uruchomić i zobaczyć aktualny plan za pomocą Adventureworks2008R2

select count(*) from Sales.SalesOrderDetail where SalesOrderID = 8 --1
select top 10 * from Sales.SalesOrderDetail where cast(SalesOrderID as tinyint) = 8  --2
select top 10 * from Sales.SalesOrderDetail where cast(SalesOrderID as bigint) = 8  --3
select top 10 SalesOrderID from Sales.SalesOrderDetail where cast(ModifiedDate  as date) = '19780322' --4
select top 10 SalesOrderID from Sales.SalesOrderDetail where convert(date,ModifiedDate) = '19780322'  --5
select top 10 SalesOrderID from Sales.SalesOrderDetail where cast(ModifiedDate as varchar(20)) = '1978'  --6 -- THIS WILL NOT USE INDEX
select  SalesOrderID from Sales.SalesOrderDetail where cast(ModifiedDate  as date) between '19780101' and '19780109'  --7