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

query xml Expand / Collapse
Author
Message
Posted Sunday, May 26, 2013 9:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:21 AM
Points: 41, Visits: 65
i have a large xml file
one of my queries looks like:
SELECT
p.value('(PodactID)[1]', 'nvarchar(max)'),
q.value('(CustomerID)[1]', 'nvarchar(max)'),
r.value('(IsPaid)[1]', 'int'),
r.value('(Payment)[1]', 'real'),
r.value('(PaymentDate)[1]', 'nvarchar(max)')
FROM XmlTable CROSS APPLY XML_DATA.nodes('Customers/Customer/Podact/PodactBills/PodactsBill') t(p)
CROSS APPLY p.nodes('Payments/PaymentDetails[CustomerID]') a(q)
CROSS APPLY r.nodes('PaymentsDetails[IsPaid]') b(r)

how can i improve the query duration, because it takes lot of time?

thanks
Post #1456868
Posted Tuesday, May 28, 2013 10:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
Process large xml in chunks.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1457439
Posted Tuesday, May 28, 2013 1:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:21 AM
Points: 41, Visits: 65
any idea how to divide the xml to chunks?
Post #1457491
Posted Tuesday, May 28, 2013 4:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 1,780, Visits: 5,750
Do you really need your PodactID, CustomerID and PaymentDate to be nvarchar(MAX) data types? I doubt it - use the correct data type for these.

When you query an element value, do it like this : '(mynode/text())[1]', not this '(mynode)[1]'

What is going on with those cross apply clauses ? r.nodes is invalid here as the only "r" declared is the result of the r.nodes clause!!

How many rows are in your table? How big is each XML_DATA value? Do you have suitable indexes and a WHERE clause?


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1457542
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse