Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
гр к
Guest
Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения подстроки в строке?
Т.е., что лучше написать инструкции CASE
when f1 LIKE ('%обр%') then 1
else 0

или ч-з строковую ф-ю типа
when CHARINDEX('обр',f1) then 1
else 0
19 июн 14, 11:05    [16187663]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
гр к
Guest
гр к
или ч-з строковую ф-ю типа
when CHARINDEX('обр',f1)>0 then 1
else 0

Поправил
19 июн 14, 11:07    [16187674]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
гр к,

поскольку индекс по полю f1 нельзя использовать ни там ни там,
то всё зависит от внутренней реализации LIKE и CHARINDEX, а сие нам неведомо...
Но разница небольшая, если и есть.
19 июн 14, 11:10    [16187689]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Однако, CHARINDEX не просто показывает вхождение подстроки в строки,
а и определяет место, где оно обнаружено.
Можно ли тут сравнивать в связи с этим?
19 июн 14, 11:13    [16187711]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
msLex
Member

Откуда:
Сообщений: 8927
iap
Однако, CHARINDEX не просто показывает вхождение подстроки в строки,
а и определяет место, где оно обнаружено.
Можно ли тут сравнивать в связи с этим?

Я бы уточнил, самое раннее место его обнаружения, что чуть "выкручивает руки" этому оператору.
Like (чисто теоретически) может разбить огромную строку на N и искать в параллель и при нахождении в одном из поток остановить работу остальных. CHARINDEX-у так делать нельзя.

хотя, я все равно согласен с вами
iap
Но разница небольшая, если и есть.
19 июн 14, 11:24    [16187789]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
msLex
iap
Однако, CHARINDEX не просто показывает вхождение подстроки в строки,
а и определяет место, где оно обнаружено.
Можно ли тут сравнивать в связи с этим?

Я бы уточнил, самое раннее место его обнаружения, что чуть "выкручивает руки" этому оператору.
Like (чисто теоретически) может разбить огромную строку на N и искать в параллель и при нахождении в одном из поток остановить работу остальных. CHARINDEX-у так делать нельзя.

При определенных входных данных этот подход приведет даже к чисто алгоритмическому оверхеду (без учета расходов на переключение потоков).
19 июн 14, 11:56    [16188041]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8584
Изучение планов показывает, что LIKE просматривает индекс и выполняет поиск ключа по полю поиска и производит меньше чтений, charindex же выполняет просмотр кластерного индекса по умолчанию и выполняет немного быстрее.

Однако, если использовать хинт для LIKE на использование кластерного индекса, то получилось в 4 раза быстрее и в плане появилось параллельное выполнение.
19 июн 14, 12:14    [16188181]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Владислав Колосов
Изучение планов показывает, что LIKE просматривает индекс и выполняет поиск ключа по полю поиска и производит меньше чтений, charindex же выполняет просмотр кластерного индекса по умолчанию и выполняет немного быстрее.

Однако, если использовать хинт для LIKE на использование кластерного индекса, то получилось в 4 раза быстрее и в плане появилось параллельное выполнение.


а вы не путаете это
f1 LIKE ('%обр%')

с этим?
f1 LIKE ('обр%')
19 июн 14, 12:41    [16188421]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8584
Я при тестировании использовал like '%pattern%'.
19 июн 14, 13:03    [16188640]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
гр к
Guest
Т.к. возможности повесить на f1 индекс все равно нет, оставлю вар-т с Like.
Всем спасибо.
19 июн 14, 13:22    [16188811]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
Владислав Колосов
Я при тестировании использовал like '%pattern%'.
И из-за этого LIKE подхватился индекс по pattern, да?
А не сказки ли это?
19 июн 14, 13:28    [16188868]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
iap
Владислав Колосов
Я при тестировании использовал like '%pattern%'.
И из-за этого LIKE подхватился индекс по pattern, да?
А не сказки ли это?


Вот и я о том же подумал,
но может у оптимизатора вообще других вариантов небыло кроме как выбрать этот индекс,
мы ж не знаем какой запрос он делал и какой индекс у него там,
может с where value is not null и в селекте только value, такой вариант может быть быстрее кластерного скана.
19 июн 14, 13:38    [16188952]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8584
Таблица вида:

ID int Primary key, NAME varhcar(100), other fields

индекс ix_name (NAME).

Оптимизатор считает, что ix_name использовать выгоднее, т.к. при этом производится меньшее количество чтений (6к против 28к).
Тем не менее, при "линейном" чтении charindex () оказывается слегка быстрее.
Зато по кластерному, при использовании хинта, происходит распараллеливание (у меня на 4 потока, судя по количеству выполнений) и like побеждает.
Несмотря на меньшее количество чтений индекса выигрыша не дает, т.к. поиск подстроки намного более затратная операция.
19 июн 14, 13:38    [16188958]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
msLex
Member

Откуда:
Сообщений: 8927
iap
Владислав Колосов
Я при тестировании использовал like '%pattern%'.
И из-за этого LIKE подхватился индекс по pattern, да?
А не сказки ли это?

index scan это хрень, он (SQL2012) научился по like '%' + @p + '%' делать index SEEK.

if object_id('tempdb..#t')  is not null 
	drop table #t
select 
	o.object_id
	, o.name
	, o.create_date
into #t 
from sys.objects o

create index i on #t (name)

declare @p varchar(10) = N't'
set statistics profile on

select name 
from #t
where name like '%' + @p + '%' 

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
2208	1	select name 
from #t
where name like '%' + @p + '%'	1	1	0	NULL	NULL	NULL	NULL	151,8403	NULL	NULL	NULL	0,004189765	NULL	NULL	SELECT	0	NULL
2208	1	  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [Expr1009], [Expr1010]))	1	2	1	Nested Loops	Inner Join	OUTER REFERENCES:([Expr1008], [Expr1009], [Expr1010])	NULL	151,8403	0,003865741	0,0003240243	69	0,004189765	[tempdb].[dbo].[#t].[name]	NULL	PLAN_ROW	0	1
0	0	       |--Compute Scalar(DEFINE:([Expr1008]=LikeRangeStart(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)), [Expr1009]=LikeRangeEnd(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)), [Expr1010]=LikeRangeInfo(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0))))	1	3	2	Compute Scalar	Compute Scalar	DEFINE:([Expr1008]=LikeRangeStart(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)), [Expr1009]=LikeRangeEnd(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)), [Expr1010]=LikeRangeInfo(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)))	[Expr1008]=LikeRangeStart(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)), [Expr1009]=LikeRangeEnd(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)), [Expr1010]=LikeRangeInfo(CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0))	1	0	0	41	0	[Expr1008], [Expr1009], [Expr1010]	NULL	PLAN_ROW	0	1
1	1	       |    |--Constant Scan	1	4	3	Constant Scan	Constant Scan	NULL	NULL	1	0	0	0	0	NULL	NULL	PLAN_ROW	0	1
2208	1	       |--Index Seek(OBJECT:([tempdb].[dbo].[#t]), SEEK:([tempdb].[dbo].[#t].[name] > [Expr1008] AND [tempdb].[dbo].[#t].[name] < [Expr1009]),  WHERE:([tempdb].[dbo].[#t].[name] like CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)) ORDERED FORWARD)	1	12	2	Index Seek	Index Seek	OBJECT:([tempdb].[dbo].[#t]), SEEK:([tempdb].[dbo].[#t].[name] > [Expr1008] AND [tempdb].[dbo].[#t].[name] < [Expr1009]),  WHERE:([tempdb].[dbo].[#t].[name] like CONVERT_IMPLICIT(nvarchar(12),'%'+[@p]+'%',0)) ORDERED FORWARD	[tempdb].[dbo].[#t].[name]	151,8403	0,003865741	0,0003240243	69	0,004189765	[tempdb].[dbo].[#t].[name]	NULL	PLAN_ROW	0	1


вот это "LikeRange*" что такое?
19 июн 14, 13:39    [16188967]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
invm
Member

Откуда: Москва
Сообщений: 9724
iap
А не сказки ли это?
Не сказки. Зависит от того, как оптимизатор оценит селективность такого предиката.
19 июн 14, 13:54    [16189119]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли разница в быстродействии LIKE или строковых ф-й для нахожения вхождения  [new]
msLex
Member

Откуда:
Сообщений: 8927
нашел ответ на свой вопрос у Paul White

если вкратце

На момент построения плана запроса оптимизатор не знает, что храниться в переменой (не путать с параметрами процедур), по которой идет like, и раньше (до 2008R2) он заведомо сваливался в скан.
Начиная с 2008 (R2?) в сам план добавилось вычисление диапазона для seek (как раз функции LikeRange*), если используется строка поиска '%p%', то это будет "фиктивный" seek с минимального по максимальное значение.
19 июн 14, 13:55    [16189120]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить