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

Откуда:
Сообщений: 1196
Всем привет!

Есть таблица
CREATE TABLE [MyTable](
	[CrId] [bigint] IDENTITY(1,1) NOT NULL,
	[ProductId] [int] NOT NULL,
	[CategoryId] [int] NOT NULL,
	[RecordDate] [datetime] NOT NULL,
	[UpdateDate] [datetime] NOT NULL,
	[WebUrl] [varchar](max) NOT NULL,
	[CurrentInfo] [xml](CONTENT [OLTP].[CurrentInfo]) NULL,
	[HistoryId] [int] NOT NULL,
	[RecordDateTrim] [date] NOT NULL,
 CONSTRAINT [PK_CrawlerResult] PRIMARY KEY CLUSTERED 
(
	[CrId] ASC,
	[RecordDateTrim] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [YearlyPSd]([RecordDateTrim])
) ON [YearlyPSd]([RecordDateTrim])
,

которая содержит около 100 млн. записей. Данные без индексов занимают 65 Гб. Регулярно производится запросы к XML полю CurrentInfo, которые нужно оптимизировать.

Идея построения XML индекса по этому полю не очень нравится из-за его размера. Сделать реиндекс на нём будет очень проблематично. Есть идея заменить секционированную таблицу на секционированние представление.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Хотелось бы услышать мнения профессионалов.
3 авг 11, 13:22    [11064224]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Стесняюсь спросить... Вам зачем индекс по XML полю? Вы собираетесь там что-то искать?
3 авг 11, 13:28    [11064258]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
tpg,

Ага. Типа такого:

WITH XMLNAMESPACES('http://oursite.com/ProductSchema.xsd' AS SSA)
SELECT ProductId, 
      CR.CurrentInfo.value('(SSA:CurrentInfo/SSA:Rating/@Value)[1]', 'real')
      AS Rating,                       
      CR.CurrentInfo.value('(SSA:CurrentInfo/SSA:Rating/@ReviewsCount)[1]', 'int')
      AS ReviewsCount,
      CASE CR.CurrentInfo.exist('(SSA:CurrentInfo/SSA:SalesRanks)')
      WHEN 1 THEN
		CR.CurrentInfo.query('(SSA:CurrentInfo/SSA:SalesRanks)')
	  ELSE NULL END
	  AS SalesRank FROM ЬнЕфиду AS CR
		WHERE (CR.RecordDateTrim BETWEEN @BeginDate AND @EndDate)
		AND ((CR.CurrentInfo.exist('(SSA:CurrentInfo/SSA:Rating)') = 1)
		OR (CR.CurrentInfo.exist('(SSA:CurrentInfo/SSA:SalesRanks)') = 1)) AND 
		(CR.CrId IN
          (SELECT  MAX(CrId)
            FROM          OLTP.CrawlerResult AS CR2
            WHERE      (CR2.ProductId = CR.ProductId) AND (CR2.RecordDateTrim BETWEEN @BeginDate AND @EndDate)
            GROUP BY dbo.LastDayOfMonth(CR2.RecordDateTrim)
            ))
3 авг 11, 14:31    [11064684]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
Jovanny,

Вопрос то какой?

Если нужно искать по полю, нужен индекс. Большой индекс- плата за большие данные, ничего не поделаешь.

Только непонятно, зачес индекс для описанного запроса - там ищется ровно одна запись по ПК, никакого поиска по xml полю нету.
3 авг 11, 14:42    [11064767]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
alexeyvg
Только непонятно, зачес индекс для описанного запроса - там ищется ровно одна запись по ПК, никакого поиска по xml полю нету.
А, нет, там же подзапрос много записей возвращает...
3 авг 11, 14:45    [11064793]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
alexeyvg
Jovanny,
Большой индекс- плата за большие данные, ничего не поделаешь.


Вот именно. Будет ли выигрыш, если использовать секционированное представление, где по таблице в секции индекс будет вполне приемлемых размеров?
3 авг 11, 14:48    [11064825]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Т.е. будет ли оптимизатор использовать XML индексы использовать?
И попутно вопрос: можно ли для XML поля в разных таблицах секционированного представления использовать разные XML схемы?
3 авг 11, 14:52    [11064858]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Jovanny
Будет ли выигрыш, если использовать секционированное представление, где по таблице в секции индекс будет вполне приемлемых размеров?

А где у вас в запросе условие для секционирования то ? Период ?
3 авг 11, 14:54    [11064881]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Glory,

BETWEEN @BeginDate AND @EndDate
3 авг 11, 14:55    [11064896]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
Jovanny
alexeyvg
Jovanny,
Большой индекс- плата за большие данные, ничего не поделаешь.


Вот именно. Будет ли выигрыш, если использовать секционированное представление, где по таблице в секции индекс будет вполне приемлемых размеров?
Нет, конечно.

От разбиения суммарный размер индекса не меняется, перестраивать столько же.
3 авг 11, 14:56    [11064907]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Таблица секционирована по RecordDateTrim.
3 авг 11, 14:56    [11064908]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
alexeyvg
От разбиения суммарный размер индекса не меняется, перестраивать столько же.

Не понял. В секционированное представление входят обычные таблицы.
В секционированной таблице я перестраиваю индексы по секциям, в основном по последней, если не правил данные за предыдущие периоды.
Т.е. в основном будет перестраиваться индекс в одной таблице, куда добавляются свежие данные.
3 авг 11, 14:59    [11064938]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31962
Jovanny
alexeyvg
От разбиения суммарный размер индекса не меняется, перестраивать столько же.

Не понял. В секционированное представление входят обычные таблицы.
В секционированной таблице я перестраиваю индексы по секциям, в основном по последней, если не правил данные за предыдущие периоды.
Т.е. в основном будет перестраиваться индекс в одной таблице, куда добавляются свежие данные.
А, это да, согласен.
3 авг 11, 15:03    [11064984]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Jovanny
alexeyvg
От разбиения суммарный размер индекса не меняется, перестраивать столько же.

Не понял. В секционированное представление входят обычные таблицы.
В секционированной таблице я перестраиваю индексы по секциям, в основном по последней, если не правил данные за предыдущие периоды.
Т.е. в основном будет перестраиваться индекс в одной таблице, куда добавляются свежие данные.
Ну и какая разница перестраивать индекс в последней секции секционированной таблицы или перестраивать индекс в последней таблице (для секционированного представления), особо если размеры данных и индексов одинаковы?
3 авг 11, 15:03    [11064991]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
[quot tpg]
Jovanny
пропущено...
Ну и какая разница перестраивать индекс в последней секции секционированной таблицы или перестраивать индекс в последней таблице (для секционированного представления), особо если размеры данных и индексов одинаковы?


Беда в том, что XML индекс не поддерживает секционирование. Т.е. он секционируется как кластерный индекс, но по секции XML индекс не перестроишь.

BOL
Для создания первичного XML-индекса таблица, содержащая индексируемый XML-столбец и называемая базовой таблицей, должна иметь кластеризованный индекс первичного ключа. Это гарантирует, что в случае секционирования базовой таблицы первичный XML-индекс может быть секционирован при использовании той же схемы и той же функции секционирования.
...
Сведения о файловых группах и секционировании пользовательской таблицы применимы к XML-индексам. Однако пользователи не могут задавать их для XML-индекса отдельно.
3 авг 11, 15:23    [11065214]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Jovanny,

а на сколько сильно фрагментируется этот индекс, что вам кровь из носу надо его перестраивать?
4 авг 11, 06:01    [11068397]     Ответить | Цитировать Сообщить модератору
 Re: XML индекс для больших таблиц  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
tpg,

Для этой таблицы я его ещё не создавал, но для похожей, с 5 млн. записями, приходилось перестраивать где-то 2 раза в месяц, и это занимало пару часов. А тут записей в 20 раз больше.
4 авг 11, 09:48    [11068735]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить