#sql #xml #sql_server
Подскажите, кто имел опыт работы с построением индекса для XML:
С какой скоростью он строится? Решил его повесить на базе в 7 000 000 записей, так
он уже 7 час строится.
Наверное, если отменить построение, то он столько же будет откатываться?
Сколько в итоге будет весить такой индекс? Тяжелее, чем максимально доступно для
индексирования поле nvarchar?
Ответы
Ответ 1
С какой скоростью он строится? Зависит от сложности индексируемых XML-сущностей и их количества. Если у вас SqlServer 2014 или более поздняя версия, то прогресс построения индекса в какой-то степени можно отслеживать (см. тут и тут). Наверное, если отменить построение, то он столько же будет откатываться? Отмена построения происходит, вроде бы, достаточно быстро. Сколько в итоге будет весить такой индекс? Это зависит, опять же, от сложности индексируемых XML-сущностей. Но, как правило, XML-индексы прожорливы. Это можно видеть на примере. Пусть есть таблица со столбцом xml типа CREATE TABLE XTable (Id int IDENTITY CONSTRAINT PK_XTable PRIMARY KEY, XData xml); Добавим в неё 100 тыс. довольно простых XML-сущностей INSERT INTO XTable (XData) SELECT TOP (100000) '' FROM sys.all_columns a, sys.all_columns b; Построим на столбце XData разнообразные виды XML-индексов CREATE PRIMARY XML INDEX XI_XTable_XData ON XTable (XData); CREATE XML INDEX XI_XTable_XData_Path ON XTable (XData) USING XML INDEX XI_XTable_XData FOR PATH; CREATE XML INDEX XI_XTable_XData_Value ON XTable (XData) USING XML INDEX XI_XTable_XData FOR VALUE; CREATE XML INDEX XI_XTable_XData_Property ON XTable (XData) USING XML INDEX XI_XTable_XData FOR PROPERTY; CREATE SELECTIVE XML INDEX XI_XTable_XData_item ON XTable (XData) FOR ( item = '/root/item' AS XQUERY 'node()', itemid = '/root/item/@id' as SQL int SINGLETON ); CREATE XML INDEX XI_XTable_XData_item_id ON XTable (XData) USING XML INDEX XI_XTable_XData_item FOR ( itemid ); и посмотрим сколько дополнительного места они занимают. Первичный (primary xml index) и селективный (selective xml index) представляют собой select xml_index_name = xi.name, internal_table_name = it.name, it.internal_type_desc from sys.xml_indexes xi join sys.internal_tables it on it.parent_id = xi.object_id and it.parent_minor_id = xi.index_id where xi.object_id = object_id('XTable'); отдельные внутренние таблицы с кластерным индексом xml_index_name internal_table_name internal_type_desc --------------------- ---------------------------------- ------------------------------- XI_XTable_XData xml_index_nodes_2002106173_256000 XML_INDEX_NODES XI_XTable_XData_item xml_sxi_table_2002106173_256004 SELECTIVE_XML_INDEX_NODE_TABLE Дополнительные XML-индексы это select xml_index_name = xi.name, index_name = ix.name, ix.type_desc, ix.filter_definition from sys.indexes ix join sys.internal_tables it on it.object_id = ix.object_id join sys.xml_indexes xi on xi.object_id = it.parent_id and xi.index_id = it.parent_minor_id where xi.object_id = object_id('XTable'); фактически дополнительные некластерные индексы на этих внутренних таблицах xml_index_name index_name type_desc filter_definition --------------------- ------------------------- ------------- -------------------------- XI_XTable_XData XI_XTable_XData CLUSTERED NULL XI_XTable_XData XI_XTable_XData_Path NONCLUSTERED NULL XI_XTable_XData XI_XTable_XData_Value NONCLUSTERED NULL XI_XTable_XData XI_XTable_XData_Property NONCLUSTERED NULL XI_XTable_XData_item XI_XTable_XData_item CLUSTERED NULL XI_XTable_XData_item XI_XTable_XData_item_id NONCLUSTERED ([path_2_id] IS NOT NULL) Сколько индексы занимают места можно узнать запросив sys.dm_db_index_physical_stats select index_name = ix.name, st.index_type_desc, size_KB = sum(st.page_count * 8), size_MB = sum(st.page_count / 128.0) from sys.indexes ix cross apply sys.dm_db_index_physical_stats(db_id(), ix.object_id, ix.index_id, DEFAULT, 'DETAILED') st where ix.object_id = object_id('XTable') group by ix.name, st.index_type_desc; Получилось index_name index_type_desc size_KB size_MB ------------------------- ------------------ -------- ------------ PK_XTable CLUSTERED INDEX 16392 16.007811 <-- объём таблицы XI_XTable_XData PRIMARY XML INDEX 114504 111.820312 XI_XTable_XData_Path XML INDEX 69816 68.179686 XI_XTable_XData_Value XML INDEX 69832 68.195311 XI_XTable_XData_Property XML INDEX 69872 68.234374 XI_XTable_XData_item XML INDEX 46640 45.546874 XI_XTable_XData_item_id XML INDEX 17912 17.492187 в несколько раз больше, чем сами данные. При построении первичного XML-индекса каждая XML-сущность разбивается на узлы, и для каждого узла во внутренней таблице создаётся запись (для сущностей со значением NULL записи не создаются). Чем сложнее XML-сущность, тем этих записей больше. Так, например, каждой нашей XML-сущности во внутренней таблице будет соответствовать 11 + 10 + 1 = 22 записи, т.к. сущность содержит 11 элементов, у 10 из которых есть по одному атрибуту, плюс один скрытый корневой элемент, который создаётся даже для пустых (@xml = N'') XML-сущностей. Длина записи, соответствующей узлу, возрастает с увеличением уровня вложенности узла. Поэтому, перед построением XML-индекса, лучше сделать прикидку объёма данных и времени построения на небольшом репрезентативном подмножестве строк. И, самое главное, проверить будущие запросы с построенными XML-индексами, т.к. от добавления XML-индексов они могут как ускориться, так и замедлиться. SqlServer, по-видимому, использует XML-индексы безусловно, т.е. если XML-индекс есть, и он подходит для использования в запросе, то он используется. Иногда, однако, доступ через индекс может быть медленнее, чем парсинг сущности на лету. Ответ 2
В общем не дождался построения и отменил его. Индекс откатился быстро. Провел эксперимент на 63775(Если верить datalength, то там XML на 3 гб) записях и он построился за 13 минут => получатся 4905 записей/мин, что означает, что для построения индекса для 7 000 000 будет затрачено 23 часа. При этом сервер загружен вообще не был и CPU не уходил в потолок => медленность построения связана с природой XML. А вес индекса для 63775 записей составил 9802МБ, если верить этому запросу: SELECT i.[name] AS IndexName ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id] GROUP BY i.[name] ORDER BY i.[name] GO Что не очень хорошо для БД, которая итак весит 600 ГБ из-за XML. Решил протестировать скорость выборки на запросе ради которого все это затевалось: SELECT a.e.value('(local-name(.))[1]','nvarchar(255)') AS NodeName, a.e.value('(text())[1]','nvarchar(255)') AS Value INTO #TMP FROM [TABLE] CROSS APPLY XDoc.nodes('root/SomeNode/*') a(e) WHERE EXISTS([Какое-то условие отбора]) И производительность стала ниже... Хотя, прочитав документация, я ожидал, что разжеванный XML будет работать быстрее.. Даже вторичные индексы не спасил.
Комментариев нет:
Отправить комментарий