0

See my below sample stored procedure code which is not full code base because my it is huge.

I am passing several xml data fragments to the stored procedure from my C# application and it is taking long time to pass them.

In the stored procedure, I am extracting data from each xml parameter & 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.

My problem is why it is taking long time to pass multiple huge xml data fragments to the stored procedure.

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?

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 do_work event - is this the reason for the long run time?

Thanks

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dona bhatt
  • 71
  • 2
  • 5
  • Have you tried an SQL-Debugger (e.g. via SQL Management Studio)? – Manfred Radlwimmer Aug 18 '19 at 16:28
  • i am passing many huge xml data from my c# application to sql server store proc. symantec endpoint protection is installed in my pc. does it checking / inspect data because huge data is passing from my pc to other pc where sql server is installed.. – Dona bhatt Aug 18 '19 at 16:47
  • 1
    Maybe it has nothing to do with passing but the processing is slow in the stored procedure. Have you benchmarked the stored procedure with the XML you're passing to it? – CodingYoshi Aug 18 '19 at 17:19

0 Answers0