Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Индекс и xml индекс: вместе веселее  [new]
x3mka
Member

Откуда:
Сообщений: 30
Всем здравствуйте.

Описание проблемы:

Есть табличка, немного упрощена по сравнению с реальной структурой:
CREATE TABLE [dbo].[ActivityLog](
	[LogId] [uniqueidentifier] NOT NULL,
	[EventType] [nvarchar](50) NOT NULL,
	[Message] [nvarchar](255) NOT NULL,
	[RelatedData] [xml] NULL,
	CONSTRAINT [XPKActivityLog] PRIMARY KEY CLUSTERED 
	(
		[LogId] ASC
	) ON [PRIMARY]
) ON [PRIMARY]


В поле RelatedData хранится XML следующего вида:

<changes>
	<prop name="Name" oldValue="xxx" newValue="yyy">	
	<prop name="Status" oldValue="stopped" newValue="started">	
	...
</changes>


Работаю с запросом вида (у достаточно большого кол-ва записей в xml инфа о статусе может отсутствовать, мне такие записи не нужны, поэтому CROSS APPLY, а не OUTER):
SELECT 
	al.*,
	Prop.Change.value('./@newValue','nvarchar(20)') AS [Status]	
FROM dbo.ActivityLog al		
CROSS APPLY RelatedData.nodes('//prop[@name="Status"]') AS Prop(Change)			
WHERE 				
	EventType IN ('SERVER_STOP', 'SERVER_START')


Задачка состоит в оптимизации данного запроса.

В общем попытка номер раз, создал индекс:
CREATE NONCLUSTERED INDEX [XIE2ActivityLog] ON [dbo].[ActivityLog] 
(
	[EventType ] ASC
)


Понятно, запрос ускорился, в плане Seek, как и должно быть.

Попытка два: создал индексы на XML (если я правильно шарю, то тут как раз value индекс нужен):
CREATE PRIMARY XML INDEX IDX_RelatedData ON [dbo].[ActivityLog] (RelatedData)

CREATE XML INDEX IDX_RelatedData_Value ON [dbo].[ActivityLog] (RelatedData)
USING XML INDEX IDX_RelatedData FOR VALUE


В общем результат: в плане используется Seek по вторичному xml индексу при разборе xml как и планировалось, но поиск по самой таблице ActivityLog превращается в Scan (XIE2ActivityLog не юзается).

Вот и думаю, как подружить два индекса.

Мои данные:
Всего записей в ActivityLog ~ 100 000
Отфильтрованных по EventType ~ 50 000
Всех с полем Status в xml ~ 40 000
Отфильтрованных по EventType с полем Status в xml ~ 1 000

Без xml индексов запрос заботает около 2 сек, с xml индексами - раза в 3 дольше.

Кол-во записей будет расти, поэтому не хочу ждать, пока ткнут в проблему.

Если есть идеи, буду премного благодарен.

INCLUDE RelatedData в XIE2ActivityLog не помогает.
19 мар 12, 20:57    [12277020]     Ответить | Цитировать Сообщить модератору
 Re: Индекс и xml индекс: вместе веселее  [new]
x3mka
Member

Откуда:
Сообщений: 30
SELECT @@VERSION

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
19 мар 12, 20:59    [12277034]     Ответить | Цитировать Сообщить модератору
 Re: Индекс и xml индекс: вместе веселее  [new]
Виталий Гонтовой
Member

Откуда: Харьков
Сообщений: 8
Фильтрованный индекс ?
19 мар 12, 21:25    [12277198]     Ответить | Цитировать Сообщить модератору
 Re: Индекс и xml индекс: вместе веселее  [new]
x3mka
Member

Откуда:
Сообщений: 30
Пока не могу понять, как он поможет.
19 мар 12, 21:39    [12277288]     Ответить | Цитировать Сообщить модератору
 Re: Индекс и xml индекс: вместе веселее  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
x3mka
Пока не могу понять, как он поможет.
Если EventType мало, можно сделать несколько фильтрованных XML индексов по количеству значений EventType
19 мар 12, 21:41    [12277298]     Ответить | Цитировать Сообщить модератору
 Re: Индекс и xml индекс: вместе веселее  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Да повесить вычисляемый столбец и добавить его в первый индекс (можно в виде фильтра).
19 мар 12, 21:45    [12277313]     Ответить | Цитировать Сообщить модератору
 Re: Индекс и xml индекс: вместе веселее  [new]
Виталий Гонтовой
Member

Откуда: Харьков
Сообщений: 8
ХМЛ индексы конечное же не фильтруются.

Но если нужны только определенные EventType, то можно XIE2ActivityLog сделать фильтрованным с INCLUDE не только RelatedData, но и всех колонок, которые будут использоваться в SELECT.
19 мар 12, 22:41    [12277647]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить