title: Passing XML data from C# application to stored procedure taking long time
body: <p>See my below sample stored procedure code which is not full code base because my it is huge.</p>
  
  <p>I am passing several xml data fragments to the stored procedure from my C# application and it is taking long time to pass them.</p>
  
  <p>In the stored procedure, I am extracting data from each xml parameter &amp; insert that data into multiple tables and later join many physical tables and store result into a temporary table. At last return data from final temporary table.</p>
  
  <p>My problem is why it is taking long time to pass multiple huge xml data fragments to the stored procedure. </p>
  
  <p>How to diagnose this problem to know which one taking long time and where time lagging? I need to diagnose is C# taking long time to call my stored procedure, or is the procedure taking a long time?</p>
  
  <p>Tell me the best way to pass multiple huge XML data fragments to a stored procedure. I am calling my stored procedure from a background worker <code>do_work</code> event - is this the reason for the long run time?</p>
  
  <p>Thanks</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[USP_InsertDataFromXML]
      (@Ticker VARCHAR(50),
       @PCName VARCHAR(50),
       @LiconfigXML XML,
       @10QKXML XML,
       @CustomFormula XML,
       @Comments XML,
       @ToggleData XML,
       @BrokerHistoryOptionXML XML,
       @BrokerGridXML XML,
       @PrevEarningXML XML,
       @BrokerBogey NVARCHAR(MAX),
       @PeriodListXML XML,
       @existHist VARCHAR(10),
       @existHistFY VARCHAR(10),
       @lblEarning VARCHAR(10))
  AS
  BEGIN
      SET @t1 = GETDATE();
  
      DELETE FROM [tblTenQKBogey] WHERE PCName = @PCName
  
      IF (@10QKXML IS NOT NULL) 
      BEGIN
          INSERT INTO [tblTenQKBogey] (Section, LineItem, XFundCode, PCName)
              SELECT
                  TenQK.detail.value('(Section/text())[1]','VARCHAR(MAX)') AS Section, 
                  TenQK.detail.value('(LineItem/text())[1]','VARCHAR(MAX)') AS LineItem, 
                  TenQK.detail.value('(xFundCode/text())[1]','VARCHAR(MAX)') AS xFundCode,
                  @PCName AS PCName
              FROM  
                  @10QKXML.nodes('/Bogeylist/BrokerData') AS TenQK(detail) 
          END
  
          SET @t2 = GETDATE();
  
          SELECT @timetaken = CAST(DATEDIFF(millisecond, @t1, @t2) AS VARCHAR(20))
  
          SELECT @DataCount = COUNT(*) FROM [tblTenQKBogey]
  
          INSERT INTO tblQcLog (Action, Info, TimeTaken, PCName, TableDataCount, Ticker)
          VALUES ('INSERT INTO [tblTenQKBogey] table',
                  'dumping data from xml',
                  @timetaken, @PCName, @DataCount, @Ticker)
      END
  END
  </code></pre>