Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: delete from vs delete with subquery in transaction

    First, this is not my code.

    This one is weird and I am missing something fundamental on this one. A developer was getting a timeout with this...

    Code:
    CREATE PROCEDURE p_CM_DeleteBatch  
    (  
        @SubmitterTranID VARCHAR(50)  
    )  
    AS  
    DECLARE   
        @COUNT INT,  
        @COMMIT INT  
      
    SET @COUNT = 0  
    SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1  
      
    select @COUNT = COUNT(*) from claimsreceived   
    where (claimstatus NOT IN ('Keyed', 'Imported')) AND  
        SubmitterTranID = @SubmitterTranID  
      
    IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete  
        BEGIN  
          BEGIN TRANSACTION  
          DELETE FROM INVOICECLAIMMAPPING WHERE CLMRECDID IN (SELECT DISTINCT CLMRECDID FROM CLAIMSRECEIVED WHERE SUBMITTERTRANID = @SUBMITTERTRANID)
          IF (@@ERROR <> 0)  
                SET @COMMIT = 0  
    
          DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID  
          IF (@@ERROR <> 0)  
                SET @COMMIT = 0
     
          DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID  
          IF (@@ERROR <> 0)  
              SET @COMMIT = 0  
    
    	DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID
    												FROM ClaimsReceivedPayors
    												WHERE SubmitterTranID = @SubmitterTranID)
    
    	IF (@@ERROR <> 0)  
                SET @COMMIT = 0
    
    	DELETE FROM ClaimsReceivedPayors WHERE ClmRecdid in (SELECT ClmRecdID 
    										FROM ClaimsReceived 
    										WHERE SubmitterTranID = @SubmitterTranID)        
    	IF (@@ERROR <> 0)  
                SET @COMMIT = 0 
    
          DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID  
          IF (@@ERROR <> 0)  
                SET @COMMIT = 0  
          DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID  
          IF (@@ERROR <> 0)  
                SET @COMMIT = 0  
          DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID  
          IF (@@ERROR <> 0)  
                SET @COMMIT = 0  
      
          IF (@COMMIT = 1)  
              BEGIN  
                  --ROLLBACK TRANSACTION  --For Testing Purpose ONLY  
                  COMMIT TRANSACTION  
                  RETURN (0)  
              END  
          ELSE  
              BEGIN  
                  ROLLBACK TRANSACTION  
                  RETURN (-1)  
              END  
        END  
    ELSE  
        BEGIN  
            RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)  
        END  
    GO
    I applied a couple of indices and got ride of the uncorrelated subqueries

    Code:
    CREATE PROCEDURE p_CM_DeleteBatch  
    (  
        @SubmitterTranID VARCHAR(50)  
    )  
    AS  
    DECLARE   
        @COUNT INT,  
        @COMMIT INT  
      
    SET @COUNT = 0  
    SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1  
      
    select @COUNT = COUNT(*) from claimsreceived   
    where (claimstatus NOT IN ('Keyed', 'Imported')) AND  
        SubmitterTranID = @SubmitterTranID  
      
    IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete  
        BEGIN  
          BEGIN TRANSACTION  
          
    		DELETE INVOICECLAIMMAPPING
    		FROM INVOICECLAIMMAPPING 
    		JOIN CLAIMSRECEIVED
    		ON INVOICECLAIMMAPPING.CLMRECDID = CLAIMSRECEIVED.CLMRECDID
    		WHERE CLAIMSRECEIVED.SUBMITTERTRANID = @SUBMITTERTRANID		
    
    		IF (@@ERROR <> 0)  
                		SET @COMMIT = 0  
    
          		DELETE FROM ClaimsPayment 
    		WHERE SubmitterTranID = @SubmitterTranID  
         		IF (@@ERROR <> 0)  
     	           	SET @COMMIT = 0
     
          		DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID  
          		IF (@@ERROR <> 0)  
              		SET @COMMIT = 0  
    
    		DELETE ClaimsreceivedPayorServices
    		FROM ClaimsreceivedPayorServices
    		JOIN ClaimsReceivedPayors
    		ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID
    		WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID 
    		IF (@@ERROR <> 0)  
                		SET @COMMIT = 0
    
    		DELETE ClaimsReceivedPayors 
    		FROM ClaimsReceivedPayors
    		JOIN ClaimsReceived
    		ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid 
    		WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID 
    		
    		IF (@@ERROR <> 0)  
                		SET @COMMIT = 0 
    
          		DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID  
          		IF (@@ERROR <> 0)  
                		SET @COMMIT = 0  
          
    		DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID  
          		IF (@@ERROR <> 0)  
                		SET @COMMIT = 0  
          
    		DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID  
          		IF (@@ERROR <> 0)  
                		SET @COMMIT = 0
    	
          IF (@COMMIT = 1)  
              BEGIN  
                  --ROLLBACK TRANSACTION  --For Testing Purpose ONLY  
                  COMMIT TRANSACTION  
                  RETURN (0)  
              END  
          ELSE  
              BEGIN  
                  ROLLBACK TRANSACTION  
                  RETURN (-1)  
              END  
        END  
    ELSE  
        BEGIN  
            RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)  
        END
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
     
    GO
    Suddenly this constraint was being violated with the change

    Code:
    ALTER TABLE [dbo].[ClaimsReceivedPayorServices] ADD CONSTRAINT [FK_ClaimsReceivedPayorServices_CLAIMSRECEIVEDPAYORS] FOREIGN KEY 
    	(
    		[ClmRecdPyID]
    	) REFERENCES [CLAIMSRECEIVEDPAYORS] (
    		[CLMRECPYID]
    	)
    Is the delete on ClaimsReceivedPayors starting before the delete on ClaimsreceivedPayorServices finishes? If so why would it matter between the join and subquery? This one is making me depressed because I can not explain it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    I havent seen anything unusual except there is a difference in these two query after comparing two procedure u posted.
    Code:
    DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID
                FROM ClaimsReceivedPayors
                WHERE SubmitterTranID = @SubmitterTranID)
    Code:
    DELETE ClaimsreceivedPayorServices
      FROM ClaimsreceivedPayorServices
      JOIN ClaimsReceivedPayors
      ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID
      WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID
    My view abt constrain violation is,
    there may be some records in table ClaimsReceivedPayors get deleted which is exist in ClaimsreceivedPayorServices table,condition is different here.

    Do select statements on this table and check whether ClmRecdPyID from table ClaimsreceivedPayorServices matches with ClmRecdPyID from ClaimsReceivedPayors

    Code:
    DELETE ClaimsReceivedPayors 
      FROM ClaimsReceivedPayors
      JOIN ClaimsReceived
      ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid 
      WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well i think I fixed that while I was working with QA.

    This point is kind of moot. The indices I slapped on (that mainly sped up a few triggers involved) took it from 33 seconds for 58 claims to 1 1/2 which is not less than a second but I can live with it for now. Too many issues at the moment to get everything to query performance nirvana.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •