Замедление в индексированном представлении для SQL 2005
Скажем, у меня есть очень длинная таблица (~35 миллионов строк), которая называется TimeCard и имеет всего 5 столбцов (tableID, CompanyID, UserID, ProjectID, DailyHoursWorked, entryDate). Это довольно простая таблица, в которой записываются рабочие часы сотрудников в день на проект для каждой компании.
Теперь мне нужно сгенерировать отчет, чтобы узнать общее количество рабочих часов сотрудников в месяц на проект для любой конкретной компании. Вместо того чтобы выполнять агрегацию, необходимую при запуске отчета, я хочу создать табличную структуру данных, в которой уже собраны все данные о компании / проекте / пользователе, сгруппированные по месяцам, поэтому при выполнении отчета я могу просто запросить эту структуру данных напрямую без выполнения агрегации во время выполнения, поскольку ~ 35 миллионов записей могут занять несколько минут.
Так что у меня есть 2 разных способа. Создайте дополнительную физическую таблицу с (CompanyID, UserID, ProjectID, MonthlyHoursWorked, Month) в качестве моих столбцов и просто используйте триггер в таблице TimeCard для изменения значений в дополнительной таблице. Или я могу создать индексированное представление. Поэтому я попробовал оба. Сначала я попробовал индексированное представление с помощью следующего кода:
CREATE VIEW [dbo].[vw_myView] WITH SCHEMABINDING AS
SELECT
JobID,
ProjectID,
Sum(DailyHoursWorked) AS MonthTotal,
DATEADD( Month, DATEDIFF( Month, 0, entryDate), 0 ) AS entryMonth,
CompanyID,
COUNT_BIG(*) AS Counter
FROM
dbo.TimeCard
Group By DATEADD( Month, DATEDIFF( Month, 0, entryDate ), 0 ), JobID, ProjectID, CompanyID
Go
CREATE UNIQUE CLUSTERED INDEX [IX_someIndex] ON [dbo].[vw_myView]
(
[CompanyID] ASC,
[entryMonth] ASC,
[UserID] ASC,
[ProjectID] ASC
)
Индексированное представление создано правильно и составляет в общей сложности ~5 миллионов строк.
Однако каждый раз, когда я очищаю кэш SQL и выполняю следующий запрос: * select * from vw_myView, где companyID = 1 *, это занимает почти 3 минуты. Если я выберу дополнительный маршрут таблицы, как я уже упоминал выше, с очищенным кэшем, это займет около 4 секунд.
Мои вопросы: является ли Indexed View плохим выбором для этого конкретного сценария? В частности, мне интересно знать, будет ли пересчитываться / агрегироваться все индексированное представление каждый раз, когда базовая таблица (TimeCard) изменяется или когда к ней выполняется запрос?
Спасибо!
6 ответов
Если вы не используете версию Enterprise или Developer, вам необходимо использовать with (noexpand) подсказка:
select *
from vw_myView with (noexpand)
where companyID = 1
При изменении базовых данных представление будет обновлять только строки, связанные с измененными данными, а не всю таблицу. Это может оказать неблагоприятное влияние на базу данных OLTP с высокой степенью вставок, но если использование только умеренное, не должно создавать проблем с производительностью.
Как общая рекомендация, любые модификации или обновления представления или базовых таблиц, лежащих в основе представления, должны выполняться, по возможности, пакетами, а не одноэтапными операциями. Это может уменьшить некоторые накладные расходы при обслуживании представления.
Я думаю, что вы находитесь на правильном пути с использованием индекса View. Тем не менее, вы положили индексы на таблицу, из которой вы запрашиваете, TimeCard для ваших совокупных столбцов. Вы должны сделать индекс JobID, ProjectID, entryDate, CompanyID (1 индекс). Если вы используете 1 индекс для каждого столбца, это НЕ решит ваши проблемы, потому что Запрос должен использовать все 4 индекса вместе.
Я думаю, что использование триггера будет медленным, но другим способом. Это сделает ваш запрос быстрее, но замедлит каждую вставку, в которую вы делаете TimeCard, Если вы решите использовать Триггер, я бы позаботился о том, чтобы индексировать и эту таблицу, или она может быть медленной, не медленной на 3 минуты, но все же медленной для сортировки и возврата данных.
Рассматривали ли вы разделение таблицы. Вы можете думать о комбинации списка и таблицы хэширования.
Я бы не использовал представление для этого. Я думаю, что таблица, заполненная триггером, - это путь. Но не забудьте настроить итоги для обновлений и удалений, а также вставок.
Что ж, идея индексированного представления определенно хороша, и если вы можете создать кластеризованный индекс для него, - идеально. Это должно быть быстро - намного лучше, чем 3 минуты на запрос!
С другой стороны: если эти порции информации обновляются только один раз, например, раз в месяц или раз в неделю (или даже каждую ночь), может быть лучше просто поместить их в отдельный DailyTimeCard таблица, которая регулярно заполняется / обновляется, например, пакетом служб SSIS.
Я бы не советовал использовать триггеры для постоянного обновления такой таблицы фактов - если вам действительно нужно иметь самые последние данные в каждую секунду в течение дня, тогда оставайтесь с индексированным представлением.
Но ваше индексированное представление выполняет довольно тяжелую работу - оно суммирует, группирует и так далее. Постоянно держать это в курсе, в то время как ваши основные TimeCard Таблица меняется и обновляется, что вызовет некоторую нагрузку на вашу систему - трудно сказать, сколько - но это может быть весьма заметно.
Если вы найдете способ извлечь нужную информацию - сгруппировать и суммировать один раз, а затем сохранить эти агрегированные данные в отдельной таблице фактов - у вас должны быть как быстрые, так и быстрые запросы на DailyTimeCard таблица, и остальная часть вашей системы должна быть менее обременительной, чтобы постоянно поддерживать актуальность индексированного представления.
Может быть, это не то решение, которое вы ищете, но просто подумайте об этом немного. Это может - или не может - сработать для вас!
Я не думаю, что вам нужно индексированное представление (я не говорю, что индексированное представление - плохая / хорошая идея). Я думаю, вам нужен индекс по столбцам "CompanyID" и "EntryDate". После этого вы должны использовать условие where "WHERE CompanyID = @CompanyID AND EntryDate >= @StartDate AND EntryDate <= @EndDate".
Если таблица обрабатывается первично с помощью "EntryDate", вы можете использовать кластерный индекс для столбца "EntryDate".
После этого, я думаю, оператор select будет намного быстрее, чем сейчас.