Как я могу ускорить этот индексированный просмотр?

У меня простой индексированный вид. Когда я спрашиваю об этом, он довольно медленный. Сначала я покажу вам схему и индексы. Тогда простые запросы. Наконец, план запроса screnie.

Обновление: подтверждение решения в нижней части этого сообщения.

Схема

Это выглядит так: -

CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING AS
    SELECT PostId, PostTypeId, 
        [dbo].[ToUriCleanText]([Subject]) AS CleanedSubject
    FROM [dbo].[Posts]

My udf ToUriCleanText просто заменяет различные символы пустым символом. Например. заменяет все символы "#" на "".

Затем я добавил два индекса: -

Индексы

Индекс первичного ключа (т.е. кластерный индекс)

CREATE UNIQUE CLUSTERED INDEX [PK_PostCleanSubjectView] ON 
    [dbo].[PostsCleanSubjectView] 
(
    [PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

И некластеризованный индекс

CREATE NONCLUSTERED INDEX [IX_PostCleanSubjectView_PostTypeId_Subject] ON 
    [dbo].[PostsCleanSubjectView] 
(
    [CleanedSubject] ASC,
    [PostTypeId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Теперь это имеет около 25 тыс. строк. Ничего особенного.

Когда я выполняю следующие запросы, они берут около 4 нечетных секунд. WTF? Это должно быть.. в основном мгновенно!

Запрос 1

SELECT a.PostId
FROM PostsCleanSubjectView a 
WHERE a.CleanedSubject = 'Just-out-of-town'

Запрос 2 (добавлено другое, где пункт пункта)

SELECT a.PostId
FROM PostsCleanSubjectView a 
WHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1

Чем я ошибся? Является ли UDF завинчиванием? Я думал, что, поскольку у меня есть указатель этого взгляда, это будет реализовано. Таким образом, ему не нужно было бы вычислять этот строковый столбец.

Вот скриншот плана запроса, если это помогает: -

Также обратите внимание на используемый им индекс? Почему он использует этот индекс?

Этот индекс...

CREATE NONCLUSTERED INDEX [IX_Posts_PostTypeId_Subject] ON [dbo].[Posts] 
(
    [PostTypeId] ASC,
    [Subject] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Итак, какие-нибудь идеи?

Обновление 1: добавлена схема для udf.

CREATE FUNCTION [dbo].[ToUriCleanText]
(
    @Subject NVARCHAR(300)
)
RETURNS NVARCHAR(350) WITH SCHEMABINDING
AS 
BEGIN
   <snip>
   // Nothing insteresting in here. 
   //Just lots of SET @foo = REPLACE(@foo, '$', ''), etc.
END

Обновление 2: Решение

Да, это потому, что я не использовал индекс в представлении и должен был вручную убедиться, что я не расширил представление. Сервер Sql Server 2008 Standard Edition. Полный ответ ниже. Здесь доказательство, WITH (NOEXPAND)

Спасибо, что помогли мне решить эту проблему:)

Ответ 1

Какая версия SQL Server? Я считаю, что только Enterprise и Developer Edition будут автоматически использовать индексированные представления, а другие поддерживают его с помощью подсказок запросов.

SELECT a.PostId
FROM PostsCleanSubjectView a WITH (NOEXPAND)
WHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1

Из подсказки запросов (Transact SQL) в MSDN:

Индексированный вид не расширяется, только если представление напрямую ссылается на часть SELECT запроса и WITH (NOEXPAND) или WITH (NOEXPAND, INDEX (index_value [,... n])).

Ответ 2

Я вижу знак @в коде запроса в вашем плане выполнения. Там задействована строковая переменная.

Сервер Sql имеет поведение NASTY, если тип строковой переменной не соответствует типу столбца строки в индексе. Sql Server будет... преобразовать весь столбец в этот тип, выполнить быстрый поиск, а затем выбросить преобразованный индекс, чтобы он мог сделать все это снова следующим запросом.

Саймон понял это, но здесь более полезная деталь: http://msdn.microsoft.com/en-us/library/ms187373.aspx

Если запрос содержит ссылки на столбцы, которые присутствуют как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления обеспечивает лучший метод для выполнения запроса, оптимизатор запросов использует индекс в представлении, Эта функция называется индексированным представлением соответствия и поддерживается только в выпусках SQL Server Enterprise и Developer.

Однако, чтобы оптимизатор рассматривал индексированные представления для сопоставления или использования индексированного представления, на которое ссылается подсказка NOEXPAND, следующие параметры SET должны быть установлены в положение ON:

Итак, что происходит, это то, что индексированное представление соответствия не работает. Убедитесь, что вы используете выпуски Enterprise или Developer Sql Server (довольно вероятно). Затем проверьте свои настройки SET в соответствии со статьей.

Ответ 3

Вы обновили статистику после создания индексов?

Ответ 4

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

Я не использовал SQL Enterprise, хотя я не получал возможность использовать индексированные представления. Предполагается ли, что индексированный вид способен индексировать детерминированные результаты UDF?

Ответ 5

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

Ответ 6

Какую пользу вы ищете с помощью индексированного представления? Невозможно правильно проиндексировать таблицу (таблицы) самостоятельно? Без хорошего обоснования вы добавляете сложность и просите оптимизатора работать с более объектами базы данных с меньшей гибкостью.

Вы оценили одну и ту же логику запросов со стандартными индексами?

Смешивание в логике UDF еще больше мутирует.

Ответ 7

Если все, что вам нужно, - это сохранить возвращаемое значение UDF, рассмотрите постоянный вычисленный столбец, а не индексированный вид.

Ответ 8

Его использование этого индекса возможно потому, что представление использует вашу функцию. Если я помню, что исправленные индексированные представления имели ограничения на использование какой-либо функции.

Попробуйте это, сделайте таблицу tmp с результатами просмотра, без индексов и попробуйте запрос, и посмотрите, сколько времени потребуется, сделайте то же самое с индексами и посмотрите, сколько времени потребуется. Выполняйте те же запросы в исходной таблице, используя вашу функцию, и посмотрите, сколько времени потребуется. У вас будет лучшая картина индекса без индекса с вашей функцией и без. Возможно, просто попробуйте проиндексированные запросы, если вам нужен ответ быстро.

Если его значительно быстрее, это, вероятно, означает, что ваша функция несовместима с индексированием вида, попробуйте изменить ее или использовать таблицу tmp-таблицы или вспомогательную таблицу с post-id, обработанным_объектом и использовать это как фильтр для получения пост- id, это может быть не самое изящное решение, но обычно дисковое пространство является не-проблемой и временем выполнения запроса критическим.