none
WCF-SQL Adapter to update table is hanging and taking more than 12 hours in updating a simple Row RRS feed

  • Question

  • Hi 

    I am picking a batch file from the SQL table with help of the WCF-SQL Adapter sending it to SAP in order to process and after 30 second , again same table  is being updated with WCF-SQL adapter by calling stored procedure , in test environment its working absolutely fine , but in production its taking more than 12 hours in order to one single row 

    I  analyze queries with help sp_who2  if some query is blocking and also analyze with help of sql profiler but did not get any fruitful result 

    It would be great  if someone can help me on it 

    Below is SP to update the table

    CREATE PROCEDURE [dbo].[USP_UpdateProc] 
    	@xml_parameter nvarchar(max)
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	Update 
    CREATE PROCEDURE [dbo].[usp_GetProc] 
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    	declare @myDoc xml 
    	Declare @IncludeExtendedInformation varchar(20);
    	
    IF @IncludeExtendedInformation = (select top 1 status from xxxx_Table  where status = 'COMPLETED' order by DATE_TIMESTAMP desc)
    PRINT @IncludeExtendedInformation
    	
    	set @myDoc = (SELECT top 1 MATERIALMASTER_XML
          from MATERIALMASTER_STAGING where status = 'NEW' order by DATE_TIMESTAMP asc)
    		SELECT @myDoc.query('/AMOS_MAT_DATA/AMOS_Interchange')
    	
    	
    	Update xxxx_Table  set STATUS = 'ACCEPTED' where ID =(Select TOP 1 ID from xxxx_Table   where STATUS = 'New' order by DATE_TIMESTAMP asc)
    	
    END
    Below SP is being used for the pulling the Batch file from Table-----------
    set STATUS = 'COMPLETED' where ID =(Select TOP 1 ID from  xxxx_Table   where STATUS = 'ACCEPTED' order by DATE_TIMESTAMP asc)
    END
    


    Thanks KK

     
    Thursday, August 16, 2018 12:49 PM

All replies

  • As you've already discovered, there's blocking going on.  Meaning, it's not BizTalk Server or the SQL Adapter.

    Without being able to see the environment live, it's difficult to say but a likely culprit is other queries, selects or updates, that aren't properly scoped or transactionalized causing SQL Sever to lock entire tables, pages or whatever.

    If this is the case, it's not a problem you can easily solve in your stored procedure.  The other code has to be refactored to not lock more than it needs.

    Thursday, August 16, 2018 1:36 PM