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

OPENXML or nodes().... Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 7:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Hi,

Again with same doubt about using OPENXML or nodes()...
In previous tests I made with both methods, nodes() was faster mainly because of sp_xml_removedocument that takes a lot of time... The tests where made with XML based on elements not attributes: <line><product>10</product><qnt>10</qnt></line>. The XML rows where quite small, just 2 elements. (In attachment).

Then I had to go into the application database and the app sends a huge XML, based on attributes...
This is a sample of the XML sent.. I limited to 500 rows per document:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS


DECLARE @strXMLLinhas VARCHAR(MAX) = ''
DECLARE @strAux VARCHAR(MAX) ='<LC EBD=''2'' ID=''{156D19F8-45E4-11E2-9E5A-005056C00008}'' NDE=''1'' NL=''1'' TL=''65'' AR=''11.22420'' AZ=''102'' LO=''102'' DES=''PAVON LAMM-NAPPA BLACK'' QT=''-564'' UN=''PA'' FC=''1'' ARRED=''0'' VA=''0'' VB=''0'' VC=''0'' QTF=''0'' PU=''0'' D1=''0'' D2=''0'' D3=''0'' DC=''0'' CI=''23'' PII=''100'' TI=''23'' TR=''0'' TPR=''100'' PID=''100'' TIL=''0'' TDA=''0'' TDC=''0'' TDF=''0'' PL=''0'' EC=''0'' CIE='''' TIE=''0'' TX=''0'' TRE=''0'' TIVA=''0'' IND=''0'' DT=''12/14/2012'' DTE=''12/14/2012'' DTS=''12/14/2012 11:48:4'' MS=''S'' NLSG=''0'' RI=''0'' IML=''0'' IVL=''0'' SR=''0'' CAM=''0'' CAR=''0'' DI=''0'' DP=''0'' DD=''0'' B2BNLO=''0'' QTR=''0'' QTS=''0'' ET=''P'' FE=''0'' QTT=''-564'' NSE=''0'' REGIVA=''0'' TIIEC=''0'' TIEC=''0'' VIEC=''0'' AIVA=''0'' /> <LC EBD=''2'' ID=''{1D232D09-45E4-11E2-9E5A-005056C00008}'' IDLP=''{156D19F8-45E4-11E2-9E5A-005056C00008}'' NDE=''1'' NL=''2'' TL=''11'' AR=''11.22420.5.5'' AZ=''102'' LO=''102'' LT=''1200001.00'' DES=''PAVON LAMM-NAPPA BLACK(5.5)'' QT=''-1'' UN=''PA'' FC=''1'' ARRED=''0'' VA=''0'' VB=''0'' VC=''0'' QTF=''0'' PU=''-32'' D1=''0'' D2=''0'' D3=''0'' DC=''0'' CI=''23'' PII=''100'' TI=''23'' TR=''0'' TPR=''100'' PID=''100'' TIL=''-32'' TDA=''0'' TDC=''0'' TDF=''0'' PL=''-32'' EC=''0'' CIE='''' TIE=''0'' TX=''0'' TRE=''0'' TIVA=''-7.31999999999788'' IND=''0'' DT=''12/14/2012'' DTE=''12/14/2012'' DTS=''12/14/2012 11:48:4'' MS=''S'' NLSG=''0'' RI=''0'' IML=''0'' IVL=''0'' SR=''0'' CAM=''0'' CAR=''0'' DI=''0'' DP=''0'' DD=''0'' B2BNLO=''0'' QTR=''0'' QTS=''0'' ET=''P'' FE=''0'' QTT=''-1'' NSE=''0'' REGIVA=''0'' TIIEC=''0'' TIEC=''0'' VIEC=''0'' AIVA=''0'' /> <LC EBD=''2'' ID=''{1D232D08-45E4-11E2-9E5A-005056C00008}'' IDLP=''{156D19F8-45E4-11E2-9E5A-005056C00008}'' NDE=''1'' NL=''3'' TL=''11'' AR=''11.22420.6'' AZ=''102'' LO=''102'' LT=''1200001.00'' DES=''PAVON LAMM-NAPPA BLACK(6)'' QT=''-1'' UN=''PA'' FC=''1'' ARRED=''0'' VA=''0'' VB=''0'' VC=''0'' QTF=''0'' PU=''-17.450205'' D1=''0'' D2=''0'' D3=''0'' DC=''0'' CI=''23'' PII=''100'' TI=''23'' TR=''0'' TPR=''100'' PID=''100'' TIL=''-17.45'' TDA=''0'' TDC=''0'' TDF=''0'' PL=''-17.45'' EC=''0'' CIE='''' TIE=''0'' TX=''0'' TRE=''0'' TIVA=''-4.01'' IND=''0'' DT=''12/14/2012'' DTE=''12/14/2012'' DTS=''12/14/2012 11:48:4'' MS=''S'' NLSG=''0'' RI=''0'' IML=''0'' IVL=''0'' SR=''0'' CAM=''0'' CAR=''0'' DI=''0'' DP=''0'' DD=''0'' B2BNLO=''0'' QTR=''0'' QTS=''0'' ET=''P'' FE=''0'' QTT=''-1'' NSE=''0'' REGIVA=''0'' TIIEC=''0'' TIEC=''0'' VIEC=''0'' AIVA=''0'' /> <LC EBD=''2'' ID=''{1D232D07-45E4-11E2-9E5A-005056C00008}'' IDLP=''{156D19F8-45E4-11E2-9E5A-005056C00008}'' NDE=''1'' NL=''4'' TL=''11'' AR=''11.22420.6.5'' AZ=''102'' LO=''102'' LT=''1200001.00'' DES=''PAVON LAMM-NAPPA BLACK(6.5)'' QT=''-1'' UN=''PA'' FC=''1'' ARRED=''0'' VA=''0'' VB=''0'' VC=''0'' QTF=''0'' PU=''-32'' D1=''0'' D2=''0'' D3=''0'' DC=''0'' CI=''23'' PII=''100'' TI=''23'' TR=''0'' TPR=''100'' PID=''100'' TIL=''-32'' TDA=''0'' TDC=''0'' TDF=''0'' PL=''-32'' EC=''0'' CIE='''' TIE=''0'' TX=''0'' TRE=''0'' TIVA=''-7.36'' IND=''0'' DT=''12/14/2012'' DTE=''12/14/2012'' DTS=''12/14/2012 11:48:4'' MS=''S'' NLSG=''0'' RI=''0'' IML=''0'' IVL=''0'' SR=''0'' CAM=''0'' CAR=''0'' DI=''0'' DP=''0'' DD=''0'' B2BNLO=''0'' QTR=''0'' QTS=''0'' ET=''P'' FE=''0'' QTT=''-1'' NSE=''0'' REGIVA=''0'' TIIEC=''0'' TIEC=''0'' VIEC=''0'' AIVA=''0'' /> <LC EBD=''2'' ID=''{1D232D06-45E4-11E2-9E5A-005056C00008}'' IDLP=''{156D19F8-45E4-11E2-9E5A-005056C00008}'' NDE=''1'' NL=''5'' TL=''11'' AR=''11.22420.7'' AZ=''102'' LO=''102'' LT=''1200001.00'' DES=''PAVON LAMM-NAPPA BLACK(7)'' QT=''-1'' UN=''PA'' FC=''1'' ARRED=''0'' VA=''0'' VB=''0'' VC=''0'' QTF=''0'' PU=''-32'' D1=''0'' D2=''0'' D3=''0'' DC=''0'' CI=''23'' PII=''100'' TI=''23'' TR=''0'' TPR=''100'' PID=''100'' TIL=''-32'' TDA=''0'' TDC=''0'' TDF=''0'' PL=''-32'' EC=''0'' CIE='''' TIE=''0'' TX=''0'' TRE=''0'' TIVA=''-7.36'' IND=''0'' DT=''12/14/2012'' DTE=''12/14/2012'' DTS=''12/14/2012 11:48:3'' MS=''S'' NLSG=''0'' RI=''0'' IML=''0'' IVL=''0'' SR=''0'' CAM=''0'' CAR=''0'' DI=''0'' DP=''0'' DD=''0'' B2BNLO=''0'' QTR=''0'' QTS=''0'' ET=''P'' FE=''0'' QTT=''-1'' NSE=''0'' REGIVA=''0'' TIIEC=''0'' TIEC=''0'' VIEC=''0'' AIVA=''0'' />'

SELECT TOP 100 @strXMLLinhas = @strAux + @strXMLLinhas
FROM master..spt_values
WHERE spt_values.type='P'

SET @strXMLLinhas = '<Documento>' + @strXMLLinhas + '</Documento>'

SET STATISTICS IO ON
SET STATISTICS TIME ON

PRINT '---------- NODES() ------------'
DECLARE @xmlXMLLinhas XML = CAST(@strXmlLinhas AS XML)

SELECT
t.c.value('(@EBD)', 'SMALLINT') AS EBD,
t.c.value('@ID', 'UNIQUEIDENTIFIER') AS ID,
t.c.value('@IDLP', 'UNIQUEIDENTIFIER') AS IDLP,
t.c.value('@IDCC', 'UNIQUEIDENTIFIER') AS IDCC,
t.c.value('@NDE', 'VARCHAR(20)') AS NDE,
t.c.value('@NL', 'INT') AS NL,
t.c.value('@TL', 'VARCHAR(2)') AS TL,
t.c.value('@AR', 'VARCHAR(48)') AS AR,
t.c.value('@AZ', 'VARCHAR(5)') AS AZ,
t.c.value('@LO', 'VARCHAR(30)') AS LO,
t.c.value('@LT', 'VARCHAR(20)') AS LT,
t.c.value('@DES', 'VARCHAR(512)') AS DES,
t.c.value('@QT', 'FLOAT') AS QT,
t.c.value('@UN', 'VARCHAR(5)') AS UN,
t.c.value('@FC', 'FLOAT') AS FC,
t.c.value('@ARRED', 'INT') AS ARRED,
t.c.value('@FOR', 'VARCHAR(5)') AS [FOR],
t.c.value('@VA', 'FLOAT') AS VA,
t.c.value('@VB ', 'FLOAT') AS VB ,
t.c.value('@VC ', 'FLOAT') AS VC ,
t.c.value('@QTF ', 'FLOAT') AS QTF ,
t.c.value('@PU ', 'FLOAT') AS PU ,
t.c.value('@D1 ', 'REAL') AS D1 ,
t.c.value('@D2 ', 'REAL') AS D2 ,
t.c.value('@D3 ', 'REAL') AS D3 ,
t.c.value('@DC ', 'FLOAT') AS DC ,
t.c.value('@PL ', 'FLOAT') AS PL ,
t.c.value('@TIL ', 'FLOAT') AS TIL ,
t.c.value('@TDA ', 'FLOAT') AS TDA ,
t.c.value('@TDC ', 'FLOAT') AS TDC ,
t.c.value('@TDF ', 'FLOAT') AS TDF ,
t.c.value('@TRE ', 'FLOAT') AS TRE ,
t.c.value('@TIVA ', 'FLOAT') AS TIVA ,
t.c.value('@CAM ', 'FLOAT') AS CAM ,
t.c.value('@CAR ', 'FLOAT') AS CAR ,
t.c.value('@DI ', 'FLOAT') AS DI ,
t.c.value('@DP ', 'FLOAT') AS DP ,
t.c.value('@DD ', 'FLOAT') AS DD ,
t.c.value('@CI ', 'VARCHAR(2)') AS CI ,
t.c.value('@TI ', 'REAL') AS TI ,
t.c.value('@PII ', 'REAL') AS PII ,
t.c.value('@TR ', 'REAL') AS TR ,
t.c.value('@TPR ', 'REAL') AS TPR ,
t.c.value('@PID ', 'REAL') AS PID ,
t.c.value('@IND ', 'FLOAT') AS IND ,
t.c.value('@DT ', 'DATETIME') AS DT ,
t.c.value('@DTS ', 'DATETIME') AS DTS ,
t.c.value('@DTE ', 'DATETIME') AS DTE ,
t.c.value('@MS ', 'VARCHAR(1)') AS MS ,
t.c.value('@NLSG ', 'INT') AS NLSG ,
t.c.value('@RI ', 'VARCHAR(1)') AS RI ,
t.c.value('@IP ', 'VARCHAR(8)') AS IP ,
t.c.value('@IML ', 'FLOAT') AS IML ,
t.c.value('@IR ', 'VARCHAR(2)') AS IR ,
t.c.value('@IVL ', 'FLOAT') AS IVL ,
t.c.value('@SR ', 'BIT') AS SR ,
t.c.value('@OID ', 'UNIQUEIDENTIFIER') AS OID ,
t.c.value('@WBSI ', 'VARCHAR(100)') AS WBSI ,
t.c.value('@OIID ', 'INT') AS OIID ,
t.c.value('@IDCOD ', 'VARCHAR(20)') AS IDCOD ,
t.c.value('@IDESC ', 'VARCHAR(4000)') AS IDESC ,
t.c.value('@CID ', 'INT') AS CID ,
t.c.value('@SEID ', 'INT') AS SEID ,
t.c.value('@CATID ', 'INT') AS CATID ,
t.c.value('@TA ', 'VARCHAR(3)') AS TA ,
t.c.value('@AID ', 'UNIQUEIDENTIFIER') AS AID ,
t.c.value('@MOC ', 'VARCHAR(1)') AS MOC ,
t.c.value('@IDLOC ', 'UNIQUEIDENTIFIER') AS IDLOC ,
t.c.value('@CCBL ', 'VARCHAR(20)') AS CCBL ,
t.c.value('@CCCBL ', 'VARCHAR(20)') AS CCCBL ,
t.c.value('@ACBL ', 'VARCHAR(20)') AS ACBL ,
t.c.value('@FCBL ', 'VARCHAR(20)') AS FCBL ,
t.c.value('@QTR ', 'FLOAT') AS QTR ,
t.c.value('@QTS ', 'FLOAT') AS QTS ,
t.c.value('@ET ', 'VARCHAR(1)') AS ET ,
t.c.value('@FE ', 'BIT') AS FE ,
t.c.value('@IDLCO ', 'UNIQUEIDENTIFIER') AS IDLCO ,
t.c.value('@IDLRI ', 'UNIQUEIDENTIFIER') AS IDLRI ,
t.c.value('@QTT ', 'FLOAT') AS QTT ,
t.c.value('@TNS ', 'BIT') AS TNS ,
t.c.value('@TLO ', 'BIT') AS TLO ,
t.c.value('@NSE ', 'INT') AS NSE ,
t.c.value('@IDB2BLO', 'UNIQUEIDENTIFIER') AS IDB2BLO,
t.c.value('@B2BNLO ', 'INT') AS B2BNLO ,
t.c.value('@EC ', 'FLOAT') AS EC ,
t.c.value('@CIE', ' VARCHAR(2)') AS CIE,
t.c.value('@TIE ', 'REAL') AS TIE ,
t.c.value('@TX ', 'FLOAT') AS TX ,
t.c.value('@REGIVA ', 'SMALLINT') AS REGIVA ,
t.c.value('@MOTEST ', 'VARCHAR(3)') AS MOTEST ,
t.c.value('@IDLEST ', 'UNIQUEIDENTIFIER') AS IDLEST ,
t.c.value('@IDH ', 'UNIQUEIDENTIFIER') AS IDH ,
t.c.value('@EPEN ', 'VARCHAR(4)') AS EPEN ,
t.c.value('@CIIEC ', 'VARCHAR(2)') AS CIIEC ,
t.c.value('@TIIEC ', 'FLOAT') AS TIIEC ,
t.c.value('@TIEC ', 'FLOAT') AS TIEC ,
t.c.value('@VIEC ', 'FLOAT ') AS VIEC ,
t.c.value('@TO', 'VARCHAR(2)') AS [TO],
t.c.value('@AIVA ', 'FLOAT') AS AIVA
INTO #Garbage
FROM @xmlXMLLinhas.nodes('/Documento/LC') t(c)
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
DROP TABLE #Garbage

SET STATISTICS IO ON
SET STATISTICS TIME ON

PRINT '------------- OPENXML ---------------------'
DECLARE @intLinhas INT
EXEC sp_xml_preparedocument @intLinhas OUTPUT, @strXmlLinhas
SELECT LC.[EBD]
, LC.[ID]
, LC.[IDLP]
, [IDCC] = NEWID()
, LC.[NDE]
, LC.[NL]
, LC.[TL]
, LC.[AR]
, LC.[AZ]
, LC.[LO]
, LC.[LT]
, LC.[DES]
, LC.[QT]
, LC.[UN]
, LC.[FC]
, LC.[FOR]
, LC.[VA]
, LC.[VB]
, LC.[VC]
, LC.[QTF]
, LC.[PU]
, LC.[D1]
, LC.[D2]
, LC.[D3]
, LC.[DC]
, LC.[PL]
, LC.[TIL]
, LC.[TDA]
, LC.[TDC]
, LC.[TDF]
, LC.[TRE]
, LC.[TIVA]
, LC.[CAM]
, LC.[CAR]
, LC.[DI]
, LC.[DP]
, LC.[DD]
, LC.[CI]
, LC.[TI]
, LC.[PII]
, LC.[TR]
, LC.[TPR]
, LC.[PID]
, LC.[IND]
, LC.[DT]
, LC.[DTS]
, LC.[DTE]
, LC.[MS]
, LC.[NLSG]
, LC.[RI]
, LC.[IP]
, LC.[IML]
, LC.[IR]
, LC.[IVL]
, LC.[SR]
, LC.[OID]
, LC.[WBSI]
, LC.[OIID]
, LC.[IDCOD]
, LC.[IDESC]
, LC.[CID]
, LC.[SEID]
, LC.[CATID]
, LC.[TA]
, LC.[AID]
, LC.[MOC]
, LC.[IDLOC]
, LC.[CCBL]
, LC.[CCCBL]
, LC.[ACBL]
, LC.[FCBL]
, LC.[QTR]
, LC.[QTS]
, LC.[ET]
, LC.[FE]
, LC.[IDLCO]
, LC.[IDLRI]
, LC.[QTT]
, LC.[IDB2BLO]
, LC.[B2BNLO]
, LC.[EC]
, LC.[CIE]
, LC.[TIE]
, LC.[TX]
, LC.[REGIVA]
, LC.[MOTEST]
, LC.[IDLEST]
, LC.[IDH]
, LC.[EPEN]
, LC.[CIIEC]
, LC.[TIIEC]
, LC.[TIEC]
, LC.[VIEC]
, LC.[TO]
, LC.[AIVA]
, LC.[NSE]
INTO #Garbage0
FROM OPENXML (@intLinhas, 'Documento/LC')
WITH
( [EBD] SMALLINT
, [ID] UNIQUEIDENTIFIER
, [IDLP] UNIQUEIDENTIFIER
, [IDCC] UNIQUEIDENTIFIER
, [NDE] VARCHAR(20)
, [NL] INT
, [TL] VARCHAR(2)
, [AR] VARCHAR(48)
, [AZ] VARCHAR(5)
, [LO] VARCHAR(30)
, [LT] VARCHAR(20)
, [DES] VARCHAR(512)
, [QT] FLOAT
, [UN] VARCHAR(5)
, [FC] FLOAT
, [ARRED] INT
, [FOR] VARCHAR(5)
, [VA] FLOAT
, [VB] FLOAT
, [VC] FLOAT
, [QTF] FLOAT
, [PU] FLOAT
, [D1] REAL
, [D2] REAL
, [D3] REAL
, [DC] FLOAT
, [PL] FLOAT
, [TIL] FLOAT
, [TDA] FLOAT
, [TDC] FLOAT
, [TDF] FLOAT
, [TRE] FLOAT
, [TIVA] FLOAT
, [CAM] FLOAT
, [CAR] FLOAT
, [DI] FLOAT
, [DP] FLOAT
, [DD] FLOAT
, [CI] VARCHAR(2)
, [TI] REAL
, [PII] REAL
, [TR] REAL
, [TPR] REAL
, [PID] REAL
, [IND] FLOAT
, [DT] DATETIME
, [DTS] DATETIME
, [DTE] DATETIME
, [MS] VARCHAR(1)
, [NLSG] INT
, [RI] VARCHAR(1)
, [IP] VARCHAR(8)
, [IML] FLOAT
, [IR] VARCHAR(2)
, [IVL] FLOAT
, [SR] BIT
, [OID] UNIQUEIDENTIFIER
, [WBSI] VARCHAR(100)
, [OIID] INT
, [IDCOD] VARCHAR(20)
, [IDESC] VARCHAR(4000)
, [CID] INT
, [SEID] INT
, [CATID] INT
, [TA] VARCHAR(3)
, [AID] UNIQUEIDENTIFIER
, [MOC] VARCHAR(1)
, [IDLOC] UNIQUEIDENTIFIER
, [CCBL] VARCHAR(20)
, [CCCBL] VARCHAR(20)
, [ACBL] VARCHAR(20)
, [FCBL] VARCHAR(20)
, [QTR] FLOAT
, [QTS] FLOAT
, [ET] VARCHAR(1)
, [FE] BIT
, [IDLCO] UNIQUEIDENTIFIER
, [IDLRI] UNIQUEIDENTIFIER
, [QTT] FLOAT
, [TNS] BIT
, [TLO] BIT
, [NSE] INT
, [IDB2BLO] UNIQUEIDENTIFIER
, [B2BNLO] INT
, [EC] FLOAT
, [CIE] VARCHAR(2)
, [TIE] REAL
, [TX] FLOAT
, [REGIVA] SMALLINT
, [MOTEST] VARCHAR(3)
, [IDLEST] UNIQUEIDENTIFIER
, [IDH] UNIQUEIDENTIFIER
, [EPEN] VARCHAR(4)
, [CIIEC] VARCHAR(2)
, [TIIEC] FLOAT
, [TIEC] FLOAT
, [VIEC] FLOAT
, [TO] VARCHAR(2)
, [AIVA] FLOAT
) LC

EXEC sp_xml_removedocument @intLinhas
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
DROP TABLE #Garbage0

The data is inserted in a temp table since it has lots of data (better than table variables) and it's used in other SPs called from the main one.
The results from this test is quite different from the one in the attachment:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
---------- NODES() ------------

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 46 ms.

SQL Server Execution Times:
CPU time = 1872 ms, elapsed time = 2126 ms.

(500 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
------------- OPENXML ---------------------

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 95 ms.

SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 574 ms.

(500 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Are these results due to this new XML having lots of columns? Is OPENXML faster in these cases?
Even if it is, is it wise to use OPENXML on a "large" system where lots of users can be doing this operation (opening many XML documents with OPENXML), since OPENXML stores the XML data in memory?

Thanks,
Pedro




If you need to work better, try working less...


  Post Attachments 
XmlTests.txt (1 view, 1.86 KB)
Post #1407846
Posted Wednesday, January 16, 2013 8:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Personally, I use XQuery (nodes, et al) consistently. Haven't used OpenXML in years. Prefer XQuery for performance, ease-of-use, etc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1407853
Posted Wednesday, January 16, 2013 8:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
GSquared (1/16/2013)
Personally, I use XQuery (nodes, et al) consistently. Haven't used OpenXML in years. Prefer XQuery for performance, ease-of-use, etc.


I use it also but in this example OPENXML is 4x faster getting the data...
It's probably cause the XML has lots of columns in the output.. If I just use 10 columns the nodes is faster.
In the example sent, with just 10 columns on the output, nodes is 2.5x faster...

Thanks,
Pedro




If you need to work better, try working less...
Post #1407860
Posted Wednesday, January 16, 2013 9:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
PiMané (1/16/2013)
GSquared (1/16/2013)
Personally, I use XQuery (nodes, et al) consistently. Haven't used OpenXML in years. Prefer XQuery for performance, ease-of-use, etc.


I use it also but in this example OPENXML is 4x faster getting the data...
It's probably cause the XML has lots of columns in the output.. If I just use 10 columns the nodes is faster.
In the example sent, with just 10 columns on the output, nodes is 2.5x faster...

Thanks,
Pedro


The usual rule applies then. Use whatever works better. These things are very situationally dependent, so OpenXML may very well be your best bet in this case.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1407902
Posted Wednesday, January 16, 2013 9:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
My only concern is "what's said" about OPENXML... that every time it's used it gets 1/8 of the server resources...
So if 10 people use it the server has 10/8 of its resources used!!!

Pedro




If you need to work better, try working less...
Post #1407927
Posted Wednesday, January 16, 2013 9:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
From what I recal, it is expensive that way. Can cause SQL Server to page-out RAM to disk. It's been years since I used it, so I could be misremembering the details. Do you have a test environment that you can safely overload that way?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1407940
Posted Wednesday, January 16, 2013 10:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
GSquared (1/16/2013)
From what I recal, it is expensive that way. Can cause SQL Server to page-out RAM to disk. It's been years since I used it, so I could be misremembering the details. Do you have a test environment that you can safely overload that way?


I used SqlQueryStress with 5 iterations and 10 threads and got no error... And the times were as expected..
Nodes() is faster with less columns but OPENXML is way faster with more columns... Funny that it doesn't matter the columns defined on the WITH clause from OPENXML (always defined all in both cases), it only matters the columns on the SELECT output...
Sent an image with the results....

Thanks,
Pedro




If you need to work better, try working less...


  Post Attachments 
querystress.jpg (2 views, 355.42 KB)
Post #1407991
Posted Wednesday, January 16, 2013 10:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
PiMané (1/16/2013)
GSquared (1/16/2013)
From what I recal, it is expensive that way. Can cause SQL Server to page-out RAM to disk. It's been years since I used it, so I could be misremembering the details. Do you have a test environment that you can safely overload that way?


I used SqlQueryStress with 5 iterations and 10 threads and got no error... And the times were as expected..
Nodes() is faster with less columns but OPENXML is way faster with more columns... Funny that it doesn't matter the columns defined on the WITH clause from OPENXML (always defined all in both cases), it only matters the columns on the SELECT output...
Sent an image with the results....

Thanks,
Pedro


Definitely interesting. Looks to me like you can go with OpenXML in this case.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1407993
Posted Thursday, January 17, 2013 3:24 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Just some more extra info
I was trying to get to the number of columns where both performed equally.. In SSMS, 1 run each, the number was 35 columns output... 600ms..
But when using SQLQueryStress with 10 iterations and 10 threads with the 35 columns, nodes() completely smashes OPENXML. Total time for nodes() is 41s, for OPENXML 1m:04s... With 1 thread the times are identical...
With 50 columns (10 it, 10 thrd) OPENXML takes the same time and nodes() takes 1m:11s. But the CPU s/iteration (avg) is for OPENXML 0,65s and nodes() 0,95seg, Actual sec/iteration (avg) OPENXML 5,3s and nodes() 1,65s.... Client sec/iteration (avg) OPENXML 5,41s and nodes() 2,74s.

These are weird times.. how is it possible for each iteration for OPENXML take longer but the overall is less...
Can this be a test environment issue?

Thanks,
Pedro

PS:
SQL Server service priority is set to High... times are completely different if priority is normal..
With all the columns (100) the times are, for 10 iterations 10 threads:
OPENXML > CPU: 0,68 Actual: 5.45 Client: 5.54 Total: 1m
nodes() > CPU: 2s Actual: 6s Client: 8s Total 2m.

With all the columns (100) the times are, for 5 iterations 5 threads:
OPENXML > CPU: 0,62 Actual: 0.88 Client: 1.15 Total: 10s
nodes() > CPU: 2s Actual: 2.34s Client: 3.73s Total 32s





If you need to work better, try working less...
Post #1408275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse