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

Откуда: Кишинёв
Сообщений: 6724
Не знаю даже куда постить. И надеюсь не баян.

Имеется xml размером 50-100 метров примерно следующей структуры:
/Root/<Table>/Data/Row, где <Table> название объекта.
нод /Root/<Table> имеется под 20-40 штук, а самих /Root/<Table>/Data/Row под сотню-другую тысяч. Так вот есть два эквивалентных запроса:
SELECT M.X.value('@ID','Int') FROM @XML.nodes('/Root/MyTable/Data/Row')M(X)
SELECT R.X.value('@ID','Int') FROM @XML.nodes('/Root/MyTable/Data')M(X) CROSS APPLY M.X.nodes('Row')R(X)
Первое отрабатывает за минут 20, в второе за секунд 20. Стабильно, и на похожих случаях.
/Root/MyTable/Data - порядка 10 элементов, а всего всех этих Row меньше 100 тысяч.

Что-то какой-то "слегка" неоптимальный этот XQuery.
Регить как багу? Или может быть так стало после последних оптимизаций?
9 июн 11, 09:56    [10787345]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
Столкнулся с похожей (на мой взгляд) проблемой. Необходимо импортировать данные из документа размером в ~15 Мб.

Структура документа - Workbook/Worksheet/Table/Row/Cell

Workbook/Worksheet/Table - 1 запись
Row - ~4 тыс. записей
Cell - ~20-30 узлов на строку

Запрос возвращает 86 строк за 5 секунд
select distinct T.r.query('.').value('(/Row/Cell)[1]', 'varchar(200)') n
from @xml.nodes('/Workbook/Worksheet/Table/Row') T(r)

попытка вставить результаты этого запроса во временную таблицу - 9 минут!

declare @t table(n varchar(200))
insert into @t(n)
select distinct T.r.query('.').value('(/Row/Cell)[1]', 'varchar(200)') n
from @xml.nodes('/Workbook/Worksheet/Table/Row') T(r)
Посоветовали сравнить актуальные планы запросов.
В итоге выяснилось, что проблема в том, что в плане есть Table Spool (Eager Spool) с актуальным числом записей ~ 1 млрд.!!!

Переписал запрос на следующий:

declare @t table(n varchar(200))
insert into @t(n)
select distinct T.r.value('(./Cell[1])[1]', 'varchar(200)') n
from @xml.nodes('/Workbook/Worksheet/Table/Row') T(r)
Выполнилось за 5 секунд, как и требовалось.

Есть какие-нибудь соображения почему оптимизатор в одном случае добавляет спул, а в другом нет?
Подскажите что почитать на тему XQuery и планов запросов, чтобы как-то лучше понимать происходящее и быть готовым к чудесам
21 июн 11, 21:00    [10850377]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> попытка вставить результаты этого запроса во временную таблицу - 9 минут!

2008-ой?
21 июн 11, 21:26    [10850462]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
21 июн 11, 21:31    [10850475]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
если _перед началом батча_ выставить dbcc traceon(4130) - не лечится?
http://support.microsoft.com/kb/957205

хотя, если переписывание помогло, мож и не оно. но похоже по описанию.
21 июн 11, 21:36    [10850490]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
не помогло
данные выдаваться стали раньше (начиная где-то со 2-й минуты)
время выполнения 8:45
21 июн 11, 21:50    [10850523]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
forestGreen
не помогло
данные выдаваться стали раньше (начиная где-то со 2-й минуты)
время выполнения 8:45


как это - выдаваться? проблема же со вставкой была, или я не понял чего?
ну, нет - так нет. не оно, значит.
21 июн 11, 21:59    [10850557]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
Проблема была в Table Spool'е, которые генерил 1 млрд. записей перед вставкой в таблицу, что увеличило время выборки данных с 5 секунд до 9 минут.
Мне бы хотелось понять, почему возник этот table spool и как его можно избежать и предвидеть.
21 июн 11, 23:40    [10850860]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
daw
как это - выдаваться? проблема же со вставкой была, или я не понял чего?

Это я что-то вчера заработался. В тестовом запросе стоял select и за ним insert+select. select выдал данные, а затем 8 с лишним минут работал insert. В общем установка флага не помогла, или если и помогла, то всего лишь уменьшим время вставки на 20-30 секунд.
22 июн 11, 09:36    [10851669]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> В общем установка флага не помогла, или если и помогла, то всего лишь уменьшим время
> вставки на 20-30 секунд.

а точно _до_ начала батча его ставите? он на его компиляцию влияет, потому что.

dbcc traceon
GO
-- тут инсерт идет

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

Posted via ActualForum NNTP Server 1.4

22 июн 11, 09:45    [10851728]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
ваша настырность более чем уместна - флаг ставил в том же батче (хоть и в самом начале)
при выносе в отдельный - все заработало!

PS: Посоветуйте что-нибудь толковое почитать по XQuery + планы запросов
22 июн 11, 10:43    [10852129]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> PS: Посоветуйте что-нибудь толковое почитать по XQuery + планы запросов

ух ты! вот, только что рылся - и вот на такой вайтпапир наткнулся:

http://www.vldb2005.org/program/paper/thu/p1175-pal.pdf

будем посмотреть-почитать.

зы: да, а по поводу вот этого конкретно случая, лечащегося трейсфлагом, - так то своего рода бага просто.
как разъясняют:
The spool was introduced due to a general halloween protection logic (that is not needed for the XQuery expressions). In
order to minimize the risk, we decided to use the traceflag to turn the spool off for now. We are looking into how to
address this in an upcoming version.

https://connect.microsoft.com/SQLServer/feedback/details/594974/bad-performance-when-inserting-data-from-element-centric-documents

Posted via ActualForum NNTP Server 1.4

22 июн 11, 11:45    [10852686]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
Не совсем понятно откуда может быть "hallowen problem" при вставке во временную таблицу
В любом случае спасибо за ссылку - почитаю
22 июн 11, 12:26    [10853150]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
forestGreen
Столкнулся с похожей (на мой взгляд) проблемой
@#%ть. forestGreen, нафига ты засрал эту тему. Что, не мог свою завести?!
У меня проблема кардинально в другом - в MSXML библиотеке, точнее в алгоритме поиска узлов.
Внимательно и вдумчиво читать надо, а не по похожести букав смотреть.

А Файлы нужно загружать в таблы через XMLBulkLoad и только черех XMLBulkLoad.

Мне что заново тему заводить?!
22 июн 11, 21:43    [10857998]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Mnior, я уже говорил вам, что вставать нужно желательно с обоих ног сразу? Шоб не получилось, что не с той ноги встали? ))).

По сути - для меня SQL+XML, ну и XQuery до кучи - шо китайская грамота. Т.е. читать-то читаю, че-то даже написать могу, но понимания нет. Потому как практики нет. Потому как чтение документации без практики - потеря времени. Очень даже не удивлюсь, если подобная ситуация наблюдается у бОльшей части участников данного форума. Т.е. когда у forestGreen возникла проблема, то я не долго думая порекомендовал обратиться к вам, как к человеку хорошо понимающему XML-кухню. А вы вот так, сходу, да и в грязь утоптать...

Ну я конечно понимаю, что скуль не совсем добрый форум. Сам я тоже не добрый ни разу. Но тут ведь вроде вопрос вполне коректно задан, если есть желание - разъясните почему и как должно быть, а также что происходит в обратном случае. По крайней мере мне знать это будет интересно и полезно. Это если серьезно говорить.
22 июн 11, 23:07    [10858348]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
kDnZP
Потому как чтение документации без практики - потеря времени.
Оно конечно, только имея под рукой и доки (инет) и инструменты (сервер(а)) можно самостоятельно до всё дотумкать, если конечно иметь голову на плечах. Но если нет, то ничего не поможет.
Но соффремённая молодёжж привыкла со школы, что лучше сначала пусть другие подумают/сделают/поищут/нажмут кнопку, ну а если нет то тогда ладно, так уж и быть, сделаю одолжение миру, сам решу свою проблему - поищу в нете/сделаю/пошевелю извилинами.

kDnZP
то я не долго думая порекомендовал обратиться к вам
Это вот оно откуда ноги растут. Получается меня негласно наняли на решение чужих проблем. Мене "пеар" не нужен, и записывать в "учятеля" тоже не надо. Кто бы, что бы там не знал/умел это не повод не быть самостоятельным. Не надо культа личностей.
Учителям, представьте, тоже платят, так что если надо то обращайтесь на соответствующий форум.
Если есть проблема, ставьте пост на форум и пусть каждый, кто заинтересуется и захочет помочь, тот и отвечает.

Общественности важна не помощь конкретному лицу (да пусть он провалится), важно выработка общих и стратегических решений и принципов для общей объявленной проблеме.

kDnZP
Ну я конечно понимаю, что скуль не совсем добрый форум.
Ещё один хранитель мифов. Нет "добрых" форумов в этой вселенной, нигде.

kDnZP
если есть желание - разъясните почему и как должно быть, а также что происходит в обратном случае.
Во первых надо было поискать готовые решения. Во вторых (если оных нет не смог найти), то постить в отдельной теме, а не нарушать правила форума, тем более дважды.

XMLBulkLoad, лично я описывал не один десяток раз, не говоря о других. И на этом всё, далее шевелите пальцами сами.
И если хотите сделать "добро", выложите в FAQ это, если там его нет.
23 июн 11, 12:53    [10860806]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
forestGreen
Member

Откуда:
Сообщений: 11
Mnior
постить в отдельной теме, а не нарушать правила форума, тем более дважды.

Если вы считаете, что были нарушены правила - обратитесь к модераторам
23 июн 11, 13:20    [10861124]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
forestGreen
Столкнулся с похожей (на мой взгляд) проблемой
@#%ть. forestGreen, нафига ты засрал эту тему. Что, не мог свою завести?!
Заводишь свой форум, там заводишь свою тему и нещадно банишь тех кто ее засирает. Человеку показалось что тема похожая, он и спросил в ней, чтоб не делать еще стопицот тем, от которых все так стонут "ну вот опять", "каждый день одно и то же" - а выливать на него за это ушат брани какое-то гопничество (ты пацанчик че в моем дваре делаешь, сам ваще с какова района?). И форумы в инете "добрые" есть. И в учителя тебя никто не прочит, будь спок (максимум можно назвать: "экспертного" мнения спросили).
23 июн 11, 13:21    [10861150]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Mnior, когда возникнет необходимость разобраться с XML - я разберусь. По крайней мере, в это верю.
Вашу позицию вполне понял (даже можно сказать по большей части поддерживаю), в плане того, что самостоятельность это хорошо.
Но всеже у меня несколько иная позиция. Описать ее можно простой фразой: "Отдавать не меньше, чем брать". В том числе это касается и знаний. Главный принцип: "Никто никому ничего не должен" - верно. Но это не мешает мне при наличие желания времени - отвечать на вопросы, да и периодически ВНЕЗАПНО абсолютно бесплатно и оперативненько поработать если не головой, то руками (с)

* Но это ессно лично мое мнение, которое может не совпадать с мнением редакции. В любом случае - вам спасибо, как впрочем и всем другим, кто регулярно делится знаниями.
23 июн 11, 13:28    [10861235]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
Однако__
Guest
Mnior

А Файлы нужно загружать в таблы через XMLBulkLoad и только черех XMLBulkLoad.

Мне что заново тему заводить?!


Есть неплохая альтернатива в виде того же SSIS.
23 июн 11, 13:51    [10861480]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow, проблем с баном таки нет, есть кнопочка "Сообщить модератору", указываешь пункт правил форума, предупреждение (бан если совсем наглёшь), профит.

SomewhereSomehow
чтоб не делать еще стопицот тем, от которых все так стонут "ну вот опять", "каждый день одно и то же"
Если были проделаны усилия в этом направлении, то количество тем было в 100 раз меньше. Поэтому пункт правил "поискать перед" давно не работает (если вообще работал когда либо). С этим многие смирились.

SomewhereSomehow
ушат брани
Может быть и надо было поменьше, но слишком много пунктов нарушено. Но на вопрос я его также ответил, так что не надо тут общественный суд устраивать, ладно. Сказали раз и ладно.

SomewhereSomehow
И форумы в инете "добрые" есть.
Ещё один. Притом без какогото маленького пруфа.

SomewhereSomehow
И в учителя тебя никто не прочит, будь спок (максимум можно назвать: "экспертного" мнения спросили).
На мнение kDnZP, я ответил, давайте вы не будете обобщать мои слова. Ok?
И тем более вешать на меня ярлыки "эксперта". Вы видимо не до конца меня поняли.
23 июн 11, 14:04    [10861628]     Ответить | Цитировать Сообщить модератору
 Re: xml, nodes speed  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Однако__
Есть неплохая альтернатива в виде того же SSIS.
Да, разве что это не альтернатива, а вариант из.
23 июн 11, 14:49    [10862212]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить