Столкнулся с похожей (на мой взгляд) проблемой. Необходимо импортировать данные из документа размером в ~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 и планов запросов, чтобы как-то лучше понимать происходящее и быть готовым к чудесам