ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / CONVERT_IMPLICIT когда работает
9 сообщений из 9, страница 1 из 1
CONVERT_IMPLICIT когда работает
    #39807141
Acce_Ekb
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Acce_Ekb
Участник
Добрый день,

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

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

Код: sql
1.
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Developer Edition (64-bit)



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
-- 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
Все ожидаемо.
Предикат:
Код: sql
1.
CONVERT_IMPLICIT(int,[Test].[dbo].[TestConversion].[f1],0)=[@0]


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



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


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

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


но нет предупреждения, как в тесте 1.
...
Рейтинг: 0 / 0
27.04.2019, 10:54
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39807149
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Acce_Ekb,

А почему должно?
Я так понимаю думайте над convert('' as int)
...
Рейтинг: 0 / 0
27.04.2019, 11:38
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39807158
Acce_Ekb
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Acce_Ekb
Участник
TaPaK,

Наверно, потому что во всех случаях одинаковый случай type precedence?
Думаю про smalldatetime больше всего. Почему он не приводится к более "старшему" типу, а во всех остальных случаях приводится.
...
Рейтинг: 0 / 0
27.04.2019, 12:31
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39807166
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
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

На вашем примере:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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.

Что касается предупреждений, я бы вообще на них особо не ориентировался, механизм, на мой взгляд, до конца не продуман.
...
Рейтинг: 0 / 0
27.04.2019, 14:12
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39807211
Acce_Ekb
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Acce_Ekb
Участник
SomewhereSomehow https://www.sql.kiwi/2011/07/join-performance-implicit-conversions-and-residuals.html

Спасибо, интересно про семейства.
...
Рейтинг: 0 / 0
27.04.2019, 20:18
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39807497
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Acce_Ekb,

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

https://docs.microsoft.com/ru-ru/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017
...
Рейтинг: 0 / 0
29.04.2019, 11:22
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39807559
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
Владислав КолосовAcce_Ekb,
имо преобразование отображается в случае преобразования типа с меньшим приоритетом к большему, а наоборот - нет.

1. А наоборот что, бывает? Бывает случай неявного преобразования от большего приоритета к меньшему?
2. Любые два неодинаковых типа будут иметь, очевидно, разный приоритет, значит один из них будет выше, другой ниже, тем не менее, выше вы видели пример, когда никакой информации о преобразовании в плане не отображается.
...
Рейтинг: 0 / 0
29.04.2019, 12:27
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39807888
Acce_Ekb
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Acce_Ekb
Участник
SomewhereSomehow,

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

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

Видимо, потому что имея на входе значение типа datetime мы можем использовать для поиска дерево индекса по smalldatetime, т.к. значения smalldatetime и datetime упорядочены одинаково.
А, например, имея на входе значение типа int мы не можем использовать для поиска дерево индекса по varchar, т.к. множество int и множество соответствующих им разных varchar упорядочены неодинаково (например, числу 9 соответствуют строки "9", "09", "000009", " 9" и т.д.).
...
Рейтинг: 0 / 0
29.04.2019, 19:46
| Ответить | Цитировать | Написать  
CONVERT_IMPLICIT когда работает
    #39808061
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
Acce_EkbЭти явное и неявное преобразования получаются "неравноценные".

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

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

Некоторые видны из графического представления плана опосредованно, но видны, если его открыть как XML. Например:
Код: sql
1.
2.
3.
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. Все это со включенным планом. Посмотрим, что отобразится в предикатах, будет ли это поиск или просмотр.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
----------------------------------------
-- 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” =), и иногда хочется с ним согласиться. Хотя, многое вызвано просто требованиями обратной совместимости.

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

Удачи в конвертациях =)
...
Рейтинг: 0 / 0
30.04.2019, 01:19
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / CONVERT_IMPLICIT когда работает
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]