asiaindian


Hello,

I am looking to tune the below below query to run faster. any ideas would be appreciated.

I have a table with nText datatype column storing text data such as ...

'< xml version="1.0" encoding="utf-8" ><cd: SomeFile xmlns:cd="ABCD"><Server>Server1</Server><Object>dsObejct/IPhone.asmx</Object><Port>443</Port>

<TargetFolder>\\Server2</TargetFolder><TargetName>someName</TargetName>

<SomeKey>KeyName</SomeKey><SomeCode>Code</SomeCode><FileSize>11111111</FileSize>

</cd: SomeFile>'

My code is as below....

DECLARE @Data VARCHAR(5000)

SELECT @Data = nTextColumn FROM TABLE

DECLARE @XML AS XML, @SomeKey VARCHAR(100), @Status VARCHAR(50), @Center varchar(50)

SELECT @XML = CAST(@Data AS XML)

IF SUBSTRING(@Data,41,2) = 'cs'

BEGIN

SELECT @SomeKey = @XML.value('declare namespace cs="ABCD"; (/cs:ContentStatus/File/@SomeKey)[1]', 'varchar(500)')

SELECT @Center = @XML.value('declare namespace cs="ABCD"; (/cs:ContentStatus/File/DistributionCenter/@Name)[1]', 'varchar(50)')

END

What should I do to tune this query to run faster

Regards......





Re: XML query tuning

phe


How many records in the table How the PK/Index is defined I doubt the slowness is in getting the ntext column value into @Data variable.

If the column is of ntext, it's better to use ntext as the data type of the related variables, e.g. @Data.