Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 29 March 2024 18:51:29


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Ищите рост производительности в использовании индексных представлений SQL Server

Tim Chapman (оригинал: See performance gains by using indexed views in SQL Server)
Перевод Моисеенко С.И.

Представление - это хранимый запрос T-SQL в SQL Server. Определение представления сохраняется SQL Server, чтобы оно могло быть использовано как виртуальная таблица для упрощения запросов и создания дополнительного слоя безопасности доступа к вашим базовым таблицам; при этом оно не занимает никакого места в базе данных. Фактически, представление действительно ничего не делает до тех пор, пока Вы не выполните адресованный к нему запрос.

Индексные представления

В SQL Server 2000 и 2005 Вы имеете возможность добавлять индексы к представлениям. Но если представление - это только хранимое определение запроса в базе данных, не имеющее собственных данных, пока его не выполнить, как же Вы можете создать индекс на таком определении? Это хитрая штука.

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

Листинг А показывает пример создания индексного представления. Скрипт создает таблицу SalesHistory и загружает в нее данные.

Листинг A

CREATE TABLE SalesHistory
(    
     SaleID INT IDENTITY(1,1),
     Product VARCHAR(30),
     SaleDate SMALLDATETIME,
     SalePrice MONEY
)

DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=10000)
BEGIN
     INSERT INTO SalesHistory
     (Product, SaleDate, SalePrice)
     VALUES
     ('Computer', DATEADD(mm, @i, '3/10/2006'), DATEPART(ms, GETDATE()) + (@i + 57) )

     INSERT INTO SalesHistory
     (Product, SaleDate, SalePrice)
     VALUES
     ('BigScreen', DATEADD(mm, @i, '3/2/2006'), DATEPART(ms, GETDATE()) + (@i + 13) )

     INSERT INTO SalesHistory
     (Product, SaleDate, SalePrice)
     VALUES
     ('PoolTable', DATEADD(mm, @i, '2/11/2007'), DATEPART(ms, GETDATE()) + (@i + 29) )

     SET @i = @i + 1
END
GO

Следующий код создаст представление для суммирования данных в таблице:

CREATE VIEW vw_salesbyproduct
AS
     SELECT
         Product,
         COUNT_BIG(*) as ProductCount,
         SuM(SalePrice) as TotalSales
     FROM dbo.SalesHistory
     GROUP BY Product

На создание представления не требуется времени, т.к. это всего лишь определение запроса на T-SQL. Как только представление создано, Вы можете адресовать к нему запросы точно так же, как вы делаете это для таблицы.

SELECT Product, TotalSales, ProductCount
FROM vw_SalesByProduct
WHERE product = 'Computer'

Если Вы установите соответствующую опцию в SQL Server Management Studio или в Query Analyzer для вывода плана выполнения запроса (Execution Plan), то Вы заметите, что вышеупомянутый запрос использует сканирование таблицы для нахождения агрегатных значений для продукции 'Computer'. Сканирования таблицы (table scan) обычно выполняются в ситуациях, когда отсутствует индекс на данных, поэтому сканируется весь результирующий набор для нахождения требуемых значений.

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

SET ANSI_NULLS ON
GO

SET ANSI_PADDING ON
GO

SET ANSI_WARNINGS ON
GO

SET CONCAT_NULL_YIELDS_NULL ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET NUMERIC_ROUNDABORT OFF
GO

Теперь Вы можете создать наше представление. Для простоты я создаю совершенно новое представление.

CREATE VIEW dbo.vw_SalesByProduct_Indexed
WITH SCHEMABINDING
AS
     SELECT
         Product,
         COUNT_BIG(*) AS ProductCount,
         SUM(ISNULL(SalePrice,0)) AS TotalSales
     FROM dbo.SalesHistory
     GROUP BY Product

Скрипт ниже создает индекс на нашем представлении:

CREATE UNIQUE CLUSTERED INDEX
idx_SalesView ON vw_SalesByProduct_Indexed(Product)

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

EXECUTE sp_spaceused 'vw_SalesByProduct_Indexed'

Нижеприведенный оператор SELECT - отличается от приведенного выше только временем, в течение которого выполняется поиск в кластеризованном индексе (clustered index seek) и которое, как правило, оказывается очень быстрым.

SELECT
    Product, TotalSales, ProductCount
FROM vw_SalesByProduct_Indexed
WHERE Product = 'Computer'

Не забывайте тестировать производительность

Индексные представления великолепны, когда вы применяете их в правильных ситуациях, поскольку они могут радикально улучшить производительность запросов. Однако выигрыш в производительности за счет кластеризованного индекса приводит к необходимости поддержки со стороны механизма БД этого индекса в ответ на каждую транзакцию, затрагивающую таблицы, на которые имеются ссылки в представлении. Из-за этого компромисса создание индексного представление может и не дать выгоды для вашей системы. Лучший способ определить это - выполнить тестирование при рабочей нагрузке.

16-02-2007

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.