SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OPENXML or nodes()....


OPENXML or nodes()....

Author
Message
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
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...
Attachments
XmlTests.txt (9 views, 1.00 KB)
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23351 Visits: 9730
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
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
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...
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23351 Visits: 9730
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
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
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...
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23351 Visits: 9730
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
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
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...
Attachments
querystress.jpg (6 views, 355.00 KB)
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23351 Visits: 9730
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
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search