Неявные преобразования (Implicit Conversions)

Автор: Craig Freedman Implicit Conversions

В нескольких статьях я уже показывал, как явные преобразования могут приводить к ошибкам. В этой статье рассмотрим некоторые проблемы, связанные с неявными преобразованиями. SQL Server добавляет неявные преобразования, когда в одном выражении запроса используются колонки, переменные и/или параметры с разными (но совместимыми) типами данных. Например, если нужно сравнить колонки с типами INT и FLOAT, INT необходимо преобразовать в FLOAT. Если вы напишете "C_INT = C_FLOAT", SQL Server перепишет это выражение как "CONVERT (FLOAT, C_INT) = C_FLOAT".

При выполнении неявных преобразований SQL Server попытается выбрать преобразование, которое с наименьшей вероятностью приведет к сбою из-за переполнения или потере точности. Например, SMALLINT будет преобразовано в INT, поскольку все SMALLINT могут быть преобразованы в INT без потери данных. С другой стороны, INT будет преобразовано в REAL, поскольку все INT могут быть преобразованы в REAL, но не наоборот. Однако преобразование потенциально может привести к потерям, поскольку некоторые INT содержат больше цифр, чем может быть представлено REAL.

Обратите внимание, что некоторые типы данных можно объединить только в одном выражении с помощью явного преобразования, а некоторые типы данных вообще несовместимы с явным преобразованием или без него. На этой странице Books Online показана матрица совместимости, показывающая все возможные комбинации преобразования типов данных. В этой статье мы сосредоточимся только на неявных преобразованиях.

Для начала посмотрим пример, который не очень хорошо работал на SQL Server 2000 и тем удобен для демонстрации:

CREATE TABLE T (C INT)

CREATE UNIQUE CLUSTERED INDEX TC ON T(C)

INSERT T VALUES (1000000000)

INSERT T VALUES (1000000001)

Сначала рассмотрим запрос, использующий просмотр кластерного индекса:

DECLARE @V REAL

SET @V = 1E9

SELECT * FROM T WITH (INDEX(0)) WHERE C = @V

 

C          

-----------

1000000000

1000000001

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

|--Clustered Index Scan(OBJECT:([T].[TC]), WHERE:(Convert([T].[C])=[@V]))

Более того, если мы проверим результаты преобразования, то увидим, что оно выполнилось с потерями. Оба INT преобразуются в одно и то же значение REAL. Приведенные выше результаты теперь имеют больше смысла:

 

SELECT *, CONVERT(REAL,C) REAL_C FROM T

 

C           REAL_C                  

----------- ------------------------

1000000000  1.0E+9

1000000001  1.0E+9

 

Теперь рассмотрим тот же запрос с поиском по кластерному индексу:

 

DECLARE @V REAL

SET @V = 1E9

SELECT * FROM T WITH (INDEX(1)) WHERE C = @V

 

C          

-----------

1000000000

Подождите, куда пропала вторая строка? Давайте заглянем в план:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
       |--Compute Scalar(DEFINE:([Expr1002]=Convert([@V])-1, [Expr1003]=Convert([@V])+1, [Expr1004]=If (Convert([@V])-1=NULL) then 0 else 6|If (Convert([@V])+1=NULL) then 0 else 10))
       |    |--Constant Scan
       |--Clustered Index Seek(OBJECT:([T].[TC]), SEEK:([T].[C] > [Expr1002] AND [T].[C] < [Expr1003]),  WHERE:(Convert([T].[C])=[@V]) ORDERED FORWARD)

Этот план выглядит сложнее, но, помимо демонстрации, к сожалению, неправильного результата (согласно семантике преобразования, которую мы обсудили выше), на самом деле он довольно прост. Для выполнения поиска по индексу SQL Server должен оперировать значениями ключа, которые соответствуют типу данных в индексе. SQL Server не может выполнять поиск по индексу по колонке INT с использованием ключа с типом REAL. Поэтому SQL Server должен преобразовать переменную REAL в INT. Поскольку преобразования могут быть с потерями, SQL Server ещё и расширил диапазон значений, возвращаемых поиском по индексу, вычитая и прибавляя единицу к результату преобразования. По сути, после подстановки значения параметра этот план будет такой, как и если бы выполнялся следующего запроса:

SELECT * FROM T WHERE (C > 999999999 AND C < 1000000001) AND CONVERT(REAL,C) = 1E9

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

CREATE TABLE T (C NUMERIC(12,4))

CREATE UNIQUE CLUSTERED INDEX TC ON T(C)

INSERT T VALUES (0.0001)

INSERT T VALUES (0.0002)

INSERT T VALUES (0.0003)

...

DECLARE @P REAL

SET @P = 1.0000

SELECT * FROM T WITH (INDEX(1)) WHERE C = @P

Предположим, что таблица T содержит много записей со значениями в диапазоне от 0,0000 до 2,0000. В этом примере преобразование выполняется без потерь, запросом возвращаются только строки со значением 1,0000. К сожалению, поиск по индексу тут будет выбирать все записи в диапазоне, а потом фильтр с преобразованием оставит строки со значением 1,0000.

Теперь давайте посмотрим, как в SQL Server с версии 2005 обрабатываются эти же запросы. Начнем со просмотра кластерного индекса:

DECLARE @P REAL

SET @P = 1E9

SELECT * FROM T WITH (INDEX(0)) WHERE C = @P

  |--Clustered Index Scan(OBJECT:([T].[TC]), WHERE:(CONVERT_IMPLICIT(real(24),[T].[C],0)=[@P]))

Это тот же план, как в SQL Server 2000, и результат тот же (обе строки). Единственное отличие в том, что SQL Server теперь указывает, что преобразование было неявное, и показывает тип этого преобразования. Теперь давайте попробуем выполнить поиск по кластерному индексу:

DECLARE @P REAL

SET @P = 1E9

SELECT * FROM T WITH (INDEX(1)) WHERE C = @P

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
       |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@P],[@P],(62))))
       |    |--Constant Scan
       |--Clustered Index Seek(OBJECT:([T].[TC]), SEEK:([T].[C] > [Expr1005] AND [T].[C] < [Expr1006]),  WHERE:(CONVERT_IMPLICIT(real(24),[T].[C],0)=[@P]) ORDERED FORWARD)

Этот план тоже похож на сгенерированный SQL Server 2000 с одним важным отличием: преобразование для поискового ключа из REAL в INT выполняется внутренней функцией GetRangeThroughConvert, которая правильно определяет точный диапазон значений для поиска. Небольшой эксперимент покажет, что в этом примере затрагивается диапазон для значений между 999999968 и 1000 000479. С другой стороны, для примера с числовым столбцом «диапазон» срабатывает ровно до 10000. Это решение и точнее, и эффективнее, чем алгоритм SQL Server 2000.

Наконец, давайте рассмотрим влияние нового плана на производительность:

CREATE TABLE T1 (C_INT INT, C_REAL REAL)

CREATE CLUSTERED INDEX T1_C_REAL ON T1(C_REAL)

CREATE TABLE T2 (C_INT INT, C_REAL REAL)

CREATE CLUSTERED INDEX T2_C_INT ON T2(C_INT)

SET NOCOUNT ON

DECLARE @I INT

SET @I = 0

WHILE @I < 100000

  BEGIN

    INSERT T1 VALUES (@I, @I)

    INSERT T2 VALUES (@I, @I)

    SET @I = @I + 1

  END

Ниже три запроса и три плана с соединениями. В них используется подсказка типа соединения, чтобы гарантированно не получить хэш-соединение, которое использует совершенно другой алгоритм. COUNT(*) используется для исключения накладных расходов на передачу строк клиенту, а подсказка OPTION, чтобы избежать параллелизма. Первый план соединяет две колонки INT. Второй план соединяет два столбца REAL. Третий план соединяет колонки INT и REAL и использует тот же алгоритм с функцией GetRangeThroughConvert, что и простой запрос выше.

SELECT COUNT(*)

FROM T1 INNER LOOP JOIN T2 ON T1.C_INT = T2.C_INT

OPTION(MAXDOP 1)

  |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1012],0)))
       |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[C_INT], [Expr1011]) WITH UNORDERED PREFETCH)
                 |--Clustered Index Scan(OBJECT:([T1].[T1_C_REAL]))
                 |--Clustered Index Seek(OBJECT:([T2].[T2_C_INT]), SEEK:([T2].[C_INT]=[T1].[C_INT]) ORDERED FORWARD)

SELECT COUNT(*)

FROM T2 INNER LOOP JOIN T1 ON T2.C_REAL = T1.C_REAL

OPTION(MAXDOP 1)

  |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1012],0)))
       |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[C_REAL], [Expr1011]) WITH UNORDERED PREFETCH)
                 |--Clustered Index Scan(OBJECT:([T2].[T2_C_INT]))
                 |--Clustered Index Seek(OBJECT:([T1].[T1_C_REAL]), SEEK:([T1].[C_REAL]=[T2].[C_REAL]) ORDERED FORWARD)

SELECT COUNT(*)

FROM T1 INNER LOOP JOIN T2 ON T1.C_REAL = T2.C_INT

OPTION(MAXDOP 1)

  |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1014],0)))
       |--Stream Aggregate(DEFINE:([Expr1014]=Count(*)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[C_REAL]))
                 |--Clustered Index Scan(OBJECT:([T1].[T1_C_REAL]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1012], [Expr1013], [Expr1011]))
                      |--Compute Scalar(DEFINE:(([Expr1012],[Expr1013],[Expr1011])=GetRangeThroughConvert([T1].[C_REAL],[T1].[C_REAL],(62))))
                      |    |--Constant Scan
                      |--Clustered Index Seek(OBJECT:([T2].[T2_C_INT]), SEEK:([T2].[C_INT] > [Expr1012] AND [T2].[C_INT] < [Expr1013]),  WHERE:([T1].[C_REAL]=CONVERT_IMPLICIT(real(24),[T2].[C_INT],0)) ORDERED FORWARD)

Время исполнения отслеживаем с помощью SET STATISTICS TIME ON. Вот результаты, которые я получил на своей системе:

·        SQL Server Execution Times:   CPU time = 750 ms,  elapsed time = 741 ms.

·        SQL Server Execution Times:   CPU time = 735 ms,  elapsed time = 742 ms.

·        SQL Server Execution Times:   CPU time = 1609 ms, elapsed time = 1605 ms.

Обратите внимание, что первые два запроса с соединениями по совпадающим типам завершается за 740 ms, тогда как третий запрос с соединением по несовпадающим типам выполняется более чем в два раза дольше — 1600 ms! Мораль этой истории? Старайтесь придерживаться совпадающих типов, когда это возможно.