• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 929
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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