среда, 2 декабря 2009 г.

SQL Поиск по шаблону. PATINDEX

Недавно коллега с соседней команды подкинули мне интересную задачу: у вас алфавитное меню A-Z, при клике на букву в подменю должны отобразится записи, которые начинаются на выбранную букву. Заполнением подменю занимается аяксовый сервис, который дергает хранимку, которая должна вернуть данные наичнающиеся с указанной буквы(такая была архитектура). Меню строится для продуктов. Ниже пример названий продуктов.

Как видите не все так сладко. Некоторые продукты не начинаются с буквы. Допустим нам нужны продукты которые начинаются на 'А'. Должны отобразится 128, 130-135, 137, 138, 140 и тд. Я привел только те, которые видны на картинке. В базе 300 000 продуктов, а подменю должно отображатся как подменю :)
Как решить задачу ?

Решение 1:
- Добавить колонку в таблицу, например [FirstLetter]. Вызвать один раз хранимку или серверный код, не важно, который заполнит это поле. Потому можно проиндексивать поле и выгребать данные как угодно и быстро. Или если товары импортируются, делать это при импорте.

Решение 2:
- Если "Решение 1" не подходит или нет возможности его реализовать, есть другой вариант. Можно выбрать один раз все имена продуктов на сервере, закешировать, поставить SqlCacheDependency если можно и нужно. Потом в сервисе можно в сохраненной выборке перебирать данные с помощью Regex, LINQ, искать первую букву, как угодно, возможностей много. Данное решение хорошее, но держать в кешэ 300 000 записей жирно та и требует изменений в существующей архитектуре - хочется сделать минимальные усилия с максимальным результатом исходя из того, что есть.

Решение 3:
- Хотелось бы воспользоваться оператором LIKE в котором можно задать примитивный шаблон типо регулярки, но как я не крутил с LIKE, у меня ничего не получилось, но я был на правильном пути. И тогда я вспомнил что есть встроенная функция PATINDEX, которая делает тоже самое, что и LIKE, только еще и возвращает индекс совпадения этого шаблона. По скольку нам нужно найти в названии первую букву и сравнить её с заданой, то это можно написать так (например для буквы 'A'):

SELECT [Name] AS 'Product Name'
FROM Product
WHERE SUBSTRING(LOWER([Name]), PATINDEX('%[a-z]%', LOWER([Name])), 1) = 'a'

В результате получим:


Данные выбираются меньше секунды. Задача решена как и хотелось, с минимальными усилиями и максимальным успехом.
А теперь как это работает: функция PATINDEX ищет первое совпадение по указанному шаблону (с синтаксисом шаблона можно ознакомится по ссылкам). Наш шаблон говорит "искать первую попавщуюся букву в вырежении". Нашли, получили индекс этой буквы. Вырезаем эту букву функцией SUBSTRING и сравниваем с заданной.
Успехов.