table valued function xml reader high cost

Question

Post reply

table valued function xml reader high cost

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
March 6, 2014 at 3:14 am

#283067

Hi All,
I have a xml and i am inserting data into one temp table from xml.
this cost is very high.can u please give your valuable suggestions to reduce this xml reader cost.
Please find the actual execution plan.
Original query:
---XML
DECLARE @entityIds XML
SET @entityIds =N'<?xml version="1.0" encoding="utf-16"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<int>112350</int>
<int>100886642</int>
</ArrayOfInt>'
---INSERT INTO TEMP TABLE
SELECT Tbl.Col.value('.', 'int') AS val
into #entityIdList
FROM @entityIds.nodes('/ArrayOfInt/int') Tbl(Col)
---SELECT DATA FROM TEMP TABLE
select * FROM #entityIdList

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 1

i used like this query hint now i got some performance improvement.

please let me know is it valid or not ?

---XML

DECLARE @entityIds XML

SET @entityIds =N'<?xml version="1.0" encoding="utf-16"?>

</ArrayOfInt>'

---INSERT INTO TEMP TABLE

INSERT INTO #entityIdList

SELECT Tbl.Col.value('.', 'int') AS val

FROM @entityIds.nodes('/ArrayOfInt/int') Tbl(Col)

OPTION ( OPTIMIZE FOR ( @entityIds = null) )

---SELECT DATA FROM TEMP TABLE

select * FROM #entityIdList

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 2

please find the code in detail

IF OBJECT_ID('tempdb..#entityIdList') IS NOT NULL

drop TABLE #entityIdList

create TABLE #entityIdList

(

val INT

)

---XML

DECLARE @entityIds XML

SET @entityIds =N'<?xml version="1.0" encoding="utf-16"?>

</ArrayOfInt>'

---INSERT INTO TEMP TABLE

INSERT INTO #entityIdList

SELECT Tbl.Col.value('.', 'int') AS val

FROM @entityIds.nodes('/ArrayOfInt/int') Tbl(Col)

OPTION ( OPTIMIZE FOR ( @entityIds = null) )

---SELECT DATA FROM TEMP TABLE

select * FROM #entityIdList

lnardozi 61862 SSCommitted Points: 1931 More actions · Answer 3

For some reason you don't need the hint if you do it like this.

DECLARE @entityIds XML

SET @entityIds =N'<?xml version="1.0" encoding="utf-16"?>

<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<int>112350</int>

<int>100886642</int>

</ArrayOfInt>'

---INSERT INTO TEMP TABLE

SELECT Tbl.Col.value('.', 'int') AS val

into #entityIdList

FROM @entityIds.nodes('/ArrayOfInt/int/text()') Tbl(Col)

--OPTION ( OPTIMIZE FOR ( @entityIds = null ) )

---SELECT DATA FROM TEMP TABLE

select * FROM #entityIdList

drop table #entityIdList

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 4

HI All,

Thank you for your help.

but i need to explain this change.

FROM @entityIds.nodes('/ArrayOfInt/int/text()') Tbl(Col)

can you please explain me why we change