Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 что быстрее in или like  [new]
Badhabit
Member

Откуда:
Сообщений: 90
День добрый.
Такой вопрос. Можно ли сказать не экспериментируя что будет быстрее работать:
Есть таблица, гигов этак на 200. В ней есть текстовое поле 30 символов (символы это цифры), по этому полю сделан некластеризованный индекс.
Что будет быстрее?
select left(fld, 5)
from tbl
where left(fld, 5) in ('12345', '23456', '34567')
group by left(fld, 5)

или
select left(fld, 5)
from tbl
where fld like '12345%' or fld like '23456%' or fld like '34567%'
group by left(fld, 5)

Или без разницы?
2 авг 17, 13:57    [20694912]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Лайк быстрее обвиосли потому, что попадает в индекс. Вычисляемый предикат в индекс не попадает 100%
2 авг 17, 14:04    [20694940]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Cammomile
Лайк быстрее обвиосли потому, что попадает в индекс. Вычисляемый предикат в индекс не попадает 100%

Так ли обвиосли? Если условия суммарно покрывают большую часть записей, скан и применение функции ко всем записям может быть быстрее, чем поиск по каждому условию отдельно. Или я заблуждаюсь?
2 авг 17, 14:22    [20694989]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Badhabit
Member

Откуда:
Сообщений: 90
Cammomile
Лайк быстрее обвиосли потому, что попадает в индекс. Вычисляемый предикат в индекс не попадает 100%

Логично! Спасибо большое!
2 авг 17, 14:23    [20694991]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Minamoto
Cammomile
Лайк быстрее обвиосли потому, что попадает в индекс. Вычисляемый предикат в индекс не попадает 100%

Так ли обвиосли? Если условия суммарно покрывают большую часть записей, скан и применение функции ко всем записям может быть быстрее, чем поиск по каждому условию отдельно. Или я заблуждаюсь?


Ну вот туповатое репро:

+
create table #testtable (id int identity (1, 1), val nvarchar(36));

create nonclustered index testindex111 on #testtable (val);

insert into #testtable (val)
select	val
from (select top 1000000 cast(newid() as nvarchar(36)) as val 
		from master..spt_values as sv 
				cross join master..spt_values as sv2
		) as t1 
where t1.val > 'A'

set statistics time on

select left(val, 1)
from #testtable
where left(val, 1) in ('A', 'B', 'C')
group by left(val, 1)


select left(val, 1)
from #testtable
where val like 'A%' or val like 'B%' or val like 'C%'
group by left(val, 1)

set statistics time off


План говорит, что первый вариант стоит 55%, а второй - 45%, но при этом реальные результаты:

 SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 53 ms.

SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 116 ms.
2 авг 17, 14:31    [20695023]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А ты гроупбай убери и посмотри.
2 авг 17, 15:37    [20695258]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
SQL Server Execution Times:
CPU time = 438 ms, elapsed time = 422 ms.

(строк обработано: 187966)

SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 369 ms.
2 авг 17, 15:39    [20695262]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20484
Minamoto
вот туповатое репро
А где столь же туповатое репро, но с учётом
Minamoto
Если условия суммарно покрывают большую часть записей
?
2 авг 17, 15:43    [20695281]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
rnk
Member

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

Ещё советую перед индексированием сделать по полю бинарный collation. Поиск ускорится раза в полтора.
2 авг 17, 15:46    [20695291]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Cammomile
А ты гроупбай убери и посмотри.
Зачем? Вопрос вроде задан про запросы с group by
2 авг 17, 15:58    [20695349]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
лолл
Member

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

а если после индексирования?)
2 авг 17, 15:58    [20695350]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Akina
Minamoto
вот туповатое репро
А где столь же туповатое репро, но с учётом
Minamoto
Если условия суммарно покрывают большую часть записей
?
Хм.
Вот оно. Результаты поменялись. Тогда я не очень понимаю причины.

+
create table #testtable (id int identity (1, 1), val nvarchar(36));

create nonclustered index testindex111 on #testtable (val);


insert into #testtable (val)
select	val
from (select top 1000000 cast(newid() as nvarchar(36)) as val 
		from master..spt_values as sv 
				cross join master..spt_values as sv2
		) as t1 
where t1.val > 'A' and t1.val < 'E'

set nocount on
set statistics time on

select left(val, 1)
from #testtable
where left(val, 1) in ('A', 'B', 'C')
group by left(val, 1)


select left(val, 1)
from #testtable
where val like 'A%' or val like 'B%' or val like 'C%'
group by left(val, 1)

set statistics time off



 SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 85 ms.

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 76 ms.
2 авг 17, 16:03    [20695371]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
лолл
Member

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

Что вы гадаете, там на планах все видно)
2 авг 17, 16:11    [20695415]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
1
Guest
where val like 'A%' or val like 'B%' or val like 'C%'

для теста надо бы
where left(val,1) like 'A' or val like 'B' or val like 'C'
2 авг 17, 19:19    [20696032]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
лолл
Minamoto,

Что вы гадаете, там на планах все видно)
Вы детальнее можете пояснить - что вам видно на планах?

Я вижу параллелизм в первом запросе и отсутствие его во втором - за счет этого, видимо, идет выигрыш во времени выполнения - вы про это говорите?
3 авг 17, 10:11    [20697043]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Владислав Колосов
Member

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

возможно, Ваш порог ниже, чем у меня, у меня оба плана непараллельные. Тем не менее, ожидаемо - просмотр таблицы в первом и поиск по втором случае.
3 авг 17, 11:01    [20697236]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Владислав Колосов
Minamoto,

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

Это да. Непонимание у меня вызывает другая штука - почему, если подходящие варианты покрывают около 50% значений в таблице - скан получается дешевле поиска по индексу, а если варианты покрывают 75% - скан или дороже или примерно столько же стоит, сколько и поиск. Должно же быть наоборот - чем больше значений попадает под условия, тем относительно выгоднее сканирование таблицы целиком по сравнению с поиском. Или я чего то не понимаю?
3 авг 17, 11:30    [20697356]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
rnk
Member

Откуда:
Сообщений: 126
лолл
а если после индексирования?)

Тогда пройдёт переиндексирование.
3 авг 17, 13:15    [20697695]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
лолл
Member

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

Коллейшн - это не формат хранения данных, а правила их сравнения.
3 авг 17, 13:23    [20697731]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Владислав Колосов
Member

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

в персом примере CPU Time больше времени запроса, значить произошло параллельное выполнение. Во втором случае приблизительно равно, т.е. его не было.
3 авг 17, 13:34    [20697772]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
человек_ниоткуда
Guest
Minamoto
пропущено...
Ну вот туповатое репро:


Вот немного поумнее репо.
SET SHOWPLAN_TEXT OFF;
GO 

begin try
	create table #testtable (id int identity (1, 1), val nvarchar(36));

	-- не тут индекс делай

	insert into #testtable (val)
	select	val
	from (select top 1000000 cast(newid() as nvarchar(36)) as val 
			from master..spt_values as sv 
					cross join master..spt_values as sv2
			) as t1 
	where t1.val > 'A';

	-- и это ВАЖНО!!!
	create nonclustered index testindex111 on #testtable (val);
end try
begin catch
end catch;
;
GO
SET SHOWPLAN_TEXT OFF; /*ON*/
GO
set statistics time ON; /*OFF*/

select left(val, 1)
from #testtable
where left(val, 1) in ('A', 'B', 'C')
group by left(val, 1)


select left(val, 1)
from #testtable
where val like 'A%' or val like 'B%' or val like 'C%'
group by left(val, 1)

set statistics time OFF;


А если план помореть:

================================================
|--Hash Match(Aggregate, HASH:([Expr1003]), RESIDUAL:([Expr1003] = [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1003]=substring([tempdb].[dbo].[#testtable].[val],(1),(1))))
|--Table Scan(OBJECT:([tempdb].[dbo].[#testtable]), WHERE:(substring([tempdb].[dbo].[#testtable].[val],(1),(1))=N'C' OR substring([tempdb].[dbo].[#testtable].[val],(1),(1))=N'B' OR substring([tempdb].[dbo].[#testtable].[val],(1),(1))=N'A')
--------------------------------------------------------------------------------------
|--Hash Match(Aggregate, HASH:([Expr1003]), RESIDUAL:([Expr1003] = [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1003]=substring([tempdb].[dbo].[#testtable].[val],(1),(1))))
|--Index Seek(OBJECT:([tempdb].[dbo].[#testtable]), SEEK:([tempdb].[dbo].[#testtable].[val] >= N'A' AND [tempdb].[dbo].[#testtable].[val] < N'B' OR [tempdb].[dbo].[#testtable].[val] >= N'B' AND [tempdb].[dbo].[#testtable].[val] < N'C' OR [tempdb].[dbo].[#testtable].[val] >= N'C' AND [tempdb].[dbo].[#testtable].[val] < N'D'), WHERE:([tempdb].[dbo].[#testtable].[val] like N'A%' OR [tempdb].[dbo].[#testtable].[val] like N'B%' OR [tempdb].[dbo].[#testtable].[val] like N'C%') ORDERED FORWARD)
================================================

А вот если сделать "... select top 10 cast(newid() as nvarchar(36)) as val ..." то уже индекс будет и в первом случае, но только сканиться.

================================================
|--Sort(DISTINCT ORDER BY:([Expr1003] ASC))
|--Compute Scalar(DEFINE:([Expr1003]=substring([tempdb].[dbo].[#testtable].[val],(1),(1))))
|--Index Scan(OBJECT:([tempdb].[dbo].[#testtable]), WHERE:(substring([tempdb].[dbo].[#testtable].[val],(1),(1))=N'C' OR substring([tempdb].[dbo].[#testtable].[val],(1),(1))=N'B' OR substring([tempdb].[dbo].[#testtable].[val],(1),(1))=N'A'))
================================================

Ещё имейте ввиду, что в процедуре "... where [value] like @param" и "... where [value] like @param+'%'" тоже по разному обрабатываются оптимизатором.
3 авг 17, 13:35    [20697776]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
человек_ниоткуда, ну и по-прежнему left быстрее like. На этом наборе - за счет параллелизма - больше чем в два раза быстрее. Увеличиваю в 10 раз объем строк - и like обгоняет left за счет того, что и в нем включается параллелизм.

В общем, буду свои результаты списывать на то, что когда более тяжелый скан по оценке получает параллельный план, а более легкий поиск - не получает - за счет параллельного выполнения скан становится быстрее, во всех остальных случаях поиск по индексу даст лучшие результаты.
3 авг 17, 14:30    [20697962]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
человек_ниоткуда
Guest
Minamoto
человек_ниоткуда, ну и по-прежнему left быстрее like. ...


Суть в том, что надо смотреть на реальных данных и реальных запросах.
Вообще в таких случаях есть чёткий архитектурный паттерн: calculated field в котором будет substring([value] , 1, 1) . В настоящем случае у тебя будет работать всё как будто в where прописан left, а если понадобится это оптимизировать, то сделаешь persisted поле и в индекс загонишь.
3 авг 17, 16:51    [20698618]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
rnk
Member

Откуда:
Сообщений: 126
лолл
Коллейшн - это не формат хранения данных, а правила их сравнения.

Я как бы немножечко догадываюсь. Но на скорость поиска эти правила влияют. В частности, если заведомо известно, что в поле будут только цифры, то сравнение можно проводить без преобразования регистра и без учёта диакритики. Что существенно ускоряет поиск.
3 авг 17, 17:35    [20698752]     Ответить | Цитировать Сообщить модератору
 Re: что быстрее in или like  [new]
invm
Member

Откуда: Москва
Сообщений: 9270
человек_ниоткуда
В настоящем случае у тебя будет работать всё как будто в where прописан left, а если понадобится это оптимизировать, то сделаешь persisted поле и в индекс загонишь.
Не обязательно менять предлоржение where и делать вычисляемый столбец персистентным
+
create table #testtable (id int identity (1, 1), val nvarchar(36), l1 as left(val, 1));
go

insert into #testtable (val)
select	val
from (select top (10000) cast(newid() as nvarchar(36)) as val 
		from master..spt_values as sv 
				cross join master..spt_values as sv2
		) as t1 
where t1.val > 'A' and t1.val < 'D';

create nonclustered index testindex111 on #testtable (l1);
go

set showplan_text on
go

select count(*)
from #testtable
where left(val, 1) = 'A'

/*
  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1006],0)))
       |--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
            |--Index Seek(OBJECT:([tempdb].[dbo].[#testtable]), SEEK:([tempdb].[dbo].[#testtable].[l1]=N'A') ORDERED FORWARD)
*/
go

set showplan_text off
go

drop table #testtable;
go
3 авг 17, 18:28    [20698889]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить