• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

T-SQL OPENXML Query Tuning Performance Problems

I'm having real problems getting a 6MB XML document to make it through this stored procedure.  Basically it is running through the document twice filling two tables in the database.  I'm sure there is a more efficient way.  The input IS XML so I only need an OPENXML answer.

Thanks in advance.

CREATE PROCEDURE sp_enr_import (
      @strXML ntext,
      @term_id CHAR(1),
      @enr_yr CHAR(4)
)
AS
    DECLARE @iDoc int

    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

INSERT INTO IRP_ENR_IN (EMPLID,SCHL_ID,MAJOR_ID,CLASS_ID,TERM_ID,ENR_YR,FULPRT)
    (SELECT emplid, schl1_id, major_id, class_id, @term_id, @enr_yr, fulprt
      FROM OpenXML(@iDoc, '/TABLE/STUDENT', 2)
      WITH
      (      emplid VARCHAR(16),
            schl1_id VARCHAR(8),
            major_id VARCHAR(8),
            class_id INT,
            fulprt char(1)
      )
    )

INSERT INTO IRP_ENR_STUDENT (EMPLID,LASTNAME,FIRSTNAME,SEX_ID,RACE_ID)
    (SELECT * FROM OpenXML(@iDoc, '/TABLE/STUDENT', 2)
        WITH
        (      emplid VARCHAR(16),
            lastname VARCHAR(512),
            firstname VARCHAR(512),
            sex_id CHAR(1),
            race_id INT
      )
    )

      EXECUTE sp_xml_removedocument @iDoc
GO
0
trobutta
Asked:
trobutta
  • 6
  • 2
1 Solution
 
Anthony PerkinsCommented:
Please post the Xml. A fragment will suffice showing the relevant elements.
0
 
trobuttaAuthor Commented:
<TABLE>
      <STUDENT>
            <emplid>1232464</emplid>
            <schl1_id>AS</schl1_id>
            <major_id>CECS</major_id>
            <class_id>4</class_id>
            <fulprt>F</fulprt>
      </STUDENT>
</TABLE>

thanks
0
 
Anthony PerkinsCommented:
This is how I would do it:

CREATE PROCEDURE sp_enr_import (
     @strXML ntext,
     @term_id CHAR(1),
     @enr_yr CHAR(4)
)
AS
DECLARE @iDoc int

Declare @Temp Table(
            emplid VARCHAR(16),
            schl1_id VARCHAR(8),
            major_id VARCHAR(8),
            class_id INT,
            fulprt char(1),
            lastname VARCHAR(512),
            firstname VARCHAR(512),
            sex_id CHAR(1),
            race_id INT)

EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

INSERT      @Temp (EMPLID, SCHL_ID, MAJOR_ID, CLASS_ID, FULPRT, LASTNAME, FIRSTNAME, SEX_ID, RACE_ID)
SELECT      emplid, schl1_id, major_id, class_id, fulprt, lastname, firstname, sex_id, race_id
FROM      OpenXML(@iDoc, '/TABLE/STUDENT', 2) WITH(
            emplid VARCHAR(16),
            schl1_id VARCHAR(8),
            major_id VARCHAR(8),
            class_id INT,
            fulprt char(1),
            lastname VARCHAR(512),
            firstname VARCHAR(512),
            sex_id CHAR(1),
            race_id INT)

EXECUTE sp_xml_removedocument @iDoc

INSERT      IRP_ENR_IN (EMPLID,SCHL_ID,MAJOR_ID,CLASS_ID,TERM_ID,ENR_YR,FULPRT)
SELECT      emplid, schl1_id, major_id, class_id, @term_id, @enr_yr, fulprt
FROM      @Temp

INSERT      IRP_ENR_STUDENT (EMPLID,LASTNAME,FIRSTNAME,SEX_ID,RACE_ID)
SELECT      emplid, lastname, firstname, sex_id, race_id
FROM      @Temp

Also, consider using Text instead of nText for your Xml document.  That should cut it down by half.  Just understand that will affect the Xml encoding.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Anthony PerkinsCommented:
Assuming that the element names are the same, explcitly naming them may be more efficient:

CREATE PROCEDURE sp_enr_import (
     @strXML ntext,
     @term_id CHAR(1),
     @enr_yr CHAR(4)
)
AS
DECLARE @iDoc int

Declare @Temp Table(
            emplid VARCHAR(16),
            schl1_id VARCHAR(8),
            major_id VARCHAR(8),
            class_id INT,
            fulprt char(1),
            lastname VARCHAR(512),
            firstname VARCHAR(512),
            sex_id CHAR(1),
            race_id INT)

EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

INSERT      @Temp (EMPLID, SCHL_ID, MAJOR_ID, CLASS_ID, FULPRT, LASTNAME, FIRSTNAME, SEX_ID, RACE_ID)
SELECT      emplid, schl1_id, major_id, class_id, fulprt, lastname, firstname, sex_id, race_id
FROM      OpenXML(@iDoc, '/TABLE/STUDENT', 2) WITH(
                  emplid VARCHAR(16) 'emplid',
                  schl1_id VARCHAR(8) 'schl1_id',
                  major_id VARCHAR(8) 'major_id',
                  class_id INT 'class_id',
                  fulprt char(1) 'fulprt',
                  lastname VARCHAR(512) 'lastname',
                  firstname VARCHAR(512) 'firstname',
                  sex_id CHAR(1) 'sex_id',
                  race_id INT 'race_id')

EXECUTE sp_xml_removedocument @iDoc

INSERT      IRP_ENR_IN (EMPLID,SCHL_ID,MAJOR_ID,CLASS_ID,TERM_ID,ENR_YR,FULPRT)
SELECT      emplid, schl1_id, major_id, class_id, @term_id, @enr_yr, fulprt
FROM      @Temp

INSERT      IRP_ENR_STUDENT (EMPLID,LASTNAME,FIRSTNAME,SEX_ID,RACE_ID)
SELECT      emplid, lastname, firstname, sex_id, race_id
FROM      @Temp

0
 
trobuttaAuthor Commented:
Okay this is what I'm using right now and the performance is ridiculous.  I can barely push a 200K document through in under 10 minutes.


CREATE PROCEDURE sp_enr_import (
      @strXML ntext,
      @term_id CHAR(1),
      @enr_yr CHAR(4)
)
AS
DECLARE @iDoc int

Declare @Temp Table(
      emplid VARCHAR(16),
      schl_id VARCHAR(8),
      major_id VARCHAR(8),
      class_id INT,
      fulprt char(1),
      lastname VARCHAR(512),
      firstname VARCHAR(512),
      sex_id CHAR(1),
      race_id INT)

EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

INSERT      @Temp (emplid,schl_id,major_id,class_id,fulprt,lastname,firstname,sex_id,race_id)
SELECT      emplid, schl_id, major_id, class_id, fulprt, lastname, firstname, sex_id, race_id
FROM      OpenXML(@iDoc, '/TABLE/STUDENT', 2) WITH(
                  emplid VARCHAR(16) 'emplid',
                  schl_id VARCHAR(8) 'schl1_id',
                  major_id VARCHAR(8) 'major_id',
                  class_id INT 'class_id',
                  fulprt char(1) 'fulprt',
                  lastname VARCHAR(512) 'lastname',
                  firstname VARCHAR(512) 'firstname',
                  sex_id CHAR(1) 'sex_id',
                  race_id INT 'race_id')

EXECUTE sp_xml_removedocument @iDoc

INSERT      IRP_ENR_STUDENT (EMPLID,LASTNAME,FIRSTNAME,SEX_ID,RACE_ID)
SELECT      emplid, lastname, firstname, sex_id, race_id
FROM      @Temp

--- Remove any duplicates from the student table
delete IRP_ENR_STUDENT
from IRP_ENR_STUDENT,
            (
            select min(STUDENT_ID) as minID, EMPLID,LASTNAME,FIRSTNAME,SEX_ID,RACE_ID
            from IRP_ENR_STUDENT
            group by EMPLID,LASTNAME,FIRSTNAME,SEX_ID,RACE_ID
            having count(1) > 1
            ) as derived
WHERE
      IRP_ENR_STUDENT.EMPLID = derived.EMPLID AND
      IRP_ENR_STUDENT.LASTNAME = derived.LASTNAME AND
      IRP_ENR_STUDENT.FIRSTNAME = derived.FIRSTNAME AND
      IRP_ENR_STUDENT.SEX_ID = derived.SEX_ID AND
      IRP_ENR_STUDENT.RACE_ID = derived.RACE_ID AND
      IRP_ENR_STUDENT.STUDENT_ID > derived.minID
            
INSERT      IRP_ENR_IN (STUDENT_ID,SCHL_ID,MAJOR_ID,CLASS_ID,TERM_ID,ENR_YR,FULPRT)
SELECT      B.student_id, A.schl_id, A.major_id, A.class_id, @term_id, @enr_yr, A.fulprt
FROM      @Temp A, IRP_ENR_STUDENT B
WHERE
      A.emplid = B.EMPLID AND
      A.lastname = B.LASTNAME AND
      A.firstname = B.FIRSTNAME AND
      A.sex_id = B.SEX_ID AND
      A.race_id = B.RACE_ID
GO

0
 
Anthony PerkinsCommented:
>>Okay this is what I'm using right now and the performance is ridiculous.  <<
Without having your data or table structures, I cannot tell you anything further.  Feel free to send me the zipped Xml document and table defintions to my email address (in my profile) and I will attempt to duplicate the problem.
0
 
HilaireCommented:
Or upload a sample file on the net along with the table(s) creation script
0
 
Anthony PerkinsCommented:
Do you still need help with this question or can we now consider this question abandoned?
0
 
Anthony PerkinsCommented:
I can take credit for this question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now