XML-индекс замедляет запросы

У меня есть простая таблица со следующей структурой, с ~10 миллионами строк:

CREATE TABLE [dbo].[DataPoints](
    [ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ModuleID] [uniqueidentifier] NOT NULL,
    [DateAndTime] [datetime] NOT NULL,
    [Username] [nvarchar](100) NULL,
    [Payload] [xml] NULL
)

Полезная нагрузка аналогична этой для всех строк:

<payload>
  <total>1000000</total>
  <free>300000</free>
</payload>

Следующие два запроса занимают около 11 секунд каждый, чтобы выполнить на моем компьютере разработчика перед созданием индекса на Payloadстолбце:

SELECT AVG(Payload.value('(/payload/total)[1]','bigint')) FROM DataPoints

SELECT COUNT(*) FROM DataPoints
WHERE Payload.value('(/payload/total)[1]','bigint') = 1000000

Проблема в том, что при создании XML-индекса для Payloadстолбца оба запроса занимают гораздо больше времени! Я хочу знать:

1) Почему это происходит? Разве XML-индекс не должен ускорять запросы или, по крайней мере, запрос, где значение из столбца XML используется в предложении WHERE?

2) каков был бы правильный сценарий использования XML-индексов, если они не подходят для моего случая?

Это на SQL Server 2014.

1 ответ

  1. Обычный XML-индекс индексирует все в полезных данных XML

    Выборочные XML-индексы (SXI)

    Основное ограничение обычных XML-индексов заключается в том, что они индексируют весь XML-документ. Это приводит к ряду существенных недостатков, таких как снижение производительности запросов и увеличение расходов на обслуживание индекса, в основном связанных с затратами на хранение индекса.

    Для повышения производительности необходимо создать выборочный XML-индекс.

    Другой вариант-создание вторичных индексов

    XML-индексы (SQL Server)

    Для повышения производительности поиска можно создавать вторичные XML-индексы. Первичный XML-индекс должен существовать до создания вторичных индексов.

    Таким образом, назначение первичного индекса заключается в создании вторичных индексов