Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

table valued function xml reader high cost Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 3:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:56 AM
Points: 983, Visits: 289
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


  Post Attachments 
exec plan.sqlplan (5 views, 32.91 KB)
more details.png (2 views, 77.01 KB)
Post #1548129
Posted Thursday, March 6, 2014 5:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:56 AM
Points: 983, Visits: 289
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 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
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
Post #1548220
Posted Thursday, March 6, 2014 5:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:56 AM
Points: 983, Visits: 289
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 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
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
Post #1548221
Posted Thursday, March 6, 2014 2:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
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

Post #1548497
Posted Monday, March 10, 2014 1:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:56 AM
Points: 983, Visits: 289
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
Post #1549119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse