Switched From OPENXML to .nodes T(Item) // Performance is four times slower

Question

Post reply

Switched From OPENXML to .nodes T(Item) // Performance is four times slower

sholliday

SSCrazy

Points: 2100
More actions
April 28, 2008 at 11:43 am

#189944

I switched over a stored procedure FROM "OPENXML" to a more Sql Server 2005 syntax.
My performance shot down 4-10 times. As in, a heck of alot slower.
I created a small test, seen below. It performs about 3 times slower.
Can anyone provide some insight to this?
I've read a couple of places that OPENXML is not the preferred choice in SqlServer2005.
http://blogs.sqlservercentral.com/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx
for example.
I wouldn't mind if it performed about the same. But ouch, its alot slower.
I get the following results:
OPENXML ms
2360
2005 Syntax ms
6606
As you can see, from this very simple test, the new syntax is 3X slower, on
this very very very basic xml document. My xml is similar, 10 tables at 10
rows max each. Overall size of the xml is <100KB.
If you uncomment the following lines in (each of) the usp (stored
procedures), you'll see they do EXACTLY the same thing.
Just parse and push the data into some temp tables.
/*
--these are here just to show the parsing works..commented out.
select * from @EmployeeUpdate
select * from @DepartmentUpdate
select * from @JobTitleUpdate
select * from @EmployeeToJobTitleLinkUpdate
*/
Here are 2 stored procedures, and the test code at the bottom. The xml is
exactly the same.
--TSQL START HERE
print '[uspParseXmlWithOPENXML]'
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspParseXmlWithOPENXML]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspParseXmlWithOPENXML]
Go
CREATE PROCEDURE [dbo].[uspParseXmlWithOPENXML] (
@xml_doc TEXT
)
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @EmployeeUpdate TABLE (
EntryID int IDENTITY (1,1),
EmployeeUUID uniqueidentifier,
DepartmentUUID uniqueidentifier,
LastName varchar(64) ,
FirstName varchar(64) ,
IsPermanent bit,
HireDate smalldatetime
,AlreadyExists bit
)
DECLARE @DepartmentUpdate TABLE (
EntryID int IDENTITY (1,1),
DepartmentUUID uniqueidentifier,
DepartmentName varchar(64)
,AlreadyExists bit
)
DECLARE @JobTitleUpdate TABLE (
EntryID int IDENTITY (1,1),
JobTitleUUID uniqueidentifier,
JobTitleName varchar(64)
,AlreadyExists bit
)
DECLARE @EmployeeToJobTitleLinkUpdate TABLE (
EntryID int IDENTITY (1,1),
EmployeeUUID uniqueidentifier,
JobTitleUUID uniqueidentifier
)
--the next call will take the info IN the @hdoc(with is the holder for @xml_doc), and put it IN a variableTable
INSERT @EmployeeUpdate
(
EmployeeUUID,
DepartmentUUID,
LastName,
FirstName,
IsPermanent,
HireDate
,AlreadyExists
)
SELECT
EmployeeUUID,
DepartmentUUID,
LastName,
FirstName,
IsPermanent,
HireDate
, 0
FROM
OPENXML (@hdoc, '/OrganizationDS/Employee', 2) WITH (
EmployeeUUID uniqueidentifier './EmployeeUUID' ,
DepartmentUUID uniqueidentifier './DepartmentUUID' ,
LastName varchar(64) './LastName' ,
FirstName varchar(64) './FirstName' ,
IsPermanent varchar(8) './IsPermanent' ,
HireDate varchar(64) './HireDate'
,AlreadyExists bit
)
INSERT @DepartmentUpdate
(
DepartmentUUID,
DepartmentName
,AlreadyExists
)
SELECT
DepartmentUUID,
DepartmentName
, 0
FROM
OPENXML (@hdoc, '/OrganizationDS/Department', 2) WITH (
DepartmentUUID uniqueidentifier './DepartmentUUID' ,
DepartmentName varchar(64) './DepartmentName'
,AlreadyExists bit
)
INSERT @JobTitleUpdate
(
JobTitleUUID,
JobTitleName
,AlreadyExists
)
SELECT
JobTitleUUID,
JobTitleName
, 0
FROM
OPENXML (@hdoc, '/OrganizationDS/JobTitle', 2) WITH (
JobTitleUUID uniqueidentifier './JobTitleUUID' ,
JobTitleName varchar(64) './JobTitleName'
,AlreadyExists bit
)
INSERT @EmployeeToJobTitleLinkUpdate
(
EmployeeUUID ,
JobTitleUUID
)
SELECT
EmployeeUUID,
JobTitleUUID
FROM
OPENXML (@hdoc, '/OrganizationDS/EmployeeToJobTitleLink', 2) WITH (
EmployeeUUID uniqueidentifier './EmployeeUUID' ,
JobTitleUUID uniqueidentifier './JobTitleUUID'
)
--Remove the handle to the XML document, since we're done with using the xmlDoc
EXEC sp_xml_removedocument @hdoc
/*
--these are here just to show the parsing works..commented out.
select * from @EmployeeUpdate
select * from @DepartmentUpdate
select * from @JobTitleUpdate
select * from @EmployeeToJobTitleLinkUpdate
*/
SET NOCOUNT OFF
GO
--GRANT EXECUTE ON dbo.uspParseXmlWithOPENXML TO northwinduser -- [[[[USERNAME]]]]
GO
print '[uspParseXmlWith2005Syntax]'
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspParseXmlWith2005Syntax]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspParseXmlWith2005Syntax]
Go
CREATE PROCEDURE [dbo].[uspParseXmlWith2005Syntax] (
@xml_doc xml
)
AS
SET NOCOUNT ON
-- build a table (variable table) to store the xml-based result set
DECLARE @EmployeeUpdate TABLE (
EntryID int IDENTITY (1,1),
EmployeeUUID uniqueidentifier,
DepartmentUUID uniqueidentifier,
LastName varchar(64) ,
FirstName varchar(64) ,
IsPermanent bit,
HireDate smalldatetime
,AlreadyExists bit
)
DECLARE @DepartmentUpdate TABLE (
EntryID int IDENTITY (1,1),
DepartmentUUID uniqueidentifier,
DepartmentName varchar(64)
,AlreadyExists bit
)
DECLARE @JobTitleUpdate TABLE (
EntryID int IDENTITY (1,1),
JobTitleUUID uniqueidentifier,
JobTitleName varchar(64)
,AlreadyExists bit
)
DECLARE @EmployeeToJobTitleLinkUpdate TABLE (
EntryID int IDENTITY (1,1),
EmployeeUUID uniqueidentifier,
JobTitleUUID uniqueidentifier
)
--the next call will take the info IN the @hdoc(with is the holder for @xml_doc), and put it IN a variableTable
INSERT @EmployeeUpdate
(
EmployeeUUID,
DepartmentUUID,
LastName,
FirstName,
IsPermanent,
HireDate
,AlreadyExists
)
SELECT
T.Item.value('EmployeeUUID[1]', 'UNIQUEIDENTIFIER') AS EmployeeUUID,
T.Item.value('DepartmentUUID[1]', 'UNIQUEIDENTIFIER') AS DepartmentUUID,
T.Item.value('LastName[1]', 'varchar(64)') AS LastName ,
T.Item.value('FirstName[1]', 'varchar(64)') AS FirstName ,
T.Item.value('IsPermanent[1]', 'bit') AS IsPermanent ,
T.Item.value('HireDate[1]', 'smalldatetime') AS HireDate ,
0
FROM
@xml_doc.nodes('/OrganizationDS/Employee') T(Item)
INSERT @DepartmentUpdate
(
DepartmentUUID,
DepartmentName
,AlreadyExists
)
SELECT
T.Item.value('DepartmentUUID[1]', 'UNIQUEIDENTIFIER') AS DepartmentUUID,
T.Item.value('DepartmentName[1]', 'varchar(64)') AS DepartmentName ,
0
FROM
@xml_doc.nodes('/OrganizationDS/Department') T(Item)
INSERT @JobTitleUpdate
(
JobTitleUUID,
JobTitleName
,AlreadyExists
)
SELECT
T.Item.value('JobTitleUUID[1]', 'UNIQUEIDENTIFIER') AS JobTitleUUID,
T.Item.value('JobTitleName[1]', 'varchar(64)') AS JobTitleName ,
0
FROM
@xml_doc.nodes('/OrganizationDS/JobTitle') T(Item)
INSERT @EmployeeToJobTitleLinkUpdate
(
EmployeeUUID ,
JobTitleUUID
)
SELECT
T.Item.value('EmployeeUUID[1]', 'UNIQUEIDENTIFIER') AS EmployeeUUID,
T.Item.value('JobTitleUUID[1]', 'UNIQUEIDENTIFIER') AS JobTitleUUID
FROM
@xml_doc.nodes('/OrganizationDS/EmployeeToJobTitleLink') T(Item)
/*
select * from @EmployeeUpdate
select * from @DepartmentUpdate
select * from @JobTitleUpdate
select * from @EmployeeToJobTitleLinkUpdate
*/
SET NOCOUNT OFF
GO
--GRANT EXECUTE ON dbo.uspParseXmlWith2005Syntax TO northwinduser -- [[[[USERNAME]]]]
GO
declare @TestLoopCounter int
declare @msCheck int
declare @compareDate datetime
declare @NumberOfTestLoops int
select @NumberOfTestLoops = 1000
select @TestLoopCounter = @NumberOfTestLoops
select @compareDate = getdate()
while @TestLoopCounter > 0
begin
select @TestLoopCounter = @TestLoopCounter - 1
exec uspParseXmlWithOPENXML
'
<OrganizationDS>
<Employee>
<EmployeeUUID>5F6892B7-9991-4CC4-9343-D4C1DE4E546C</EmployeeUUID>
<DepartmentUUID>6893D100-0BA3-44B5-AAC2-E2E7294DB8C0</DepartmentUUID>
<LastName>Smith</LastName>
<FirstName>John</FirstName>
<IsPermanent>true</IsPermanent>
<HireDate>Apr 28 2008 10:04AM</HireDate>
</Employee>
<Department>
<DepartmentUUID>6893D100-0BA3-44B5-AAC2-E2E7294DB8C0</DepartmentUUID>
<DepartmentName>Department1</DepartmentName>
</Department>
<JobTitle>
<JobTitleUUID>A893D100-0BA3-44B5-AAC2-E2E7294DB8CD</JobTitleUUID>
<JobTitleName>JobTitle1</JobTitleName>
</JobTitle>
<EmployeeToJobTitleLink>
<EmployeeUUID>5F6892B7-9991-4CC4-9343-D4C1DE4E546C</EmployeeUUID>
<JobTitleUUID>A893D100-0BA3-44B5-AAC2-E2E7294DB8CD</JobTitleUUID>
</EmployeeToJobTitleLink>
</OrganizationDS>
'
end
select @msCheck = datediff(ms , @compareDate , GETDATE())
print 'OPENXML ms'
print @msCheck
print ''
select @TestLoopCounter = @NumberOfTestLoops
select @compareDate = getdate() --reset the date
while @TestLoopCounter > 0
begin
select @TestLoopCounter = @TestLoopCounter - 1
exec dbo.uspParseXmlWith2005Syntax
'
<OrganizationDS>
<Employee>
<EmployeeUUID>5F6892B7-9991-4CC4-9343-D4C1DE4E546C</EmployeeUUID>
<DepartmentUUID>6893D100-0BA3-44B5-AAC2-E2E7294DB8C0</DepartmentUUID>
<LastName>Smith</LastName>
<FirstName>John</FirstName>
<IsPermanent>true</IsPermanent>
<HireDate>Apr 28 2008 10:04AM</HireDate>
</Employee>
<Department>
<DepartmentUUID>6893D100-0BA3-44B5-AAC2-E2E7294DB8C0</DepartmentUUID>
<DepartmentName>Department1</DepartmentName>
</Department>
<JobTitle>
<JobTitleUUID>A893D100-0BA3-44B5-AAC2-E2E7294DB8CD</JobTitleUUID>
<JobTitleName>JobTitle1</JobTitleName>
</JobTitle>
<EmployeeToJobTitleLink>
<EmployeeUUID>5F6892B7-9991-4CC4-9343-D4C1DE4E546C</EmployeeUUID>
<JobTitleUUID>A893D100-0BA3-44B5-AAC2-E2E7294DB8CD</JobTitleUUID>
</EmployeeToJobTitleLink>
</OrganizationDS>
'
end
select @msCheck = datediff(ms , @compareDate , GETDATE())
print '2005 Syntax ms'
print @msCheck
print ''

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sholliday SSCrazy Points: 2100 More actions · Answer 1

REMOVED: (Another poster showed me how to post xml code)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

sholliday (4/28/2008)
The forum isn't allowing me to put in xml tagged code into the post.
So I posted the tsql test-it code here:
http://www.ipass.net/sloan/code/OPENXML_vs_SQL2005Syntax_Test.txt
..

If you want to post the XML - run a "find and replace" operation before pasting. You need to replace the brackets with the HTML "escaped" version. As in:

replace <with &lt;

replace > with &gt;

Once you do that - your XML code should post just fine here.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

sholliday SSCrazy Points: 2100 More actions · Answer 3

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)

Feb 10 2007 00:59:02

Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)

Mar 23 2007 16:28:52

Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)

Mar 23 2007 16:28:52

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

The versions I've gotten the crappy results on.