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

Откуда:
Сообщений: 754
Уважаемые коллеги, приветствую!

Помогите ускорить выборку из xml!
Сам xml - предельно прост:
declare @inp as xml = N'<row>
  <a>ИВАНИЧЕВ</a>
  <a>АЛЕКСАНДР</a>
  <a>ИВАНОВИЧ</a>
  <a>28.08.1973</a>
  <a />
  <a>00821000086-0000</a>
  <a />
  <a />
  <a>2.1</a>
</row>
<row>
  <a>БАБКИНА</a>
  <a>ИРИНА</a>
  <a>ИВАНОВНА</a>
  <a>22.12.1973</a>
  <a />
  <a>00821000087-0000</a>
  <a />
  <a />
  <a>2.2</a>
</row>
<row>
  <a>ГРЕЗЕВА</a>
  <a>ТАТЬЯНА</a>
  <a>ЮРЬЕВНА</a>
  <a>30.04.1981</a>
  <a />
  <a>00821000088-0000</a>
  <a />
  <a />
  <a>2.1</a>
</row>
<row>
  <a>ЗАКАР</a>
  <a>МАРИЯ</a>
  <a>ВИКТОРОВНА</a>
  <a>08.11.1971</a>
  <a />
  <a>00821000089-0001</a>
  <a />
  <a />
  <a>3.3</a>
</row>'

Select 
	ROW_NUMBER() over (order by 1/0) N
	,Upper(Ltrim(Rtrim(t.n.value('(a)[1]', 'varchar(40)')))) [fam]
	,Upper(Ltrim(Rtrim(t.n.value('(a)[2]', 'varchar(40)')))) [im]
	,Upper(Ltrim(Rtrim(t.n.value('(a)[3]', 'varchar(40)')))) [ot]
	,Try_convert(date, Ltrim(Rtrim(t.n.value('(a)[4]', 'varchar(20)'))), 104) [dr]
	,Ltrim(Rtrim(t.n.value('(a)[5]', 'varchar(40)'))) [n1]
	,Ltrim(Rtrim(t.n.value('(a)[6]', 'varchar(40)'))) [n2]
	,Ltrim(Rtrim(t.n.value('(a)[7]', 'varchar(40)'))) [otdel]
	,Ltrim(Rtrim(t.n.value('(a)[8]', 'varchar(40)'))) [phone]
	,Ltrim(Rtrim(t.n.value('(a)[9]', 'varchar(40)'))) [ver]
into #inp
	from @inp.nodes('row') t(n)


Проблема в том, что в типовом хмл - порядка 1-2 миллионов <row>.
И его отработка длится минут 10-15.
Нельзя ли хоть как-то подсакратить?
@inp - это входящий параметр хранимки.

Что-нибудь можно сделать? По частям распарсить, я не знаю, в поле временной таблицы засунуть, и индекс построить, как типизированный хмл объявить, хоть что-нибудь, я не знаю.
Подскажите, дайте пример, пожалуйста!

Хочется ускорения раз в 10. Но буду рад, если хоть как-то...
14 фев 20, 16:57    [22080205]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4309
uaggster,

А вам это надо в БД делать? Вообще есть вещи типа XSLT или LinqToXml. При делании их можно засунуть в CLR. Предкомпилированный Xslt довольно быстро может парзить.
14 фев 20, 17:03    [22080210]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
Владислав Колосов
Member

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

ETL задачи надо решать внешними средствами.
14 фев 20, 17:11    [22080217]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
uaggster
Member

Откуда:
Сообщений: 754
a_voronin, это жуткое легаси, на самом деле. Они так параметры для выборки передают, табличные.
Только вначале речь шла о паре сотен - тысяче записей, а потом постепенно доросло до миллионов, и теперь уперлось в порог, когда хранимка валится по таймауту, причем сам таймаут увеличить нельзя, он где-то в дебрях сервера приложений зарыт.
Нужно срочно что-то закостылить, чтобы хоть как-то работало.

Так что да, в базе. По куче соображений бюрократического и не переводимого на русский (кроме матерного) характера.
14 фев 20, 17:13    [22080220]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
invm
Member

Откуда: Москва
Сообщений: 9079
uaggster,

Попробуйте OPENXML.
Или https://docs.microsoft.com/ru-ru/sql/relational-databases/sqlxml-annotated-xsd-schemas-xpath-queries/bulk-load-xml/performing-bulk-load-of-xml-data-sqlxml-4-0?view=sql-server-ver15
14 фев 20, 17:15    [22080224]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
uaggster
Member

Откуда:
Сообщений: 754
invm, да, через Openxml - в 1,5 раза быстрее получается, почти в два.
Не совсем то, что хотелось, но хоть что-то!
Спасибо.
14 фев 20, 17:54    [22080260]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
invm
Member

Откуда: Москва
Сообщений: 9079
uaggster,

+
declare @inp as xml = N'<row>
  <a>ИВАНИЧЕВ</a>
  <a>АЛЕКСАНДР</a>
  <a>ИВАНОВИЧ</a>
  <a>28.08.1973</a>
  <a />
  <a>00821000086-0000</a>
  <a />
  <a />
  <a>2.1</a>
</row>
<row>
  <a>БАБКИНА</a>
  <a>ИРИНА</a>
  <a>ИВАНОВНА</a>
  <a>22.12.1973</a>
  <a />
  <a>00821000087-0000</a>
  <a />
  <a />
  <a>2.2</a>
</row>
<row>
  <a>ГРЕЗЕВА</a>
  <a>ТАТЬЯНА</a>
  <a>ЮРЬЕВНА</a>
  <a>30.04.1981</a>
  <a />
  <a>00821000088-0000</a>
  <a />
  <a />
  <a>2.1</a>
</row>
<row>
  <a>ЗАКАР</a>
  <a>МАРИЯ</a>
  <a>ВИКТОРОВНА</a>
  <a>08.11.1971</a>
  <a />
  <a>00821000089-0001</a>
  <a />
  <a />
  <a>3.3</a>
</row>';

declare @x xml, @c int = 250000;
declare @g int, @1 varchar(100), @2 varchar(100), @3 varchar(100), @4 varchar(100), @5 varchar(100), @6 varchar(100), @7 varchar(100), @8 varchar(100);

-- миллион row
select
 @x = t.x
from
 (
  select top (@c)
   @inp as [*]
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
  for xml path(''), type
 ) t(x);

set statistics time on;

with s as
(
 select
  t.n.value('.', 'varchar(100)') as v,
  (row_number() over (order by 1/0) - 1) / 9 as g,
  (row_number() over (order by 1/0) - 1) % 9 as n
 from
  @x.nodes('row/a') t(n)
)
select
 @1 = max(case when n = 0 then v end),
 @2 = max(case when n = 1 then v end),
 @3 = max(case when n = 2 then v end),
 @4 = max(case when n = 3 then v end),
 @5 = max(case when n = 4 then v end),
 @6 = max(case when n = 5 then v end),
 @7 = max(case when n = 6 then v end),
 @8 = max(case when n = 7 then v end)
from
 s
group by
 g;

set statistics time off;

 SQL Server Execution Times:
CPU time = 48578 ms, elapsed time = 55590 ms.
14 фев 20, 18:36    [22080287]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
aleks222
Member

Откуда:
Сообщений: 818
uaggster

Хочется ускорения раз в 10. Но буду рад, если хоть как-то...


sqlXmlBulkLoad

ЗЫ. Потоковые парсеры XML раз в сто быстрее.
14 фев 20, 19:03    [22080321]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4882
Да, если на входе xml-файл, то через bulkload можно. Будет в 10 раз быстрее.
14 фев 20, 22:49    [22080389]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
uaggster
Member

Откуда:
Сообщений: 754
Megabyte, в том то и дело, что это входной параметр хранимой процедуры.
Теоретически, его можно сохранить в filetable, а потом натравить на него парсер, а потом парсить в динамически созданную постоянную таблицу, но это хардкор, я б сказал!
15 фев 20, 05:48    [22080440]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
aleks222
Member

Откуда:
Сообщений: 818
uaggster
Megabyte, в том то и дело, что это входной параметр хранимой процедуры.
Теоретически, его можно сохранить в filetable, а потом натравить на него парсер, а потом парсить в динамически созданную постоянную таблицу, но это хардкор, я б сказал!


sqlXmlBulkLoad сам может создать все таблицы. Например, в tempdb. Схема только нужна.
Работать он может не с файла, а с потока, в том числе и с потока из базы.
AdoDB.Stream.

Все DOM-парсеры отдыхают.
15 фев 20, 13:27    [22080487]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли ускорить выборку из xml?  [new]
Владислав Колосов
Member

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

filetable не очень хорошая тема, лучше сохраните параметр в глобальную временную таблицу, отгрузите ее в файл через xp_cmdshell bcp, затем примените sqlXmlBulkLoad. Но это всё равно для извращенцев. Лучше сразу из приложения отгружать в файл.
15 фев 20, 15:42    [22080507]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить