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

Откуда:
Сообщений: 748
Есть field1, например, типа datetime. Есть field2 as convert(date,field1). По field2 создан индекс. Умеет ли сервер последней версии использовать индекс, если в запросе есть where convert(date,field1)=...?
23 июн 17, 20:26    [20587210]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36693
Псоледней версии -- это какой?
23 июн 17, 20:28    [20587212]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
tunknown
Умеет ли сервер последней версии использовать индекс, если в запросе есть where convert(date,field1)=...?
Умеет. И даже далеко не последней версии.
Более того, вычисляемый столбец и индекс по нему не требуется.
Достаточно индекса по field1, тогда предикат convert(date,field1) = ... будет преобразован в field1 between ... and ...
23 июн 17, 21:14    [20587296]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
wadegwadeg
Member

Откуда:
Сообщений: 36
invm
Достаточно индекса по field1, тогда предикат convert(date,field1) = ... будет преобразован в field1 between ... and ...
Да ладно, так сервер по состоянию на
Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)   Mar  6 2017 14:18:16   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit)
еще не умеет:
if object_id('tmp','U') is not null
	drop table tmp;
create table tmp (Id int not null primary key, dt datetime not null, d as convert(date, dt), index ix_tmp_dt(dt));
GO
insert into tmp (Id, dt) select [object_id], create_date from sys.objects;
update statistics tmp with all, fullscan;
GO

declare @dt datetime = (select top 1 dt from tmp where dt < dateadd(dd, -180, getdate()));
declare @d date = @dt;
select * from tmp where convert(date,dt) = @d;
select * from tmp where d = @d;
select * from tmp where dt >= @d and dt < dateadd(dd,1,@d);


К сообщению приложен файл. Размер - 47Kb
26 июн 17, 19:55    [20592025]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
msLex
Member

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

я не скажу, в чем у вас проблема, но вот для такого запроса
select @@VERSION
if object_id('tmp','U') is not null
	drop table tmp;
create table tmp (Id int not null primary key, dt datetime not null);
create index dt on tmp (dt)


GO
insert into tmp (Id, dt) select [object_id], create_date from sys.objects;
update statistics tmp with all, fullscan;
GO

declare @dt datetime = (select top 1 dt from tmp where dt < dateadd(dd, -180, getdate()));
declare @d date = @dt;
select * from tmp where convert(date, dt) = @d;



на разных серверах
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2861.0 (X64)
Aug 20 2012 19:28:58
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Microsoft SQL Server 2012 (SP2-CU15-GDR) (KB3194725) - 11.0.5676.0 (X64)
Oct 10 2016 18:32:51
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Microsoft SQL Server 2014 - 12.0.2546.0 (X64)
Jun 6 2015 00:57:37
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)
Mar 6 2017 14:18:16
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )


план везде идентичный

К сообщению приложен файл. Размер - 30Kb
26 июн 17, 20:19    [20592086]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
wadegwadeg
Member

Откуда:
Сообщений: 36
Проверяю на
@@version
Microsoft SQL Server 2014 (SP2-CU5) (KB4013098) - 12.0.5546.0 (X64) Apr 3 2017 14:55:37

Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Mar 6 2017 14:18:16

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14
- везде одна и та же вышеприведенная мною картина. Со всеми окружающими серверами одновременно что-то не так? Даже серверы с ума по одиночке сходят. У кого-нибудь еще из благородных донов подхватывается index seek?
26 июн 17, 20:44    [20592118]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
wadegwadeg,

+
use tempdb;
go

create table tmp (Id int not null primary key, dt datetime not null);
create index ix_tmp_dt on tmp(dt);
go

insert into tmp (Id, dt) select [object_id], create_date from sys.objects;
update statistics tmp with all, fullscan;
go

--set statistics profile on;
--go

declare @d date = (select top 1 dt from tmp where dt < dateadd(dd, -180, getdate()));

select * from tmp where convert(date, dt) = @d;
/*
select * from tmp where convert(date, dt) = @d;
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
       |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@d],[@d],(62))))
       |    |--Constant Scan
       |--Index Seek(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]), SEEK:([tempdb].[dbo].[tmp].[dt] > [Expr1005] AND [tempdb].[dbo].[tmp].[dt] < [Expr1006]),  WHERE:(CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)=[@d]) ORDERED FORWARD)
*/

alter table tmp add d as case when 1 = 1 then convert(date, dt) end;
select * from tmp where convert(date, dt) = @d;
/*
select * from tmp where convert(date, dt) = @d;
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
       |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@d],[@d],(62))))
       |    |--Constant Scan
       |--Index Seek(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]), SEEK:([tempdb].[dbo].[tmp].[dt] > [Expr1005] AND [tempdb].[dbo].[tmp].[dt] < [Expr1006]),  WHERE:(CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)=[@d]) ORDERED FORWARD)*/

alter table tmp drop column d;
alter table tmp add d as convert(date, dt);
select * from tmp where convert(date, dt) = @d;
/*
select * from tmp where convert(date, dt) = @d;
  |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=[tempdb].[dbo].[tmp].[d]))
       |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)))
            |--Index Scan(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]),  WHERE:(CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)=[@d]))
*/

create index ix_tmp_d on tmp(d);
select * from tmp where convert(date, dt) = @d;
/*
select * from tmp where convert(date, dt) = @d;
  |--Filter(WHERE:([tempdb].[dbo].[tmp].[d]=[@d]))
       |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=[tempdb].[dbo].[tmp].[d]))
            |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)))
                 |--Index Scan(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]))
*/

create index ix_tmp_d on tmp(d) include (dt) with (drop_existing = on);
select * from tmp where convert(date, dt) = @d;
/*
select * from tmp where convert(date, dt) = @d;
  |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=[tempdb].[dbo].[tmp].[d]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_d]), SEEK:([tempdb].[dbo].[tmp].[d]=[@d]) ORDERED FORWARD)
*/
go

--set statistics profile off;
--go

drop table tmp;
go
26 июн 17, 21:08    [20592162]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
invm,

такое поведение
case when 1 = 1 then convert(date, dt)
где то описано подробно?

В плане того что вычисляемый столбец должен создаваться с дополнительным выражением?
26 июн 17, 22:23    [20592274]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
wadegwadeg
Member

Откуда:
Сообщений: 36
Рассмотрим ваш случай:
+

use tempdb;
go

create table tmp (Id int not null primary key, dt datetime not null);
create index ix_tmp_dt on tmp(dt);
go

insert into tmp (Id, dt) select [object_id], create_date from sys.objects;
update statistics tmp with all, fullscan;
go

declare @d date = (select top 1 dt from tmp where dt < dateadd(dd, -180, getdate()));

set statistics profile on;

-- non-sargable предикат, возможен только index scan (или clustered index)
select /* 1 */ * from tmp where convert(date, dt) = @d;
-- НЕ соответствует ожидаемому поведению (index scan), неожиданное проявление оптимизационных способностей, никогда на датах прежде не замеченное
-- При этом я вижу это чудо и на своих серверах. Но почему в моих примерах чуда не было? Продолжаем разбираться
/*
select * from tmp where convert(date, dt) = @d;
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
       |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@d],[@d],(62))))
       |    |--Constant Scan
       |--Index Seek(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]), SEEK:([tempdb].[dbo].[tmp].[dt] > [Expr1005] AND [tempdb].[dbo].[tmp].[dt] < [Expr1006]),  WHERE:(CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)=[@d]) ORDERED FORWARD)
*/

-- non-sargable предикат, возможен только index scan (или тупо clustered index scan, тут неважно)
alter table tmp add d as case when 1 = 1 then convert(date, dt) end;
select /* 2 */ * from tmp where convert(date, dt) = @d;
-- НЕ соответствует ожидаемому поведению (index scan), неожиданное проявление могучих оптимизационных способностей, на датах прежде не замеченное.
-- А вот на моих серверах, включая 2016CU2 тут все равно scan, что в меру логично, т.е. зависит от способностей оптимизатора выбросить лишнюю ветку в выражении. Мои вот не осилили, возможно, пришла пора последние апдейты накатывать?
/*
select * from tmp where convert(date, dt) = @d;
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
       |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@d],[@d],(62))))
       |    |--Constant Scan
       |--Index Seek(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]), SEEK:([tempdb].[dbo].[tmp].[dt] > [Expr1005] AND [tempdb].[dbo].[tmp].[dt] < [Expr1006]),  WHERE:(CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)=[@d]) ORDERED FORWARD)*/

-- non-sargable предикат, возможен только index scan (или тупо clustered index scan)
alter table tmp drop column d;
alter table tmp add d as convert(date, dt);
select /* 3 */ * from tmp where convert(date, dt) = @d;
-- вроде и соответствует ожидаемому поведению (index scan), но если сумел в п.2 соптимизировать через диапазон более высокого типа, что мешало сделать то же и тут?
/*
select * from tmp where convert(date, dt) = @d;
  |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=[tempdb].[dbo].[tmp].[d]))
       |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)))
            |--Index Scan(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]),  WHERE:(CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)=[@d]))
*/

-- предикат предполагает возможность использования индекса ix_tmp_d при высокой селективности
create index ix_tmp_d on tmp(d);
select /* 4 */ * from tmp where convert(date, dt) = @d;
-- НЕ соответствует ожидаемому поведению (index scan, ожидается index seek по ix_tmp_d); если раньше был так могуч, что ж здесь-то затупил в классически индексном случае?
/*
select * from tmp where convert(date, dt) = @d;
  |--Filter(WHERE:([tempdb].[dbo].[tmp].[d]=[@d]))
       |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=[tempdb].[dbo].[tmp].[d]))
            |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=CONVERT(date,[tempdb].[dbo].[tmp].[dt],0)))
                 |--Index Scan(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_dt]))
*/

-- м.б., из-за отсутствующей на новом индексе статистики? нет, все равно скан. Почему?
update statistics tmp with all, fullscan;
select /* 4.1 */ * from tmp where convert(date, dt) = @d;
-- НЕ соответствует ожидаемому поведению (index scan, ожидается index seek по ix_tmp_d)

-- Но прибить гвоздями-то хоть сможем?
select /* 4.2 */ * from tmp with(forceseek) where convert(date, dt) = @d;
-- Наполовину соответствует ожидаемому поведению (index seek по ix_tmp_d)
-- Ну да, прибили, но стоимость оптимизатор оценил в разы выше, чем скан: за каким-то чертом сервер лезет еще в кластерный. Зачем? В плане видно, что за d. Но он же даже не persisted! Чем дальше, чем бредятина толще?

-- ну тут уж все столбцы в индексе, не использовать его уже совсем нельзя
create index ix_tmp_d on tmp(d) include (dt) with (drop_existing = on);
select /* 5 */ * from tmp where convert(date, dt) = @d;
-- Соответствует ожидаемому поведению (index seek по ix_tmp_d)
/*
select * from tmp where convert(date, dt) = @d;
  |--Compute Scalar(DEFINE:([tempdb].[dbo].[tmp].[d]=[tempdb].[dbo].[tmp].[d]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[tmp].[ix_tmp_d]), SEEK:([tempdb].[dbo].[tmp].[d]=[@d]) ORDERED FORWARD)
*/
go

set statistics profile off;
go

drop table tmp;
go
--select @@version


В п.5 индекс подхватился без всяких хинтов. Но что мешало сделать то же выше? Непонимание оптимизатора, что вычисляемый столбец не должен учитываться при учете "покрываемости" индекса?

А ведь это объясняет все чудеса выше, начиная с абсолютно ненужного залезания в кластерный индекс, когда вычисляемый столбец уже есть естественным образом (вычисляемым, мда). И, внимание, это же и объясняет, что в моем примере ни один сервер так и не смог применить range-оптимизацию, хорошо знакомую по другим типам данных, но крайне редко виданную (мною до сих пор - так вообще ни разу) на датах: в моем примере таблица сразу создавалась с вычисляемым столбцом.

Проверим предположение: в исходной таблице при создании укажем один столбец любого типа, и точно - даже первый запрос выродился в скан. Видимо, эта оптимизация в дикой природе на датах не встречается просто из-за недопиленности известного трюка с диапазоном применительно к этим типам? Надо будет проверить на достаточно широких таблицах, где выгода от seek'а плюс лукапа точно превысит скан индекса.
27 июн 17, 00:48    [20592430]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
felix_ff
В плане того что вычисляемый столбец должен создаваться с дополнительным выражением?
Не понял вас.
Это просто пример, демонстрирующий разницу в поведении, когда выражение в предикате совпадает с выражением в вычисляемом столбце и когда не совпадает.
27 июн 17, 01:09    [20592450]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
wadegwadeg
В п.5 индекс подхватился без всяких хинтов. Но что мешало сделать то же выше?
То, что скан оказался дешевле, чем сик+лукап (сравните оценки).
wadegwadeg
А ведь это объясняет все чудеса выше
Чудес не бывает.
Предполагаю, что если выражение в предикате совпадает с выражением в вычисляемом столбце, считается, что в предикате используется вычисляемый столбец. Со всеми вытекающими.
27 июн 17, 01:13    [20592454]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
invm,

я в плане того что: чет не догнал оно с какого рожна вот тут index seek использует:
alter table tmp add d as case when 1 = 1 then convert(date, dt) end;
select * from tmp where convert(date, dt) = @d;


а вот тут нет:
alter table tmp drop column d;
alter table tmp add d as convert(date, dt);
select * from tmp where convert(date, dt) = @d;


предикат одинаковый, единственное различие в выражении case для первого случая вычисляемого столбца, вот мне интересно и стало это вы игрались с дефинишном вычисляемого столбца пока seek не получили или это где то описано такое поведение?
27 июн 17, 01:25    [20592458]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
felix_ff
я в плане того что: чет не догнал оно с какого рожна вот тут index seek использует:
Начиная с 2008-го сервера для некоторых типов данных есть такая оптимизация: предикат cast(column as type) = @value преобразуется в column >= @boundary1 and column < @boundary2, что делает его саргабельным.
Например: datetime -> date, float -> int и т.п.
felix_ff
это вы игрались с дефинишном вычисляемого столбца пока seek не получили или это где то описано такое поведение?
Нет, не игрался. А сознательно делал выражение в вычисляемом столбце отличным от выражения в предикате.
Использование индекса по вычисляемому столбцу, если выражение в предикате совпадает с выражением вычисляемого столбца точно было описано. Но, к сожалению, ссылку пока найти не могу.
А описания невозможности применить вышеописанную оптимизацию, если выражение в предикате и в вычисляемом столбце совпадают, не встречал.
27 июн 17, 11:39    [20593337]     Ответить | Цитировать Сообщить модератору
 Re: Индекс по computed column как индекс по выражению  [new]
wadegwadeg
Member

Откуда:
Сообщений: 36
invm
wadegwadeg
В п.5 индекс подхватился без всяких хинтов. Но что мешало сделать то же выше?
То, что скан оказался дешевле, чем сик+лукап (сравните оценки).
wadegwadeg
А ведь это объясняет все чудеса выше
Чудес не бывает.
Предполагаю, что если выражение в предикате совпадает с выражением в вычисляемом столбце, считается, что в предикате используется вычисляемый столбец. Со всеми вытекающими.
Чудес у оптимизатора до сих пор хоть жопой ешь. А про оценки я и сам написал - см. цитируемую часть. И там же отметил, что никакого лукапа там вообще не требуется, все отлично тащится прямо из индекса, в т.ч. вычисляемый неперсистный столбец.
27 июн 17, 12:25    [20593548]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить