Повышение производительности запроса

Индексы в столбцах XML

SQL Server 2005 имеет собственный тип данных XML. Экземпляры XML в столбцах типа данных XML хранятся как большие двоичные объекты (BLOB) и могут иметь размер до 2 Гбайт на каждый экземпляр. Для запросов к XML-данным можно использовать язык XQuery, но такие запросы к столбцам с типом данных XML без индекса могут занимать много времени. Это особенно справедливо для больших экземпляров XML, поскольку SQL Server должен разбирать большие двоичные объекты, содержащие XML, в процессе выполнения рабочего цикла оценки запроса. Чтобы повысить производительность запросов к столбцам с типом данных XML, столбцы XML можно индексировать. XML-индексы делятся на две категории: первичные XML-индексы и вторичные XML-индексы.

Создание и использование первичных XML-индексов

Первый индекс, который следует создать в столбце XML - это первичный XML-индекс. При создании этого индекса SQL Server разбирает XML-содержимое и создает несколько строк данных, которые включают такую информацию, как имя элемента и атрибута, путь к корневому узлу, тип узла и значения и т. д. Благодаря этой информации SQL Server будет гораздо проще поддерживать запросы XQuery. Чтобы создать первичный XML-индекс, в базовой таблице должен существовать первичный ключ с кластеризованным индексом. Ниже приводится синтаксическая конструкция для создания первичного XML-индекса:

CREATE PRIMARY XML INDEX index_name 
  ON <object> ( xml_column_name )

Создаем первичный XML-индекс

1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
2. Введите и выполните следующие инструкции для создания таблицы, которая будет использоваться в следующих процедурах. Код этого примера можно найти среди файлов примеров под именем CreatingAndUsingPrimaryXMLIndexes.sql.

   CREATE TABLE dbo.Products( ProductID int NOT NULL, 
                Name dbo.Name NOT NULL, CatalogDescription xml NULL,
   CONSTRAINT PK_ProductModel_ProductID PRIMARY KEY CLUSTERED ( ProductID ));
   INSERT INTO dbo.Products (ProductID,Name,CatalogDescription) 
   SELECT ProductModelID, Name, CatalogDescription 
     FROM Production.ProductModel;
3. Следующая инструкция CREATE INDEX используется для создания первичного индекса в столбце описания каталога таблицы Production.ProductModel.

   CREATE PRIMARY XML INDEX PRXML_Products_CatalogDesc 
     ON dbo.Products (CatalogDescription);
4. Еще одна инструкция использует метод XQuery для извлечения XML- данных только в том случае, если в XML-документе существует указанный путь. Введите и выполните эту инструкцию и обязательно включите план выполнения запроса.

   WITH XMLNAMESPACES
     ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works /ProductModelDescription'
       AS "PD")
   SELECT ProductID, CatalogDescription
     FROM dbo.Products
     WHERE CatalogDescription.exist
       ("/PD:ProductDescription/PD:Features") = 1
5. В следующем плане выполнения видно, что SQL Server использует индекс в XML-столбце, чтобы найти нужную запись и возвратить нужную строку данных при помощи оператора Nested Loop. SQL Server затем выполняет поиск по кластеризованному индексу (метод seek) способом, который мы наблюдали для некластеризованных индексов.
   

   
   увеличить изображение
   
6. Закройте окно среды SQL Server Management Studio.

Вторичные XML-индексы

Хотя первичные XML-индексы и повышают производительность запросов XQuery вследствие того, что XML-данные разбираются, SQL Server все же должен просматривать разобранные данные, чтобы найти среди них запрашиваемые данные. Чтобы еще больше повысить производительность запроса, можно создать поверх первичного XML-индекса вторичный XML-индекс. Существует три типа вторичных XML-индексов, и каждый тип поддерживает определенные типы запросов к XML-столбцам, предоставляя функции для создания только тех типов индексов, которые требуются для определенного сценария. Вот эти три типа вторичных XML-индексов:

• Вторичный индекс Path типа данных XML.Вторичные XML-индексы, которые полезны при использовании метода .exist для определения существования указанного пути.
• Вторичный индекс Value типа данных XML.Вторичный XML-индекс, который используется при выполнении запросов на основе значений, где полный путь неизвестен или в путь включены групповые символы.
• Вторичный индекс Property типа данных XML.Вторичный XML-индекс, который используется для извлечения значений в том случае, если путь или значение неизвестны.

  Общая синтаксическая конструкция для создания вторичных индексов XML такова:

  CREATE XML INDEX index name 
    ON <object> ( xml column name ) 
    USING XML INDEX xml index name 
    FOR { VALUE | PATH | PROPERTY }

Создаем и используем вторичные XML-индексы

1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
2. Введите и выполните следующую инструкцию для создания вторичных индексов path, value и property в столбце XML CatalogDescription. Код этого примера можно найти среди файлов примеров под именем CreatingAndUsingSecondaryXMLindexes.sql.

   CREATE XML INDEX IXML Products CatalogDesc Path 
      ON dbo.Products (CatalogDescription) 
      USING XML INDEX PRXML Products CatalogDesc FOR PATH
   CREATE XML INDEX IXML_Products_CatalogDesc_Value 
     ON dbo.Products (CatalogDescription) 
     USING XML INDEX PRXML_Products_CatalogDesc FOR VALUE
   CREATE XML INDEX IXML_Products_CatalogDesc_Property 
     ON dbo.Products (CatalogDescription) 
     USING XML INDEX PRXML_Products_CatalogDesc FOR PROPERTY
3. Введите следующую инструкцию, которая использует метод XQuery, и запросите предполагаемый план выполнения, нажав комбинацию клавиш (Ctrl+L). Рассмотрим различные способы использования только что созданных индексов сервером SQL Server.

   WITH XMLNAMESPACES 
     ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
       ProductModelDescription' AS "PD") SELECT *
     FROM dbo.Products
     WHERE CatalogDescription.exist
      ("/PD:ProductDescription/@ProductModelID[.="19"]') = 1;
   WITH XMLNAMESPACES 
     ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
      ProductModelDescription' AS "PD") 
   SELECT *
     FROM dbo.Products
     WHERE CatalogDescription.exist ("//PD:*/@ProductModelID[.="19"]') = 1;
   WITH XMLNAMESPACES 
     ("http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
       ProductModelDescription' AS "PD")
   SELECT CatalogDescription.value
       ("(/PD:ProductDescription/@ProductModelID)[1]",
        "int") as PID 
     FROM dbo.Products 
     WHERE CatalogDescription.exist 
        ("/PD:ProductDescription/@ProductModelID") = 1;
4. Закройте окно среды SQL Server Management Studio.

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

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

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

В SQL Server 2005 Enterprise, Developer или Evaluation Edition индексированные представления могут ускорить выполнение запросов, которые не ссылаются на представления напрямую. Если обрабатываемый запрос включает, например, агрегат, и Оптимизатор запросов SQL Server обнаруживает индексированное представление, в которое этот агрегат уже включен, он запросит агрегат из индекса, а не станет вычислять его.

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

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

1. Создайте представление при помощи предложения SCHEMABINDING. Это представление должно удовлетворять нескольким требованиям. Например, оно может ссылаться только на базовые таблицы, которые существуют в одной базе данных. Все ссылочные функции должны быть детерминированными; функции наборов, производные таблицы и подзапросы не допустимы. Полный список требований можно найти в теме "Создание индексированных представлений" Электронной документации SQL Server 2005.
2. Создайте в этом представлении уникальный кластеризованный индекс. Уровень листовых вершин этого индекса состоит из полного результирующего набора представления.
3. При необходимости поверх кластеризованного индекса создайте не-кластеризованные индексы. Некластеризованные индексы можно создавать как обычно.

Создаем и используем индексированные представления

1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
2. Введите и выполните следующую инструкцию, чтобы создать представление, которое агрегирует итог LineTotal, сгруппированный по месяцам заказа. Код этого примера можно найти среди файлов примеров под именем CreatingAndUsingIndexedViews.sql.

   CREATE VIEW dbo.vOrderDetails
     WITH SCHEMABINDING AS
   SELECT DATEPART(yy,Orderdate) as Year,
       DATEPART(mm,Orderdate) as Month,
       SUM(LineTotal) as OrderTotal,
       COUNT_BIG(*) as LineCount 
     FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
     ON o.SalesOrderID = od.SalesOrderID 
     GROUP BY DATEPART(yy,Orderdate),
     DATEPART(mm,Orderdate)
3. Введите и выполните следующую инструкцию SELECT. На вкладке Messages (Сообщения) видно, что для выполнения этой инструкции SQL Server требуется почти 1000 считываний страниц.

   SET STATISTICS IO ON 
   SELECT Year, Month, OrderTotal
     FROM dbo.OrderDetails
     ORDER BY Year, Month 
   SET STATISTICS IO OFF
4. Введите и выполните следующую инструкцию CREATE INDEX, чтобы создать уникальный кластеризованный индекс в представлении vOrderDetails.

   CREATE UNIQUE CLUSTERED INDEX CLIDX_vOrderDetails_Year_Month 
      ON dbo.vOrderDetails(Year,Month)
5. Выполните еще одну инструкцию SELECT. Обратите внимание на то, что SQL Server требуется только два считывания страниц, потому что результат уже вычислен и хранится в индексе.

   SET STATISTICS IO ON
   SELECT Year, Month, OrderTotal
      FROM dbo.OrderDetails
      ORDER BY Year, Month
   SET STATISTICS IO OFF
6. Если у вас установлена одна из следующих версий пакета: SQL Server 2005 Enterprise, Developer или Evaluation edtition - введите и выполните следующую инструкцию SELECT, которая не ссылается на пред ставление, и нажмите (Ctrl+L), чтобы запросить предполагаемый план выполнения, как показано ниже.

   SELECT DATEPART(yy,Orderdate) as Year,
         SUM(LineTotal) as YearTotal
      FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
         ON o.SalesOrderID = od.SalesOrderID
      GROUP BY DATEPART(yy,Orderdate)

   

   
   увеличить изображение
   
7. План выполнения предыдущего запроса показывает, что SQL Server использует кластеризованный индекс в представлении, чтобы извлечь данные, поскольку гораздо эффективнее создать агрегат YearTotal, вычислив сумму найденных в представлении агрегатов за месяц. Таким образом, мы видим, что при наличии индексированных представлений становятся возможным ускорить запрос, не изменяя код самого запроса.
8. Закройте окно среды SQL Server Management Studio.