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

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

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

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

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

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

Дык если по нему построить индекс, он и станет PERSISTED. По крайней мере рассчитанные значения сохранятся в индексе.
А я и не спорю. Просто обращаю внимание на то, что в момент применения LIKE REVERSE уже хранится вычисленный.
Поэтому проблемы с REVERSE не вижу.
...
Рейтинг: 0 / 0
12.01.2022, 20:45
| Ответить | Цитировать | Написать  
Как ускорить like '%123'?
    #40126134
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
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:39
| Ответить | Цитировать | Написать  
Как ускорить like '%123'?
    #40126137
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
uaggster,

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

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

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

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

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


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

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

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

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


Поэкспериментируйте с длиной reverse поля для нахождения компромисса между размером и селективностью.
...
Рейтинг: 0 / 0
12.01.2022, 21:57
| Ответить | Цитировать | Написать  
Как ускорить like '%123'?
    #40126152
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
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:24
| Ответить | Цитировать | Написать  
Как ускорить like '%123'?
    #40126155
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
uaggster
Скорее всего, действительно отрежу с хвоста 8 символов, отреверсирую и сохраню как char в бинарном коллейте. И "хрен с ним, пусть тоскует"
Да отличный вариант, и не надо париться.

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

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

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


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


В бинарном коллейте быстрее идет сравнение. Т.е. грубо при поиске обычным сканом бинарный коллейт может (и дает) прирост поизводительности раза в два. В случае с поиску по индексу пользы, конечно, будет значительно меньше. Хотя, если в поле какие либо коды, бинарный коллейт не принося ощутимых недостатков может дать немного производительности.
Это всё очевидно, но неужели скорость выборки записей по like увеличится в 2 раза даже при скане? (или тем более в 3-5, как написал Владислав)
Мне кажется, общая экономия будет труднозамеряема.
По индексу то же самое, ччто и для фуллскана, если говорить только про покрывающий индекс (с лукапами уж точно без разницы, бинарный коллейт или нет)
Владислав Колосов
Где-то были примеры, производительность в 3-5 раз возрастает.
Это наверное примеры для стоимости самой операции сравнения, типа в цикле делаем лайк переменной в переменной. Бессмысленный синтетический тест.
...
Рейтинг: 0 / 0
13.01.2022, 10:47
| Ответить | Цитировать | Написать  
Как ускорить like '%123'?
    #40126236
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
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
13.01.2022, 11:09
| Ответить | Цитировать | Написать  
25 сообщений из 26, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как ускорить like '%123'?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40126134&tid=1683936]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 182ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 70ms
get tp. blocked users:
 2ms
others: 240ms
total:  543ms
созд. / загр.: 543ms
x
x
Закрыть


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