January 16, 2013 at 7:58 am
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.
, LC.[TPR]
, LC.[PID]
,LC.[IND]
, LC.[DT]
, LC.[DTS]
, LC.[DTE]
, LC.[MS]
, LC.[NLSG]
, LC.[RI]
, LC.127.0.0.1
,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
,
REAL
, [TPR] REAL
, [PID] REAL
, [IND] FLOAT
, [DT] DATETIME
, [DTS] DATETIME
, [DTE] DATETIME
, [MS] VARCHAR(1)
, [NLSG] INT
, [RI] VARCHAR(1)
, 127.0.0.1 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
January 16, 2013 at 8:05 am
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
January 16, 2013 at 8:10 am
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
January 16, 2013 at 9:00 am
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
January 16, 2013 at 9:26 am
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
January 16, 2013 at 9:37 am
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
January 16, 2013 at 10:30 am
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
January 16, 2013 at 10:35 am
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
January 17, 2013 at 3:24 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply