当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2005 - Accessing Xml Parameters Efficiently in a Stored Procedure

问题描述:

I am trying to optimize a stored procedure that relies on a few XML type parameters for variable input. Analysis of the execution plan shows a high cost associated with accessing the data in these parameters.

Given:

DECLARE @FirstNameXML XML;

In the format of

<FirstNames>

<Name>John</Name>

<Name>Joe</Name>

</FirstNames>

There are a few operations I need to perform (assuming the parameter is non-null):

Count of Names supplied

SET @FirstNameCount =

(SELECT COUNT(FirstNameValues.Name.value('.','VARCHAR(50)'))

FROM @FirstNameXML.nodes('/FirstNames/Name')

AS FirstNameValues(Name))

If count = 1

...

AND First_Name LIKE

(SELECT TOP(1) FirstNameValues.Name.value('.','VARCHAR(50)') + '%'

FROM @FirstNameXML.nodes('/FirstNames/Name')

AS FirstNameValues(Name))

...

Else if count > 1

...

AND First_Name IN

(SELECT FirstNameValues.Name.value('.','VARCHAR(50)')

FROM @FirstNameXML.nodes('/FirstNames/Name')

AS FirstNameValues(Name))

...

I have tried a few optimizations:

Changing the count query to:

SET @FirstNameCount =

(SELECT CAST(CAST(@FirstNameXML.query('count(/FirstNames/Name)')

AS VARCHAR(10)) AS INT)

Changing the count == 1 query to:

...

AND First_Name LIKE

(SELECT @FirstNameXML.value('(/FirstNames/Name)[1]', 'VARCHAR(50)') + '%')

...

Somehow the stored procedure runs even slower after the optimizations even though the changes seem to have reduced the cost in the execution plan. This leaves me with a few questions:

  1. Am I misunderstanding the optimizations I've implemented?
  2. Is there a more efficient way to go about this as a whole? (XML parameter based querying)

网友答案:

Add the rows from the XML to a table variable or a temporary table. Make sure there is an index on the Name column in the table variable/temp table. I also don't see the need to have different queries for when count is 1 or many. This way you will only parse your XML one time.

Note. The execution plan costs is not to be trusted when it comes to XML. Better to test with set statistics time on and measure the actual performance instead.

-- Test data
declare @FirstNameXML xml;
set @FirstNameXML = 
'<FirstNames>
  <Name>John</Name>
  <Name>Joe</Name>
</FirstNames>'

-- Put your data in a table variable.
-- Have Name as a primary key and you will get an index
declare @T table
(
  Name varchar(50) primary key
)

-- Add rows from XML to @T
insert into @T(Name)
select distinct T.N.value('.', 'varchar(50)')
from @FirstNameXML.nodes('FirstNames/Name') as T(N)

select *
from YourTable
where First_Name in (select Name from @T)
分享给朋友:
您可能感兴趣的文章:
随机阅读: