Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как ускорить like '%123'? / 25 сообщений из 26, страница 1 из 2
  1  все
11.01.2022, 20:32
    #40125896
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
Собственно, сабж.
Добавить вычисляемый столбец reverse([field]), построить по нему индекс и искать по нему [reverse_field] like reverse(@param) + '%' ?
Или есть еще варианты?
MSSQLSERVER2019.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 21:13
    #40125903
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster,

хранить в бинарном виде, например или использовать бинарный порядок сортировки для столбца.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 21:47
    #40125906
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
Добавить вычисляемый столбец reverse([field]), построить по нему индекс и искать по нему [reverse_field] like reverse(@param) + '%' ?
Да, отличный вариант.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 22:11
    #40125909
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
Дорого :-(. В таблице более 200 млн. записей.
Может, есть вариант сопоставить "хвосту" 1-2 байтовый хэш, и соответственно, искать внутри хэша?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 22:56
    #40125914
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster,

ну как, если искать исключительно по хвосту, то отцы-основатели говорят, что необходимо создать таблицу или таблицы с хранением хвостов разной длины, какая потребуется. Вычисляемый хранимый столбец - это уже варианты на тему. Но отдельные таблицы имеют преимущества, как мне кажется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2022, 23:54
    #40125923
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster , а шаблон поиска всегда трёхсимвольный? Ибо если это так, или если существует некий лимит длины - необязательно реверсить всё поле.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 09:55
    #40125945
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
Дорого :-(. В таблице более 200 млн. записей.
Может, есть вариант сопоставить "хвосту" 1-2 байтовый хэш, и соответственно, искать внутри хэша?
Однобайтовый хэш будет возвращать миллион записей из 200 млн, двухбайтовый соответственно 3000
Тут уж вам решать, что правильней, пожертвовать апдэйтами и диском ради скорости чтения, или наоборот. Можно и покрывающий индекс делать, и держать индексы в несколько раз больше размера таблицы, зависит от условий.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 11:29
    #40125966
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
Дорого :-(. В таблице более 200 млн. записей.
Может, есть вариант сопоставить "хвосту" 1-2 байтовый хэш, и соответственно, искать внутри хэша?
Дорого вычислять REVERSE или я неправильно понял?
Если вычисляемое поле PERSISTED, то вычисляемые значения сохраняются в таблице.
Таким образом, время на вычисление тратится в момент заполнения таблицы,
а не во время выполнения LIKE. Я так думаю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 17:14
    #40126069
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
iap
uaggster
Дорого :-(. В таблице более 200 млн. записей.
Может, есть вариант сопоставить "хвосту" 1-2 байтовый хэш, и соответственно, искать внутри хэша?
Дорого вычислять REVERSE или я неправильно понял?
Если вычисляемое поле PERSISTED, то вычисляемые значения сохраняются в таблице.
Таким образом, время на вычисление тратится в момент заполнения таблицы,
а не во время выполнения LIKE. Я так думаю.

Поциент хочет сэкономить?

1. Реверсируем столбец навсегда (по-месту).
2. Строим индекс.
3. Щастье возможно.
4. Ну, а когда (и если) понадобится "прямая последовательность" - реверсируем при выборке.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 20:35
    #40126116
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
iap
uaggster
Дорого :-(. В таблице более 200 млн. записей.
Может, есть вариант сопоставить "хвосту" 1-2 байтовый хэш, и соответственно, искать внутри хэша?
Дорого вычислять REVERSE или я неправильно понял?
Если вычисляемое поле PERSISTED, то вычисляемые значения сохраняются в таблице.
Таким образом, время на вычисление тратится в момент заполнения таблицы,
а не во время выполнения LIKE. Я так думаю.

Дык если по нему построить индекс, он и станет PERSISTED. По крайней мере рассчитанные значения сохранятся в индексе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 20:45
    #40126122
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
iap
пропущено...
Дорого вычислять REVERSE или я неправильно понял?
Если вычисляемое поле PERSISTED, то вычисляемые значения сохраняются в таблице.
Таким образом, время на вычисление тратится в момент заполнения таблицы,
а не во время выполнения LIKE. Я так думаю.

Дык если по нему построить индекс, он и станет PERSISTED. По крайней мере рассчитанные значения сохранятся в индексе.
А я и не спорю. Просто обращаю внимание на то, что в момент применения LIKE REVERSE уже хранится вычисленный.
Поэтому проблемы с REVERSE не вижу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 21:39
    #40126134
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
Akina
uaggster , а шаблон поиска всегда трёхсимвольный? Ибо если это так, или если существует некий лимит длины - необязательно реверсить всё поле.

Да нет, совсем наоборот! Шаблон поиска как раз произвольный.
Поле varchar(1000), заполнено некими номерами, причем 3..9 хвостовых символов имеют содержательное значение (там всякие отделы/годы закодированы).
Поле, в среднем, заполнено на 10%. В таблице 0,5 млрд. записей (когда я говорил про 200, точнее, про 180 млн., это, оказывается, только активные записи были. Там еще, оказывается, погашеные есть, их в 2 раза больше, они в соседней таблице, искать надо везде).
Номера неуникальны, но слабоповторяемы, процент дублей по номеру примерно 15%., причем максимальное количество дублей около 100, т.е. в основном номеров или 1, или, иногда 2, или крайне редко - десяток. Максимум повторов, как я сказал, в районе 100 входов. Причем есть определенная уверенность, что такое поведение - сохранится.
Цель - быстро искать по шаблону %3..9 символов длиной.
Причем это только один из вариантов поиска, еще ищут по полному совпадению и по началу n-символов%, так что индекс по номеру - есть, некластерный.
Иметь такой же, по отреверсированному полю - получается затратно, думаю, да и бессмысленно.
Думаю, сопоставить последним 9 символам какой то двубайтовый хэш, и построить индекс по нему.
Тем более, что число различных 9-ти символьных комбинаций с хвоста - всего то около 150 тысяч.

Вопрос, как такой хэш построить?
Думаю CRC16 должен хорошо подойти.
Никто не знает, как поизящнее посчитать, а лучше одной строкой?
Ну, или что-нибудь типа того...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 21:47
    #40126137
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster,

Какая вам польза от хеша последних 9 симовлов, если поиск идет не строго по 9 последним символам? Что вы будете делать с этим хешом при поиске, скажем, "%123"?

Ограничте reverse поле 3-4 символами для начального отсечения основных подходящих вариантов, а из них обычным (не индексированным) поиском отфильтруите нужные по полной строке.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 21:51
    #40126141
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
Ну и искать, соответственно как:
Where KEY_CRC16 = CRC16(@key) and key like '%' + @key
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 21:55
    #40126145
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
msLex
uaggster,

Какая вам польза от хеша последних 9 симовлов, если поиск идет не строго по 9 последним символам? Что вы будете делать с этим хешом при поиске, скажем, "%123"?

Ограничте reverse поле 3-4 символами для начального отсечения основных подходящих вариантов, а из них обычным (не индексированным) поиском отфильтруите нужные по полной строке.

Черт, я знал, что где то дырка.
:-)
А по трем символам строить бессмысленно, там есть комбинации, которым соответствует миллионов 20 входов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 21:55
    #40126146
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
Ну и искать, соответственно как:
Where KEY_CRC16 = CRC16(@key) and key like '%' + @key


Как вы будете сравнивать crc от 9 символов с crc от 3-х?
Хеши могут использоваться только для точного поиска.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 21:57
    #40126148
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
msLex
uaggster,

Какая вам польза от хеша последних 9 симовлов, если поиск идет не строго по 9 последним символам? Что вы будете делать с этим хешом при поиске, скажем, "%123"?

Ограничте reverse поле 3-4 символами для начального отсечения основных подходящих вариантов, а из них обычным (не индексированным) поиском отфильтруите нужные по полной строке.

Черт, я знал, что где то дырка.
:-)
А по трем символам строить бессмысленно, там есть комбинации, которым соответствует миллионов 20 входов.


Поэкспериментируйте с длиной reverse поля для нахождения компромисса между размером и селективностью.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 22:24
    #40126152
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
msLex
uaggster
Ну и искать, соответственно как:
Where KEY_CRC16 = CRC16(@key) and key like '%' + @key


Как вы будете сравнивать crc от 9 символов с crc от 3-х?
Хеши могут использоваться только для точного поиска.

Да понял уже :-(
Скорее всего, действительно отрежу с хвоста 8 символов, отреверсирую и сохраню как char в бинарном коллейте. И "хрен с ним, пусть тоскует"

Хотя, чисто теоретически, если подобрать такую функцию, которая монотонно росла бы от количества символов и от позиции - можно было бы по интервалу искать.
Ну т.е. если бы хеш(1) <= хеш(1яяяяяяяяя) <хеш(2) <= хеш(2я) и т.д.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 22:31
    #40126155
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
Скорее всего, действительно отрежу с хвоста 8 символов, отреверсирую и сохраню как char в бинарном коллейте. И "хрен с ним, пусть тоскует"
Да отличный вариант, и не надо париться.

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

С бинарным коллейтом я бы поосторожней, выгода от этого какая? Правильно, никакой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 22:34
    #40126156
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
Хотя, чисто теоретически, если подобрать такую функцию, которая монотонно росла бы от количества символов и от позиции - можно было бы по интервалу искать.
Ну т.е. если бы хеш(1) <= хеш(1яяяяяяяяя) <хеш(2) <= хеш(2я) и т.д.
А чего вы так хэш хотите?
Экономия места? Ну, будет вместо строки в 8 байт хэш в 4 байта, оно даст какой то выигрыш? ПРоцентов на 5 в размере?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2022, 22:37
    #40126157
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
alexeyvg,

емнип в бинарном выгода в том, что не используются для сравнения преобразования, а сравнивается "как есть". Где-то были примеры, производительность в 3-5 раз возрастает. Но точно уже не помню. В любом случае выигрыш был заметным.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2022, 07:37
    #40126195
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
uaggster
Поле varchar(1000), заполнено некими номерами, причем 3..9 хвостовых символов имеют содержательное значение (там всякие отделы/годы закодированы).
Ну какой же произвольный-то? если из этой фразы напрямую вытекает, что максимальная длина - 9 символов. Вот её и реверсить, а потом индексировать... а если часть, что к поиску - всегда цифровая, так ещё и отрезать нецифровые символы и конвертировать в INTEGER.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2022, 09:48
    #40126208
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
alexeyvg
С бинарным коллейтом я бы поосторожней, выгода от этого какая? Правильно, никакой.


В бинарном коллейте быстрее идет сравнение. Т.е. грубо при поиске обычным сканом бинарный коллейт может (и дает) прирост поизводительности раза в два. В случае с поиску по индексу пользы, конечно, будет значительно меньше. Хотя, если в поле какие либо коды, бинарный коллейт не принося ощутимых недостатков может дать немного производительности.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2022, 10:47
    #40126224
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
msLex
alexeyvg
С бинарным коллейтом я бы поосторожней, выгода от этого какая? Правильно, никакой.


В бинарном коллейте быстрее идет сравнение. Т.е. грубо при поиске обычным сканом бинарный коллейт может (и дает) прирост поизводительности раза в два. В случае с поиску по индексу пользы, конечно, будет значительно меньше. Хотя, если в поле какие либо коды, бинарный коллейт не принося ощутимых недостатков может дать немного производительности.
Это всё очевидно, но неужели скорость выборки записей по like увеличится в 2 раза даже при скане? (или тем более в 3-5, как написал Владислав)
Мне кажется, общая экономия будет труднозамеряема.
По индексу то же самое, ччто и для фуллскана, если говорить только про покрывающий индекс (с лукапами уж точно без разницы, бинарный коллейт или нет)
Владислав Колосов
Где-то были примеры, производительность в 3-5 раз возрастает.
Это наверное примеры для стоимости самой операции сравнения, типа в цикле делаем лайк переменной в переменной. Бессмысленный синтетический тест.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.01.2022, 11:09
    #40126236
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как ускорить like '%123'?
alexeyvg
Это всё очевидно, но неужели скорость выборки записей по like увеличится в 2 раза даже при скане?


Все, конечно, зависит от многих условий (например, все данные не закешированны и лежат на медленных дисках)

но в общем и целом ускорение в 2 раз и более это не миф

например на совсем тепличных условиях (когда все время выполнения это тот самый лайк) у меня на рабочем компе вот такие результаты


Код: 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.
drop table if exists dbo.test1

create table dbo.test1(f1 varchar(108) collate Cyrillic_General_100_BIN2, f2 varchar(108) collate Cyrillic_General_CI_AI)

insert dbo.test1(f1, f2)
select top 1000000 replicate(newid(), 3), replicate(newid(), 3)
from sys.objects o1
cross apply sys.objects o2
cross apply sys.objects o3




set statistics time on 

select count(*)
from dbo.test1
where
	f1 like '%ABC%'


select count(*)
from dbo.test1
where
	f2 like '%ABC%'

set statistics time off 

drop table if exists dbo.test1



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
(1 row affected)

 SQL Server Execution Times:
   CPU time = 907 ms,  elapsed time = 127 ms.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 10903 ms,  elapsed time = 1463 ms.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как ускорить like '%123'? / 25 сообщений из 26, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 263ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1683936]:
 0ms
get settings:
 7ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 164ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 9ms
total:  263ms
x
x
Закрыть


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