Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
любитель xml
Guest
Добрый день коллеги!
Есть xml, содержащий значения параметров. В одном элементе такого xml содержится пара атрибутов Name, Value.
Хотелось бы посмотреть как одним запросом присвоить значения таких параметров соответствующим переменным.
Свой вариант в 4 присвоения я привёл.
Хотелось бы облегчить железяке труд и сэкономить ресурсы.
Помогите с вариантами.

+

declare
  @p1 xml
, @int int
, @dt datetime
, @vc varchar(128)
, @bit bit
set @p1=convert(xml,
N'<node>
	<P PName="Name1" PValue="55"/>
	<P PName="Name2" PValue="vchar value"/>
	<P PName="Name3" PValue="2016-02-01T11:24:51.157"/>
	<P PName="Name4" PValue="1"/>
</node>')
select @p1

set @int = @p1.value('(node/P[@PName = "Name1"]/@PValue)[1]', 'int');
set @vc = @p1.value('(node/P[@PName = "Name2"]/@PValue)[1]', 'varchar(128)');
set @dt = @p1.value('(node/P[@PName = "Name3"]/@PValue)[1]', 'datetime');
set @bit = @p1.value('(node/P[@PName = "Name4"]/@PValue)[1]', 'bit');
select @int as _int, @vc as _vchar, @dt as _Dt, @bit as _bit
1 фев 16, 11:44    [18755222]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
select @int = @p1.value('(node/P[@PName = "Name1"]/@PValue)[1]', 'int'), 
@vc = @p1.value('(node/P[@PName = "Name2"]/@PValue)[1]', 'varchar(128)'), 
@dt = @p1.value('(node/P[@PName = "Name3"]/@PValue)[1]', 'datetime'), 
@bit = @p1.value('(node/P[@PName = "Name4"]/@PValue)[1]', 'bit');


Сообщение было отредактировано: 1 фев 16, 11:48
1 фев 16, 11:48    [18755250]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
любитель xml
Guest
Glory,
спасибо, конечно, формально это то, что я хотел.
Слава, Ваше чувство юмора осталось прежним.

Но, как я понимаю, с точки зрения производительности Ваш вариант и мой по сути есть одно и то же.
Есть ли ещё варианты?
1 фев 16, 11:56    [18755322]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
select
 @int = max(case when b.PName = 'Name1' then cast(b.PValue as int) end),
 @vc = max(case when b.PName = 'Name2' then cast(b.PValue as varchar(128)) end),
 @dt = max(case when b.PName = 'Name3' then cast(b.PValue as datetime) end),
 @bit = max(case when b.PName = 'Name4' then cast(b.PValue as tinyint) end)
from
 @p1.nodes('/node/P') a(n) cross apply
 (select a.n.value('@PName', 'varchar(30)'), a.n.value('@PValue', 'varchar(128)')) b(PName, PValue);

select @int, @vc, @dt, @bit;
1 фев 16, 11:58    [18755341]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
XQuery значения переменных из ат
Guest
invm, спасибо.
А почему Вы решили, что это будет лучше?
Стоимость плана выше в 16 раз (хотя и выглядит он покрасивее (3 ридера вместо 12)). По CPU вроде полегче, но как-то выглядит это дело не очень.
Или Вы просто предложили альтернативный вариант безотносительно производительности?
1 фев 16, 12:11    [18755431]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
XQuery значения переменных из ат
А почему Вы решили, что это будет лучше?
Стоимость плана выше в 16 раз (хотя и выглядит он покрасивее (3 ридера вместо 12)). По CPU вроде полегче, но как-то выглядит это дело не очень.
Потому что вызовов XML Reader 9 вместо 12 и т.п.
Сравнивать стоимости оценочных планов не всегда корректно: например, в моем варианте оценочное количество вызовов XML Reader на внутренней стороне NL - 60, а в реальности - 4.

+ Проверяем затраты
declare
  @p1 xml
, @int int
, @dt datetime
, @vc varchar(128)
, @bit bit
set @p1=convert(xml,
N'<node>
	<P PName="Name1" PValue="55"/>
	<P PName="Name2" PValue="vchar value"/>
	<P PName="Name3" PValue="2016-02-01T11:24:51.157"/>
	<P PName="Name4" PValue="1"/>
</node>')

declare @c int = 100;

while @c > 0
begin

 select/*case1*/
  @int = max(case when b.PName = 'Name1' then cast(b.PValue as int) end),
  @vc = max(case when b.PName = 'Name2' then cast(b.PValue as varchar(128)) end),
  @dt = max(case when b.PName = 'Name3' then cast(b.PValue as datetime) end),
  @bit = max(case when b.PName = 'Name4' then cast(b.PValue as tinyint) end)
 from
  @p1.nodes('/node/P') a(n) cross apply
  (select a.n.value('@PName', 'varchar(30)'), a.n.value('@PValue', 'varchar(128)')) b(PName, PValue);

 select/*case2*/
  @int = @p1.value('(node/P[@PName = "Name1"]/@PValue)[1]', 'int'), 
  @vc = @p1.value('(node/P[@PName = "Name2"]/@PValue)[1]', 'varchar(128)'), 
  @dt = @p1.value('(node/P[@PName = "Name3"]/@PValue)[1]', 'datetime'), 
  @bit = @p1.value('(node/P[@PName = "Name4"]/@PValue)[1]', 'bit');

 set/*case3*/ @int = @p1.value('(node/P[@PName = "Name1"]/@PValue)[1]', 'int');
 set/*case4*/ @vc = @p1.value('(node/P[@PName = "Name2"]/@PValue)[1]', 'varchar(128)');
 set/*case5*/ @dt = @p1.value('(node/P[@PName = "Name3"]/@PValue)[1]', 'datetime');
 set/*case6*/ @bit = @p1.value('(node/P[@PName = "Name4"]/@PValue)[1]', 'bit');

 select @c -= 1;
end;
go

with s as
(
 select
  st.statement_text, cast(qs.total_elapsed_time as float) / qs.execution_count as avg_elapsed_time, row_number() over (partition by st.statement_text order by qs.last_execution_time desc) as rn
 from
  sys.dm_exec_query_stats qs cross apply
  sys.dm_exec_sql_text(qs.plan_handle) qt cross apply
  (select substring(qt.text, qs.statement_start_offset / 2 + 1, (case when qs.statement_end_offset = -1 then datalength(qt.text) else qs.statement_end_offset end - qs.statement_start_offset) / 2 + 1)) st(statement_text) join
  (values ('%select/*case1*/%'), ('%select/*case2*/%'), ('%set/*case3*/%'), ('%set/*case4*/%'), ('%set/*case5*/%'), ('%set/*case6*/%')) p(s) on st.statement_text like p.s
 where
  st.statement_text not like '%dm[_]exec[_]query[_]stats%'
)
select
 statement_text, avg_elapsed_time
from
 s
where
 rn = 1;
go
1 фев 16, 13:02    [18755831]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Могу еще такое синтаксическое извращение предложить:
SELECT
	@int = MAX(CASE WHEN RowNum2 = 1 THEN val END),
	@vc = MAX(CASE WHEN RowNum2 = 2 THEN val END),
	@dt = MAX(CASE WHEN RowNum2 = 3 THEN val END),
	@bit = MAX(CASE WHEN RowNum2 = 4 THEN val END)
FROM (
	SELECT
		val = t.c.value('.', 'VARCHAR(100)'),
		RowNum1 = ROW_NUMBER() OVER (ORDER BY 1/0) % 2,
		RowNum2 = ROW_NUMBER() OVER (ORDER BY 1/0) / 2
	FROM @x.nodes('node/P/@*') t(c)
) t
WHERE RowNum1 = 0
1 фев 16, 13:07    [18755869]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
любитель xml
Guest
invm,
Конечно, я estimated не сравниваю. Но Ваш ответ мною понят так, что у Вас критерием была минимизация вызовов XML Reader. Ok. Я чуток переписал Вашу же идею вот так:
with nodes as
(
	select
	  c.value('@PName', 'varchar(128)') as PName
	, c.value('@PValue', 'varchar(128)') as PVal
	from @p1.nodes('/node/P') as t(c)
)
select
 @int = max(case when PName = 'Name1' then cast(PVal as int) end),
 @vc = max(case when PName = 'Name2' then cast(PVal as varchar(128)) end),
 @dt = max(case when PName = 'Name3' then cast(PVal as datetime) end),
 @bit = max(case when PName = 'Name4' then cast(PVal as tinyint) end)
from nodes
select @int as _int, @vc as _vchar, @dt as _Dt, @bit as _bit

NL от cross apply по плану переместился в другое место, в остальном планы идентичны.
Надо будет увеличить XML и потестировать.
invm, спасибо.

AlanDenton,
Спасибо, интересный ход. Но не для работы - слишком жёсткая привязка к структуре XML. При добавлении атрибутов, если некоторые необязательны - уже можно набрать неприятностей.

Коллеги, может есть ещё какие идеи?
1 фев 16, 14:21    [18756567]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
любитель xml
Но Ваш ответ мною понят так, что у Вас критерием была минимизация вызовов XML Reader.
Не только. У вас еще есть фильтрация в XPath.
любитель xml
Надо будет увеличить XML и потестировать.
Потестировать что? И на маленьком XML разница видна более чем отчетливо.
1 фев 16, 14:56    [18756856]     Ответить | Цитировать Сообщить модератору
 Re: XQuery значения переменных из атрибутов разных элементов в один запрос  [new]
FOX75
Member

Откуда:
Сообщений: 29
Странно, если применить
автор
case when PName = 'Name1' then cast(PVal as int) else @int end

то все равно Stream Aggregate?
2 фев 16, 10:37    [18760163]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить