SQL Server - индекс в вычисленном столбце?
Я присоединяюсь к таблице десятков разных раз, и каждый раз я присоединяюсь (или фильтрую) на основе результатов СУБСТРИРОВАНИЯ одного из столбцов (это строка, но слева - с нулями и Меня не волнуют последние четыре цифры). В результате, несмотря на то, что этот столбец проиндексирован, и мой запрос будет использовать индекс, он выполняет сканирование таблицы, потому что сам SUBSTRING не индексируется, поэтому SQL Server должен вычислять его для каждой строки раньше, когда он присоединяется.
Я ищу любые идеи о том, как ускорить этот процесс. В настоящее время есть представление о таблице (это "SELECT * FROM", просто чтобы дать таблице дружественное имя), и я рассматриваю возможность добавления столбца к вычисляемому представлению, а затем его индексацию. Я открыт для других предложений, хотя - любые мысли?
ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ:
Я должен был поделить это для начала. Таблица получает репликацию из нашей биллинговой системы, поэтому редактирование базовой таблицы для добавления вычисленного столбца не является вариантом. Любой расчетный столбец должен быть добавлен к представлению в таблице. Кроме того, ведущие нули не всегда являются ведущими нулями - иногда это другие данные, которые меня не интересуют. Я полагаю, что реальный вопрос: " Как я могу присоединиться к данным в середине столбца VARCHAR, также используя индекс? Полнотекстовый поиск?"
Уточнение моего примера
Я упрощаю, но по существу, допустим, что я пытаюсь найти значения в столбце со следующими значениями:
00000012345MoreStuff
00000012345Whatever
19834212345
Houses12345837443GGD
00000023456MoreStuff
Мне интересны строки, где SUBSTRING (7,5) = "12345", поэтому мне нужны строки 1-4, но не строка 5. Я предлагаю добавить столбец в мой "SELECT *", который имеет эту подстроку в нем, а затем индексирует на основе этого. Это имеет смысл?
Ответы
Ответ 1
Предполагая, что у вас есть поля в этом формате:
00Data0007
000000Data0011
0000Data0015
вы можете сделать следующее:
-
Создайте вычисленный столбец: ndata AS RIGHT(REVERSE(data), LEN(data) - 4)
Это преобразует ваши столбцы в следующее:
ataD00
ataD000000
ataD0000
-
Создайте индекс в этом столбце
-
Задайте этот запрос для поиска строки Data
:
SELECT *
FROM mytable
WHERE ndata LIKE N'ataD%'
AND SUBSTRING(ndata, LEN(N'ataD') + 1, LEN(ndata)) = REPLICATE('0', LEN(ndata) - LEN('ataD'))
Первое условие будет использовать индекс для грубой фильтрации.
Второй будет следить за тем, чтобы все ведущие символы (которые становились конечными символами в вычисленном столбце) были всего лишь нулями.
Смотрите эту запись в своем блоге для подробностей о производительности:
Обновление
Если вам просто нужен индекс на SUBSTRING
без изменения вашей схемы, создание представления является опцией.
CREATE VIEW v_substring75
WITH SCHEMABINDING
AS
SELECT s.id, s.data, SUBSTRING(data, 7, 5) AS substring75
FROM mytable
CREATE UNIQUE CLUSTERED INDEX UX_substring75_substring_id ON (substring75, id)
SELECT id, data
FROM v_substring75
WHERE substring75 = '12345'
Ответ 2
Добавьте в таблицу вычисленный столбец и создайте индекс в этом столбце.
ALTER TABLE MyTable
Add Column CodeHead As LEFT(Code,Len(Code)-4)
Затем создайте для этого индекс.
CREATE INDEX CodeHeadIdx ON MyTable.CodeHead
Ответ 3
Можете ли вы перефразировать свои критерии фильтра в терминах предложения LIKE "something%"? (Это применимо к индексу)
Ответ 4
Измените столбец на два столбца - данные, к которым вы присоединяетесь, и дополнительные 4 символа.
Использование частей столбца замедляет работу вниз, когда вы видели