InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 04:18:04 GMT20http://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspxJust 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,PedroPS: 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: 1mnodes() > 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: 10snodes() > CPU: 2s Actual: 2.34s Client: 3.73s Total 32sThu, 17 Jan 2013 03:24:10 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspx[quote][b]PiMané (1/16/2013)[/b][hr][quote][b]GSquared (1/16/2013)[/b][hr]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?[/quote]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[/quote]Definitely interesting. Looks to me like you can go with OpenXML in this case.Wed, 16 Jan 2013 10:35:09 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspx[quote][b]GSquared (1/16/2013)[/b][hr]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?[/quote]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,PedroWed, 16 Jan 2013 10:30:56 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspxFrom 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?Wed, 16 Jan 2013 09:37:53 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspxMy 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!!! PedroWed, 16 Jan 2013 09:26:23 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspx[quote][b]PiMané (1/16/2013)[/b][hr][quote][b]GSquared (1/16/2013)[/b][hr]Personally, I use XQuery (nodes, et al) consistently. Haven't used OpenXML in years. Prefer XQuery for performance, ease-of-use, etc.[/quote]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[/quote]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.Wed, 16 Jan 2013 09:00:14 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspx[quote][b]GSquared (1/16/2013)[/b][hr]Personally, I use XQuery (nodes, et al) consistently. Haven't used OpenXML in years. Prefer XQuery for performance, ease-of-use, etc.[/quote]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,PedroWed, 16 Jan 2013 08:10:21 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspxPersonally, I use XQuery (nodes, et al) consistently. Haven't used OpenXML in years. Prefer XQuery for performance, ease-of-use, etc.Wed, 16 Jan 2013 08:05:20 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1407846-392-1.aspxHi,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:[code="sql"]DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSDECLARE @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 + @strXMLLinhasFROM master..spt_valuesWHERE spt_values.type='P'SET @strXMLLinhas = '<Documento>' + @strXMLLinhas + '</Documento>'SET STATISTICS IO ONSET STATISTICS TIME ONPRINT '---------- 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 #GarbageFROM @xmlXMLLinhas.nodes('/Documento/LC') t(c)SET STATISTICS IO OFFSET STATISTICS TIME OFFDROP TABLE #GarbageSET STATISTICS IO ONSET STATISTICS TIME ONPRINT '------------- OPENXML ---------------------'DECLARE @intLinhas INTEXEC 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 @intLinhasSET STATISTICS IO OFFSET STATISTICS TIME OFFDROP TABLE #Garbage0[/code]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:[code="other"]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.[/code]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,PedroWed, 16 Jan 2013 07:58:46 GMTPiMané