Clarification on OpenXML Performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Clarification on OpenXML Performance

Author

Topic

nvinothan
Starting Member

1 Post

Posted - 2013-04-11 : 11:10:29

Can someone please guide me for my below clarification.

Basically I want to querying bulk data instead of hiting SQL DB each and every time.Hence i tried with the below openxml concept

DECLARE @XMLDocPointer INT , @InputXML XML
SET @InputXML ='<DELIVER_ORDER>
<DELIVERY>
<ORDER_NO>11399925</ORDER_NO>
<LINE_NO>1</LINE_NO>
<REL_NO>1</REL_NO>
</DELIVERY>
</DELIVER_ORDER>'
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @InputXML
SELECT OD.ORDER_NO,OD>LINE_NO,OD>REL_NO from ORDERDELIVERY OD
INNER JOIN OPENXML (@XMLDocPointer, '/DELIVER_ORDER/DELIVERY', 2) WITH (ORDER_NO varchar(50), LINE_NO varchar(50), REL_NO varchar(50), LINE_GUID varchar(200)) xmldt on (OD.ORDER_NO = xmldt.ORDER_NO) AND (OD>LINE_NO = xmldt.LINE_NO) AND (OD>REL_NO = xmldt.REL_NO)
EXEC sp_xml_removedocument @XMLDocPointer

Actually i am calling this above statement from .NET Application.

But after i read many artilces i have a little confusion on using OPENXML. like each time sp_xml_preparedocument takes 1/8 of main memory of the server to prepare a xml document.

Our scenario is like, more than 20 users will use our application simultaneously, that means there is a chance of executing the above query more than one time simultaneously.

if that is the case, will it claim more memory ? will it happen memory issue or performance ?

if it is, can you please guide me which other approach to querying bulk data (may be by using temp table not sure)

Please advise

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 04:52:40

why are you using OPENXML

Because you're on SQL 2005 or above as you use XML data type, you can use native XML functions like nodes() ,query() etc to get data you want

see illustration below


DECLARE  @InputXML XML 
SET @InputXML ='<DELIVER_ORDER>
<DELIVERY>
<ORDER_NO>11399925</ORDER_NO>
<LINE_NO>1</LINE_NO>
<REL_NO>1</REL_NO>
</DELIVERY>
</DELIVER_ORDER>'

SELECT OD.ORDER_NO,OD.LINE_NO,OD.REL_NO 
from ORDERDELIVERY OD 
INNER JOIN (SELECT t.u.value('ORDER_NO[1]','int') AS ORDER_NO,
t.u.value('LINE_NO[1]','int') AS LINE_NO,
t.u.value('REL_NO[1]','int') AS REL_NO
FROM @InputXML.nodes('/DELIVER_ORDER/DELIVERY') t(u)) xmldt 
on (OD.ORDER_NO = xmldt.ORDER_NO) AND (OD.LINE_NO = xmldt.LINE_NO) AND (OD.REL_NO = xmldt.REL_NO)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources