SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Clarification on OpenXML Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nvinothan
Starting Member

India
1 Posts

Posted - 04/11/2013 :  11:10:29  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/12/2013 :  04:52:40  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000