Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Performance Issues With OPENXML

There is a slight performance penalty when using OPENXML. sp_xml_preparedocument and sp_xml_removedocument both execute the parsing through an extended stored procedure, so parsing is actually as efficient as it gets. However, as you might imagine, going through a ton of XML over and over again can consume some resources. If you need to use the output of the OPENXML function several times in your procedure, make a note of this trick. Instead of using the OPENXML function over and over again, use it once, and populate a table variable. Then use the table variable to join to other tables, perform INSERT, UPDATE, and DELETE statements (or whatever else you desire). Using a table variable is a lot more efficient than parsing XML repeatedly.

To demonstrate the above principle, I modified the procedure in the previous example slightly. In addition to UPDATING the authors table, the procedure also populates the fictitious audit table to keep track of the changes:

ALTER PROCEDURE update_authors_OPENXML (
    @xml_text TEXT)

AS
SET NOCOUNT ON 
-- document handle:
DECLARE @i INT

-- create internal representation 
-- of the document
EXEC sp_xml_preparedocument @i OUTPUT, @xml_text

-- table variable to hold output of OPENXML:
DECLARE @authors TABLE (
    au_id  	id  NOT NULL,
    au_lname  	VARCHAR(40) NULL,
    au_fname  	VARCHAR(20) NULL,
    phone  	CHAR  (12) NULL,
    address  	VARCHAR(40) NULL,
    city  	VARCHAR(20) NULL ,
    state  	CHAR  (2)  NULL ,
    zip  	CHAR  (5)  NULL ,
    contract  	BIT  	NOT NULL )	

-- populate the table variable:
INSERT @authors (
    au_id, 
    au_lname, 
    au_fname, 
    phone, 
    address, 
    city, 
    state, 
    zip, 
    contract)
SELECT 
    au_id, 
    au_lname, 
    au_fname, 
    phone, 
    address, 
    city, 
    state, 
    zip, 
    contract
FROM 	
    OPENXML(@i, '/root/authors') 
        WITH authors 

-- check for errors
IF @@ERROR <> 0
    BEGIN
       RAISERROR('error occured while using OPENXML', 16, 1)
       RETURN
    END

-- cleanup:
EXEC sp_xml_removedocument @i

BEGIN TRAN
-- populate audit:
INSERT audit (
    table_changed,
    key_of_the_changed_table, 
    changed_by, 
    date_changed)
SELECT 
    'authors', 
    au_id, 
    USER_NAME(), 
    GETDATE()
FROM  @authors a 
INNER JOIN authors b ON 
    a.au_id = b.au_id

IF @@ERROR <> 0 
    BEGIN
       RAISERROR('error occured while populating audit trail', 16, 1)
       ROLLBACK
       RETURN
    END	

-- update authors:
UPDATE 	authors
SET  au_fname = b.au_fname, 
    au_lname = b.au_lname, 
    address = b.address, 
    phone = b.phone, 
    city = b.city, 
    state = b.state, 
    zip = b.zip, 
    contract = b.contract
FROM  authors a 
INNER JOIN @authors 
    ON a.au_id = b.au_id

IF @@ERROR <> 0 
    BEGIN
       RAISERROR('error occured while updating authors', 16, 1)
       ROLLBACK
       RETURN
    END

COMMIT TRAN

-- return success
RETURN 0
  • + Share This
  • 🔖 Save To Your Account