none
Performance issue with XML Data

    Question

  • I have a stored procedure with input parameter as @elem_dtls XML.
    I will be passing a 4 MB XML data() as Input parameter to this stored procedure.
    My worry is it takes almost 4 hours to insert the data to the temp table .I have attached the snippet of the code. Any help would be greatly appreciated.

    CREATE Procedure [dbo].[usp_INS_TT_DATA]     
                                                          
    (                                                             
      @elem_dtls xml = null                                     
    )
    AS                                                             
                                                
     BEGIN                                               
     SET NOCOUNT ON

    CREATE TABLE #tbl_timebooking                                            
     (         
      Cluster VARCHAR(25) NOT NULL, 
      StoreId Varchar(25) NOT NULL, 
      StoreName Varchar(50) NOT NULL,                          
      CourseCode VARCHAR(50) NOT NULL, 
      CourseName VARCHAR(20) NOT NULL, 
      TechnicalCourseName VARCHAR(20) NOT NULL, 
      CourseDate DATETIME NOT NULL, 
      CourseTime DATETIME NOT NULL, 
      Duration INT  NOT NULL, 
      MaxDelegates INT  NULL, 
      WeekStarting DATETIME NULL,  
      RoomNo Varchar(10) NOT NULL, 
      HostId Varchar(25) NOT NULL, 
      ScheduledDelegates INT  NULL, 
      Attendees INT  NULL 
                                               
     )                  
     
     
     
     BEGIN TRAN 


     INSERT INTO  
       #tbl_timebooking                                                 
     SELECT   
       elem_dtls.header.value('../../CLN[1]','VARCHAR(25)'), 
       elem_dtls.header.value('../ID[1]','VARCHAR(25)'), 
       elem_dtls.header.value('../NM[1]','VARCHAR(50)'),   
       '',
       '',  
       elem_dtls.header.value('TCN[1]','VARCHAR(20)'),  
       elem_dtls.header.value('DT[1]','DATETIME'),  
       elem_dtls.header.value('DT[1]','DATETIME'),  
       '',
       '',  
       '',  
       '', 
       '', 
       0, 
       0
                                                 
     FROM   
       @elem_dtls.nodes('/iPOSTSB/CL/STR/C') AS elem_dtls(header)
     


    END  
    COMMIT TRAN 
    SET NOCOUNT  OFF
    END

    Friday, March 20, 2009 1:12 PM

Answers

  • Hi Bob,

    My performance issue is solved.

    As you had mentioned in the first link, I created a XML Schema definition and used it in the stored procedure.
    My stored procedure which took 4 hrs ran in 58 seconds.

    Thank you so much.

    Wednesday, March 25, 2009 11:08 AM

All replies