Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 CONVERT_IMPLICIT когда работает  [new]
Acce_Ekb
Member

Откуда: Екатеринбург
Сообщений: 87
Добрый день,

Вопрос теоретический.
Замечено, что для разных типов данных CONVERT_IMPLICIT ведет себя по-разному.

Рассмотрим пример

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Developer Edition (64-bit) 


-- Test 1 - int and varchar
if object_id( 'dbo.TestConversion' ) is not null
  drop table dbo.TestConversion
create table dbo.TestConversion ( f1 varchar(100) )

insert into dbo.TestConversion values ('1'), ('2'), ('3')
select * from dbo.TestConversion where f1 = 1

-- Test 2 - datetime and smalldatetime
if object_id( 'dbo.TestConversion' ) is not null
  drop table dbo.TestConversion
create table dbo.TestConversion ( f1 smalldatetime )

insert into dbo.TestConversion 
values (cast( '20190101' as smalldatetime))
, (cast( '20190201' as smalldatetime))
, (cast('20190301' as smalldatetime))

eclare @d datetime = '20190101 22:33:44.333'
select * from dbo.TestConversion where f1 = @d

-- Test 3 - varchar and nvarchar
if object_id( 'dbo.TestConversion' ) is not null
  drop table dbo.TestConversion
create table dbo.TestConversion ( f1 varchar(100) )

insert into dbo.TestConversion values ('a'), ('s'), ('d')
select * from dbo.TestConversion where f1 = N'a'
go

if object_id( 'dbo.TestConversion' ) is not null
  drop table dbo.TestConversion
go


Смотрим планы запросов

Тест 1 - int и varchar
Все ожидаемо.
Предикат:
CONVERT_IMPLICIT(int,[Test].[dbo].[TestConversion].[f1],0)=[@0]

и видим предупреждение:
Преобразование типов в выражении (CONVERT_IMPLICIT(int,[Test].[dbo].[TestConversion].[f1],0)) может повлиять на "CardinalityEstimate" в выборе плана запроса


Тест 2 - smalldatetime и datetime
Предикат:
[Test].[dbo].[TestConversion].[f1]=[@d]

т.е. никакого CONVERT_IMPLICIT !

Тест 3 - varchar и nvarchar
Предикат:
CONVERT_IMPLICIT(nvarchar(100),[Test].[dbo].[TestConversion].[f1],0)=[@0]

но нет предупреждения, как в тесте 1.
27 апр 19, 10:54    [21873264]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Acce_Ekb,

А почему должно?
Я так понимаю думайте над convert('' as int)
27 апр 19, 11:38    [21873281]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
Acce_Ekb
Member

Откуда: Екатеринбург
Сообщений: 87
TaPaK,

Наверно, потому что во всех случаях одинаковый случай type precedence?
Думаю про smalldatetime больше всего. Почему он не приводится к более "старшему" типу, а во всех остальных случаях приводится.
27 апр 19, 12:31    [21873305]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Acce_Ekb,

Он приводится, просто этого в планах нет.
Есть семейства типов данных, внутри одного семейства не показываются такие преобразования.
Можете покурить статью Пола Уайта на эту тему, там на примере соединений, но суть та же, скрытые преобразования:
автор
So what’s going on here? Why is SQL Server hiding conversion details from us that have such a profound effect on execution speed?

It turns out that certain implicit conversions can be performed inside the join, and so do not require a separate Compute Scalar. This is the case if the join columns have different types, but both types come from the same ‘family’.

https://www.sql.kiwi/2011/07/join-performance-implicit-conversions-and-residuals.html

На вашем примере:
+
use tempdb;
go
set nocount on;
-- Test 2 - datetime and smalldatetime
drop table if exists dbo.TestConversion;
create table dbo.TestConversion (f1 smalldatetime);

insert dbo.TestConversion (f1)
select top(1000000) '2019-01-01' from master..spt_values v1, master..spt_values v2, master..spt_values v3;
go

declare 
	@d datetime = '2019-01-02',
	@sd smalldatetime = '2019-01-02',
	@f1 smalldatetime;

set statistics time on;
select @f1 = f1 from dbo.TestConversion where f1 = @d;
select @f1 = f1 from dbo.TestConversion where f1 = @sd;
set statistics time off;


Результат от:
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 360 ms.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 162 ms.
До, например:
SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 576 ms.
SQL Server Execution Times:
CPU time = 438 ms, elapsed time = 430 ms.

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

А увидеть, что преобразования есть, можно увы только в Debugger, вот например, как ваш запрос из теста 2 выполняется. Я выделил цветом участки, где идут преобразования типов, первое на этапе биндинга запроса выясняется все необходимое для корректной работы с типами, второе идет сравнение двух значений. За работу с типами отвечает модуль sqlTsEs - SQL Type System and Expression Services.

Что касается предупреждений, я бы вообще на них особо не ориентировался, механизм, на мой взгляд, до конца не продуман.

К сообщению приложен файл. Размер - 19Kb
27 апр 19, 14:12    [21873340]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
Acce_Ekb
Member

Откуда: Екатеринбург
Сообщений: 87
SomewhereSomehow
https://www.sql.kiwi/2011/07/join-performance-implicit-conversions-and-residuals.html

Спасибо, интересно про семейства.
27 апр 19, 20:18    [21873488]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Acce_Ekb,

имо преобразование отображается в случае преобразования типа с меньшим приоритетом к большему, а наоборот - нет.

https://docs.microsoft.com/ru-ru/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017
29 апр 19, 11:22    [21874071]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Владислав Колосов
Acce_Ekb,
имо преобразование отображается в случае преобразования типа с меньшим приоритетом к большему, а наоборот - нет.

1. А наоборот что, бывает? Бывает случай неявного преобразования от большего приоритета к меньшему?
2. Любые два неодинаковых типа будут иметь, очевидно, разный приоритет, значит один из них будет выше, другой ниже, тем не менее, выше вы видели пример, когда никакой информации о преобразовании в плане не отображается.
29 апр 19, 12:27    [21874168]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
Acce_Ekb
Member

Откуда: Екатеринбург
Сообщений: 87
SomewhereSomehow,

Эти явное и неявное преобразования получаются "неравноценные".

Если сделать кластерный индекс по этому единственному полю и выполнять поиск по значению, то в тестах 1 и 3 (где CONVERT_IMPLICIT) увидим scan-ы, а в тесте 2 будет seek.

Видимо, потому что имея на входе значение типа datetime мы можем использовать для поиска дерево индекса по smalldatetime, т.к. значения smalldatetime и datetime упорядочены одинаково.
А, например, имея на входе значение типа int мы не можем использовать для поиска дерево индекса по varchar, т.к. множество int и множество соответствующих им разных varchar упорядочены неодинаково (например, числу 9 соответствуют строки "9", "09", "000009", " 9" и т.д.).
29 апр 19, 19:46    [21874807]     Ответить | Цитировать Сообщить модератору
 Re: CONVERT_IMPLICIT когда работает  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Acce_Ekb
Эти явное и неявное преобразования получаются "неравноценные".


Речь шла только про НЕ явные преобразования и отображаются ли они в плане.

Если вы про отличия неявных, то да, они неравноценные. Некоторые сервер умеет обрабатывать так, что нам о них из плана не узнать. Этот пример вы видели в моем первом ответе. Преобразования там происходят на уровне Query Executor и Storage Engine.

Некоторые видны из графического представления плана опосредованно, но видны, если его открыть как XML. Например:
declare @t table  (a date primary key);
select * from @t where a > getdate();
go

Хотя столбец date, а функция getdate() это datetime, вы не найдете слово CONVERT_IMPLICIT, но в можно заметить интересную форму плана, а тексте XML увидеть функцию: GetRangeWithMismatchedTypes, которая преобразует getdate() в открытый интервал по date и позволяет выполнить поиск.

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

Насчет использования дерева, разумеется, если поиск происходит, дерево индекса используется.

Возвращаясь к первоначальной теме, как отображается CONVERT_IMPLICIT в планах, выглядит это довольно забавно. Вот простой пример. Возьмем кусок из таблицы типов, приведенной в ссылке выше, и относящийся к одному условному семейству типов данных: дата и время.
• 5. datetime2
• 6. datetime
• 7. smalldatetime
• 8. date

Таблицу с одним столбцом (PK) типа smalldatetime сравним с переменными типов выше по приоритету и ниже по приоритету. Потом то же самое с явной функцией CONVERT над PK. Потом проделаем то же самое для таблицы с типом столбца datetime. Все это со включенным планом. Посмотрим, что отобразится в предикатах, будет ли это поиск или просмотр.
+
----------------------------------------
-- 1. smalldatetime 
/*
5. datetime2
6. datetime
	7. smalldatetime  (test table type)
8. date
*/
declare @t table(c smalldatetime primary key);

declare @dt2 datetime2;
-- 1.1
-- Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])
select c from @t where c = @dt2;
-- 1.2
-- Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])
select c from @t where convert(datetime2,c) = @dt2; 

declare @dt datetime;
-- 1.3
-- Seek Keys[1]: Prefix: c = Scalar Operator([@dt])
select c from @t where c = @dt;
-- 1.4
-- Seek Keys[1]: Prefix: c = Scalar Operator([@dt])
select c from @t where convert(datetime, c) = @dt;

declare @d date;
-- 1.5
-- Seek Keys[1]: Prefix: c = Scalar Operator([@d])
select c from @t where c = @d;
-- 1.6
-- Seek Keys[1]: Start: c > Scalar Operator([Expr1004]); End: c < Scalar Operator([Expr1005]) (GetRangeThroughConvert)
-- Predicate: CONVERT(date,[c],0)=[@d]
select c from @t where convert(date, c) = @d; 
go

----------------------------------------
-- 2. datetime 
/*
5. datetime2
	6. datetime (test table type)
7. smalldatetime
8. date
*/
declare @t table(c datetime primary key);

declare @dt2 datetime2;
-- 2.1
-- Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])
select c from @t where c = @dt2; 
-- 2.2
-- Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])
-- Predicate: CONVERT(datetime2(7),[c],0)=[@dt2]
select c from @t where convert(datetime2,c) = @dt2; 

declare @sdt smalldatetime;
-- 2.3
-- Seek Keys[1]: Prefix: c = Scalar Operator(CONVERT_IMPLICIT(datetime,[@sdt],0))
select c from @t where c = @sdt;
-- 2.4
-- Seek Keys[1]: Start: c > Scalar Operator([Expr1004]); End: c < Scalar Operator([Expr1005])
-- Predicate: CONVERT(smalldatetime,[c],0)=[@sdt]
select c from @t where convert(smalldatetime, c) = @sdt;

declare @d date;
-- 2.5
-- Seek Keys[1]: Prefix: c = Scalar Operator([@d])
select c from @t where c = @d;
-- 2.6
-- Seek Keys[1]: Start: c > Scalar Operator([Expr1004]); End: c < Scalar Operator([Expr1005])
-- Predicate: CONVERT(date,[c],0)=[@d]
select c from @t where convert(date, c) = @d;
go


Smalldatetime

1.1 datetime2
Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])

Несмотря на то, что по порядку приоритета более низкий тип smalldatetime, тип столбца, должен был быть преобразован к более высокому, типу переменной datetime2 и способность поиска по индексу утрачена – этого не происходит. Сервер преобразует все это на более низком уровне, мы этого не видим.

1.2 CONVERT и datetime2
Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])

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

1.3, 1.4 datetime

Тоже более высокий по приоритету тип, выглядит все так же, как и в datetime2, опустим для краткости.

1.5 date
Seek Keys[1]: Prefix: c = Scalar Operator([@d])

Здесь все логично, переменная более низкого по приоритету типа date, приводится к smalldatetime. Обратите внимание, что никакого CONVERT_IMPLICIT(@d) не отображается, просто Scalar Operator([@d]).

1.6 CONVERT и date
Seek Keys[1]: Start: c > Scalar Operator([Expr1004]); End: c < Scalar Operator([Expr1005])
Predicate: CONVERT(date,[c],0)=[@d]


Здесь мы опять явно использовали функцию над полем, но при этом, сервер все равно может использовать поиск! Для этого, он строит интервал, используя внутреннюю функцию GetRangeThroughConvert, выполняет поиск в нем, а уже потом остаточным (residual) предикатом проверяет на точное соответствие. Так что мы видим два предиката, поиска и обычный.

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

Datetime

2.1 datetime2
Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])

Сравниваем с более высоким по приоритету типом datetime2. Получаем результат аналогичный пункту 1.1. Пока все ожидаемо.

2.2 CONVERT и datetime2
Seek Keys[1]: Prefix: c = Scalar Operator([@dt2])
Predicate: CONVERT(datetime2(7),[c],0)=[@dt2]


По-прежнему получаем предикат поиска, но, внезапно (!) еще и остаточный предикат, который содержит наш CONVERT. Если посмотреть на 1.2 – такого там не было, CONVERT исчезал бесследно =) И уж тем более не было остаточного предиката.

2.3 smalldatetime
Seek Keys[1]: Prefix: c = Scalar Operator(CONVERT_IMPLICIT(datetime,[@sdt],0))

Сравниваем с более низким по приоритету типом smalldatetime, переменная приводится smalldatetime приводится к datetime, получаем поиск, все нормально. Но, что мы видим, преобразование переменной происходит при помощи CONVERT_IMPLICIT! Посмотрите на пункт 1.5, где в аналогичной ситуации никакого CONVERT_IMPLICIT не было.

2.4 CONVERT и smalldatetime
Seek Keys[1]: Start: c > Scalar Operator([Expr1004]); End: c < Scalar Operator([Expr1005])
Predicate: CONVERT(smalldatetime,[c],0)=[@sdt]


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

2.5 date
Seek Keys[1]: Prefix: c = Scalar Operator([@d])

Снова сравниваем с типом с более низким приоритетом, на этот раз с date. Переменная приводится к более высокому типу, а CONVERT_IMPLICIT из пункта 2.3, который действовал для smalldatetime… внезапно исчезает, как и мой запал писать ответ дальше =)

2.6 CONVERT и date
Seek Keys[1]: Start: c > Scalar Operator([Expr1004]); End: c < Scalar Operator([Expr1005])
Predicate: CONVERT(date,[c],0)=[@d]


Совпадает с 2.4 и 1.6 – тут все нормально.

Один уважаемый специалист в шутку говорит, что “SQL Server was designed for maximum confusion” =), и иногда хочется с ним согласиться. Хотя, многое вызвано просто требованиями обратной совместимости.

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

Удачи в конвертациях =)
30 апр 19, 01:19    [21875070]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить