asked on

Need a help in tuning the one stored procedure

Below script will delete more than 7 days of data from the database, but it takes morethan one hour to complete. I like get this query tuned.

I need a help/suggestion below query which should give better performances. I debug the query and found select statement is taken long time and there index scan performed rather than index seek

so i replaced "*"with column name that is present in where clause. Now logical reads has reduced .

I'd like to get suggestion,if there are any taught on tuning this query or re-writing the script which should make the script should run in lesser time.

Can we increase the batch size? will it give better performances?

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
 
 
/********************************************************************
*  @proc: DelHistory
* 
*     Parameter:
*                 1. @pro int = 5000                       (Delete Batch Size)
*                 2. @TransDet int = 1000 (Delete Batch Size)
*                 3. @TransRowt int = 1000             (Delete Batch Size)
*                 4. @sessdetcntint = 1000            (Delete Batch Size)
*                 5. @sessrwcnt int = 1000                       (Delete Batch Size)
*                 6. @summ int = 5000                        (Delete Batch Size)
*                 7.    @Debug bit = 0    (Default is set to false. To print out @progress)
*
*     Desc: The @procedure deletes 7-day old data from the following tables:
*                 1. table1
*                 2. table2
*                 3. table3
*                 4. table4
*                 5. table5
*                 6. table6
*                 7. table7
*                 8. table8
*                 9. table9
*             10. table10
*             11. table11
*             12. table12   
*             13. table13
*             14. table14
*    
*     Date Created: 05-21-2007
*
********************************************************************/
 
-- EXECUTE DelHistory 5000, 2500, 2500, 2500, 2500, 2500, 0
 
CREATE @proC [dbo].[DelHistory] (
            @pro INT = 5000,
            @TransDet INT = 1000,
            @TransRowt INT = 1000,
            @table4RowCount INT = 1000,
            @sessrwcnt INT = 1000,
            @summ INT = 1000,
            @Debug bit = 0)
AS
 
      SET NOCOUNT ON
     
-- Get the SessionID and TransID to be deleted
     
      DECLARE @dt DATETIME
      DECLARE @MaxSessionID INT    
      DECLARE @DeleteStep VARCHAR(50)
      DECLARE @DebugMsg VARCHAR(256)
      DECLARE @ErrorMsg VARCHAR(100)
     
      SELECT @dt = DATEADD(dd, -7, GetDate())
      SELECT @MaxSessionID = MAX(SessionID)
      FROM dbo.table5 (NOLOCK)
      WHERE table5tartTime < @dt
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = ' Max SessionID: ' + CONVERT(varchar(20), @MaxSessionID)        
            PRINT @DebugMsg
      END
 
-- Step 1 - Delete table1
      SET @DeleteStep = 'Step 1 - Delete table1'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *  FROM dbo.table1 PF (NOLOCK)
                  INNER JOIN dbo.table3 TR (NOLOCK)
                  ON TR.TransID = PF.TransRef
                  INNER JOIN dbo.table5 SE (NOLOCK)
                  ON SE.SessionID = TR.SessionRef
                  WHERE SE.SessionID <= @MaxSessionID)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@pro) PF FROM dbo.table1 PF
                  INNER JOIN dbo.table3 TR
                  ON TR.TransID = PF.TransRef
                  INNER JOIN dbo.table5 SE
                  ON SE.SessionID = TR.SessionRef
                  WHERE SE.SessionID <= @MaxSessionID
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 2 - Delete table2
      SET @DeleteStep = 'Step 2 - Delete table3'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
     
      WHILE EXISTS (SELECT TOP 1 *  FROM dbo.table2 TRD (NOLOCK)
                  INNER JOIN dbo.table3 TR (NOLOCK)
                  ON TR.TransID = TRD.TransRef
                  INNER JOIN dbo.table5 SE (NOLOCK)
                  ON SE.SessionID = TR.SessionRef
                  WHERE SE.SessionID <= @MaxSessionID)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@TransDet) TRD FROM dbo.table2 TRD
                  INNER JOIN dbo.table3 TR
                  ON TR.TransID = TRD.TransRef
                  INNER JOIN dbo.table5 SE
                  ON SE.SessionID = TR.SessionRef
                  WHERE SE.SessionID <= @MaxSessionID
               
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
     
      END
     
-- Step 3 - Delete table3
      SET @DeleteStep = 'Step 3 - Delete table3'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *  FROM dbo.table3 TR (NOLOCK)
                  INNER JOIN dbo.table5 SE (NOLOCK)
                  ON SE.SessionID = TR.SessionRef
                  WHERE SE.SessionID <= @MaxSessionID)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@TransRowt) TR FROM dbo.table3 TR
                  INNER JOIN dbo.table5 SE
                  ON SE.SessionID = TR.SessionRef
                  WHERE SE.SessionID <= @MaxSessionID
               
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
     
-- Step 4 - Delete table4
      SET @DeleteStep = 'Step 4 - Delete table4'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table4 (NOLOCK)
                  WHERE sessionref <= @MaxSessionID)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@table4RowCount) FROM dbo.table4
                  WHERE sessionref <= @MaxSessionID
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 5 - Delete table5
      SET @DeleteStep = 'Step 5 - Delete table5'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table5 (NOLOCK)
                  WHERE sessionid <= @MaxSessionID)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@sessrwcnt) FROM dbo.table5
                  WHERE sessionid <= @MaxSessionID
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 6 - Delete table7
      SET @DeleteStep = 'Step 6 - Delete table7'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table7 (NOLOCK)
                  WHERE table5tartTime < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table7
                  WHERE table5tartTime < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 7 - Delete table6
      SET @DeleteStep = 'Step 7 - Delete table6'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table6 (NOLOCK)
                  WHERE table5tartTime < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table6
                  WHERE table5tartTime < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 8 - Delete table8
      SET @DeleteStep = 'Step 8 - Delete table8'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table8 (NOLOCK)
                  WHERE table5tartTime < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table8
                  WHERE table5tartTime < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 9 - Delete table9
      SET @DeleteStep = 'Step 9 - Delete table9'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table9 (NOLOCK)
                  WHERE [Date] < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table9
                  WHERE [Date] < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 10 - Delete table10
      SET @DeleteStep = 'Step 10 - Delete table10'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table10 (NOLOCK)
                  WHERE [Date] < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table10
                  WHERE [Date] < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
 
-- Step 11 - Delete table11s
      SET @DeleteStep = 'Step 11 - Delete table11s'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table11s (NOLOCK)
                  WHERE [Date] < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table11s
                  WHERE [Date] < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 12 - Delete table12
      SET @DeleteStep = 'Step 12 - Delete table12'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table12 (NOLOCK)
                  WHERE [Date] < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table12
                  WHERE [Date] < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 13 - Delete table13
      SET @DeleteStep = 'Step 13 - Delete table13'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table13 (NOLOCK)
                  WHERE [Date] < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table13
                  WHERE [Date] < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
-- Step 14 - Delete table14
      SET @DeleteStep = 'Step 14 - Delete table14'
 
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Start @processing... ' + @DeleteStep + ' at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
      WHILE EXISTS (SELECT TOP 1 *
                  FROM dbo.table14 (NOLOCK)
                  WHERE table5tartTime < @dt)
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@summ) FROM dbo.table14
                  WHERE table5tartTime < @dt
            IF @@ERROR <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        GOTO ErrExit
                  END
            ELSE
                  COMMIT TRANSACTION
      END
 
 
 
-- Delete Transaction Completed
      IF @Debug = 1
      BEGIN
            SELECT @DebugMsg = 'Delete @process completed at ' + CONVERT(varchar(30), getdate())
            PRINT @DebugMsg
      END
 
GOTO @procExit
 
ErrExit:
 
      IF @@TRANCOUNT <> 0
      BEGIN
            ROLLBACK TRANSACTION
      END
 
      SET @ErrorMsg = 'Delete failed: ' + @DeleteStep
      RAISERROR (@ErrorMsg, 16, 1)
 
@procExit:
 
 
 
 
GO

Open in new window

Saurabh Bhadauria

why you required "select top 1 * " in exists statements .....you can just write
"select 1 from "

change
(SELECT TOP 1 *
FROM dbo.table9 (NOLOCK)
WHERE [Date] < @dt)

to

(SELECT 1
FROM dbo.table9 (NOLOCK)
WHERE [Date] < @dt)

exists is used to just check existence ....no need to return all column (*) ....

VIVEKANANDHAN_PERIASAMY

ASKER

Yes, i have mentioned this in my question.I have removed to return all the column and to return only one column.Is that anything else i can do to make better perfomarnce?

Saurabh Bhadauria

I am analysing your query,,meanwhile if you can provide execution plan... then I can help in better way...

Saurabh Bhadauria

Change the each loops like below and test if it is better...

 DECLARE @ErrorCode INT
DECLARE @RowsAffected INT

WHILE 1=1
      BEGIN
            BEGIN TRANSACTION
                  DELETE TOP (@pro) PF FROM dbo.table1 PF
                  INNER JOIN dbo.table3 TR
                  ON TR.TransID = PF.TransRef
                  INNER JOIN dbo.table5 SE
                  ON SE.SessionID = TR.SessionRef
                  WHERE SE.SessionID <= @MaxSessionID
                  
               SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT
               
                if @RowsAffected=0 and @ErrorCode=0
                    break; 
                  
            IF @ErrorCode <> 0
                  BEGIN
                        ROLLBACK TRANSACTION
                        break
                        
                  END
            ELSE
                  COMMIT TRANSACTION
      END

Open in new window

VIVEKANANDHAN_PERIASAMY

ASKER

can you please explain,who this will help in performs?

Saurabh Bhadauria

I have changed the while loop logic.....
now besides checking every time the existence of records using exists clause , it will keep Deleting until the rows affected become zero.....

so i am checking if the @@rowcount is zero then only break the loop otherwise continue the action...

Have you tested the logic ??

VIVEKANANDHAN_PERIASAMY

ASKER

That's great!!! Good logic. I have to check it.

So can we exclude goto statement right?

ASKER CERTIFIED SOLUTION

Saurabh Bhadauria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

Don't forget if you break the loop like this there is still a BEGIN TRANSACTION that is not closed. So

                if @RowsAffected=0 and @ErrorCode=0
                BEGIN
                    COMMIT TRANSACTION;
                    break; 
                END

Open in new window

jogos

Not adding to a performance-gain
<<So can we exclude goto statement right?>>

If you want to get rid of the goto and repeating the error-handling you can do that with a try.. catch
http://msdn.microsoft.com/en-us/library/ms175976(v=sql.105).aspx

jogos

If that procedure is taking an hour to complete it's not only the procedure-logic you must look at. Monitor when it's running and see if it's waiting for locks, if disk is the bottleneck (db-files on which disks mdf/ldf/ tempdb ), ....

Delete's also update your indexes, so if you have unused indexes then they take unnecessary resources (also on insert).

VIVEKANANDHAN_PERIASAMY

ASKER

I want to re-write this procedure logic first,in case any query tuning can be done.
That's reason i gave a experts call.
Is there anything where we can do about query tuning?Kindly advice.

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VIVEKANANDHAN_PERIASAMY

ASKER

Yes Sir: As you mentioned there is heavy insert and delete operation is performed.
insert will happen every 3 mins once and delete will delete older than 7days old data.

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VIVEKANANDHAN_PERIASAMY

ASKER

Lets be on the same page. I guess we have lots of advice on the generic rules.
The question here is posted/more like to concentrate on the script than other rules. I like to fix the code issue first. Anyway I will be testing in the uat first then will implement in the production.

DECLARE @dt DATETIME
	DECLARE @MaxSessionID INT	
	DECLARE @DeleteStep VARCHAR(50)
	DECLARE @DebugMsg VARCHAR(256)
	dECLARE @ErrorMsg VARCHAR(100)
	DECLARE @RowsAffected int 
	DECLARE @ErrorCode int 
select 'Declare competed'+ CONVERT(varchar,getdate(),100)	
	
	SELECT @dt = DATEADD(HOUR, -250, GetDate())
	select 'get hours details'+ CONVERT(varchar,getdate(),100)	
	SELECT @MaxSessionID = MAX(SessionID) 
	FROM dbo.Sessions (NOLOCK)
	WHERE SessionStartTime < @dt
	select 'get MaxSessionID'+ CONVERT(varchar,getdate(),100)

-- Step 1 - Delete Profiles
select 'Entering while loop to select top stmt'+ CONVERT(varchar,getdate(),100)
	WHILE EXISTS (SELECT TOP 1 SE.SessionID FROM dbo.profiles PF (NOLOCK)
			INNER JOIN dbo.Transactions TR (NOLOCK)
			ON TR.TransID = PF.TransRef
			INNER JOIN dbo.Sessions SE (NOLOCK)
			ON SE.SessionID = TR.SessionRef
			WHERE SE.SessionID <= @MaxSessionID)
			select 'completed select top stmt'+ CONVERT(varchar,getdate(),100)
			WHILE 1=1
	BEGIN
		BEGIN TRANSACTION
		select 'Deleted stmt started'+ CONVERT(varchar,getdate(),100)
			DELETE TOP (10) PF FROM dbo.profiles PF
			INNER JOIN dbo.Transactions TR 
			ON TR.TransID = PF.TransRef
			INNER JOIN dbo.Sessions SE 
			ON SE.SessionID = TR.SessionRef
			WHERE SE.SessionID <= @MaxSessionID
				select 'Deleted stmt completed'+ CONVERT(varchar,getdate(),100)
SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT
               
              if @RowsAffected=0 and @ErrorCode=0
 BEGIN
                    COMMIT TRANSACTION;
                    break; 
                    end 
               
		IF @@ERROR <> 0
			BEGIN
				select 'Entered if loop'+ CONVERT(varchar,getdate(),100)
				ROLLBACK TRANSACTION
				select 'Entered if loop,roll back happened '+ CONVERT(varchar,getdate(),100)
				GOTO ErrExit
			END
		ELSE
			COMMIT TRANSACTION
			select 'COMMIT TRANSACTION happened '+ CONVERT(varchar,getdate(),100)
	END

GOTO ProcExit

ErrExit:

	IF @@TRANCOUNT <> 0
	BEGIN	
		ROLLBACK TRANSACTION
 	END

	SET @ErrorMsg = 'Delete failed: ' + @DeleteStep 
	RAISERROR (@ErrorMsg, 16, 1)

ProcExit:

Open in new window

I'm trying to execute the above query.

"completed select top stmtNov" This statement is keep on executing again and again.
I like to know why select is keep on executing. I believe it should execute only once and should start with delete transaction? Why there are multiple times the select statement is getting executed?

SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

The whole idea of the WHILE 1=1 is to get rid of the WHILE EXISTS and if you have no records executed you BREAK the while.

Seeing your code you nest the both, so the break only stops the WHILE 1=1-loop and the WHILE EXISTS-loop will continue to go on forever.

	WHILE EXISTS (SELECT TOP 1 SE.SessionID FROM dbo.profiles PF (NOLOCK)
			INNER JOIN dbo.Transactions TR (NOLOCK)
			ON TR.TransID = PF.TransRef
			INNER JOIN dbo.Sessions SE (NOLOCK)
			ON SE.SessionID = TR.SessionRef
			WHERE SE.SessionID <= @MaxSessionID)
			select 'completed select top stmt'+ CONVERT(varchar,getdate(),100)
			WHILE 1=1

Open in new window

And worse you mess up the @@rowcount and @@Error that controls your code by putting a debugstatement between the DELETE and capturing those values.

		select 'Deleted stmt started'+ CONVERT(varchar,getdate(),100)
			DELETE TOP (10) PF FROM dbo.profiles PF
			INNER JOIN dbo.Transactions TR 
			ON TR.TransID = PF.TransRef
			INNER JOIN dbo.Sessions SE 
			ON SE.SessionID = TR.SessionRef
			WHERE SE.SessionID <= @MaxSessionID
				select 'Deleted stmt completed'+ CONVERT(varchar,getdate(),100)
SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

Open in new window

In previous code you capture the @@ERROR and @@ROWCOUNT of the statement

select 'Deleted stmt completed'+ CONVERT(varchar,getdate(),100)

Open in new window

and not of the DELETE. So your whole flow-control is gone.

VIVEKANANDHAN_PERIASAMY

ASKER

SET STATISTICS IO ON 

SET STATISTICS TIME ON 

DECLARE @dt DATETIME

      DECLARE @MaxSessionID INT     

      DECLARE @DeleteStep VARCHAR(50)

      DECLARE @DebugMsg VARCHAR(256)

      DECLARE @ErrorMsg VARCHAR(100)

      DECLARE @ErrorCode INT

DECLARE @RowsAffected INT 

DECLARE @ProfilesRowCount INT = 5000

      DECLARE @TransactionDetailRowCount INT = 1000

      DECLARE @TransactionsRowCount INT = 1000

      DECLARE @SessionDetailsRowCount INT = 1000

      DECLARE @SessionsRowCount INT = 1000

      DECLARE @SummaryRowCount INT = 1000

DECLARE @Debug bit = 0

      SELECT @dt = DATEADD(DD, -7, GetDate())

      SELECT @MaxSessionID = MAX(SessionID) 

      FROM dbo.Sessions (NOLOCK)

      WHERE SessionStartTime < @dt

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = ' Max SessionID: ' + CONVERT(varchar(20), @MaxSessionID)         

            PRINT @DebugMsg

      END

 

-- Step 1 - Delete Profiles

      SET @DeleteStep = 'Step 1 - Delete Profiles'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

            END

WHILE 1=1

BEGIN

  BEGIN TRANSACTION

  select 'Deleted stmt started for Profiles ', CONVERT(varchar,getdate(),100)

   DELETE TOP (@ProfilesRowCount) PF FROM dbo.profiles PF

   INNER JOIN dbo.Transactions TR 

   ON TR.TransID = PF.TransRef

   INNER JOIN dbo.Sessions SE 

   ON SE.SessionID = TR.SessionRef

   WHERE SE.SessionID <= @MaxSessionID

    

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

   

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop for Profiles', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'Entered if loop,roll back happened for Profiles ', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened for Profiles', CONVERT(varchar,getdate(),100)

END

-- Step 2 - Delete TransactionDetails

      SET @DeleteStep = 'Step 2 - Delete Transactions'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

      

      

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for TransactionDetails ', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@TransactionDetailRowCount) TRD FROM dbo.TransactionDetails TRD

                  INNER JOIN dbo.Transactions TR 

                  ON TR.TransID = TRD.TransRef

                  INNER JOIN dbo.Sessions SE

                  ON SE.SessionID = TR.SessionRef

                  WHERE SE.SessionID <= @MaxSessionID

 

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

                if @RowsAffected=0 and @ErrorCode=0

                                  

            BEGIN

                    

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop for TransactionDetails', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'Rollbacked for TransactionDetails ', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END      

ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened for TransactionDetails', CONVERT(varchar,getdate(),100)

END

-- Step 3 - Delete transactions

      SET @DeleteStep = 'Step 3 - Delete Transactions'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for transactions ', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@TransactionsRowCount) TR FROM dbo.transactions TR

                  INNER JOIN dbo.Sessions SE

                  ON SE.SessionID = TR.SessionRef

                  WHERE SE.SessionID <= @MaxSessionID

 

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                    break; 

                   end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop for transactions', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened for transactions', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened for transactions', CONVERT(varchar,getdate(),100)

END

      

-- Step 4 - Delete SessionDetails

      SET @DeleteStep = 'Step 4 - Delete SessionDetails'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

      

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for  SessionDetails ', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SessionDetailsRowCount) FROM dbo.SessionDetails

                  WHERE sessionref <= @MaxSessionID

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop for SessionDetails', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'Roll back happened for SessionDetails ', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened for SessionDetails ', CONVERT(varchar,getdate(),100)

END

-- Step 5 - Delete Sessions

      SET @DeleteStep = 'Step 5 - Delete Sessions'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

      

WHILE 1=1

 

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for  Sessions ', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SessionsRowCount) FROM dbo.Sessions

                  WHERE sessionid <= @MaxSessionID

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop for Sessions', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened for Sessions', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened for Sessions', CONVERT(varchar,getdate(),100)

END

 

-- Step 6 - Delete ProfilesSummary

      SET @DeleteStep = 'Step 6 - Delete ProfilesSummary'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

                  select 'Deleted stmt started for  ProfilesSummary ', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.ProfilesSummary

                  WHERE SessionStartTime < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop ProfilesSummary', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'Roll back happened ProfilesSummary', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened ProfilesSummary', CONVERT(varchar,getdate(),100)

END

 

-- Step 7 - Delete TransSummary

      SET @DeleteStep = 'Step 7 - Delete TransSummary'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

                  select 'Deleted stmt started for  TransSummary', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.TransSummary

                  WHERE SessionStartTime < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                  

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop TransSummary', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened TransSummary', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened TransSummary', CONVERT(varchar,getdate(),100)

END

 

-- Step 8 - Delete Summary1

      SET @DeleteStep = 'Step 8 - Delete Summary1'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for  Summary1', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.Summary1

                  WHERE SessionStartTime < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop Summary1', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened Summary1', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened Summary1', CONVERT(varchar,getdate(),100)

END

 

-- Step 9 - Delete TestAccounts

      SET @DeleteStep = 'Step 9 - Delete TestAccounts'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for  TestAccounts', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.TestAccounts

                  WHERE [Date] < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN


                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop TestAccounts', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'Roll back happened TestAccounts', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened ', CONVERT(varchar,getdate(),100)

END

-- Step 10 - Delete FrontDoor

      SET @DeleteStep = 'Step 10 - Delete FrontDoor'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

                  select 'Deleted stmt started for  FrontDoor', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.FrontDoor 

                  WHERE [Date] < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                   

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop FrontDoor', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened FrontDoor', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened FrontDoor', CONVERT(varchar,getdate(),100)

END

 

-- Step 11 - Delete FrontDoorVersions

      SET @DeleteStep = 'Step 11 - Delete FrontDoorVersions'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

                  select 'Deleted stmt started for  FrontDoorVersions', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.FrontDoorVersions 

                  WHERE [Date] < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

                 

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop FrontDoorVersions', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened FrontDoorVersions', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened FrontDoorVersions', CONVERT(varchar,getdate(),100)

END

 

-- Step 12 - Delete ProfileSummary

      SET @DeleteStep = 'Step 12 - Delete ProfileSummary'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for  ProfileSummary', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.ProfileSummary 

                  WHERE [Date] < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

             

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop ProfileSummary', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened ProfileSummary ', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened ProfileSummary', CONVERT(varchar,getdate(),100)

END

 

-- Step 13 - Delete ErrorDistributions

      SET @DeleteStep = 'Step 13 - Delete ErrorDistributions'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

 

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

                  select 'Deleted stmt started for  ErrorDistributions', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.ErrorDistributions 

                  WHERE [Date] < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

              

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop Delete ErrorDistributions', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened Delete ErrorDistributions', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened Delete ErrorDistributions', CONVERT(varchar,getdate(),100)

END

 

-- Step 14 - Delete Errors

      SET @DeleteStep = 'Step 14 - Delete Errors'

 

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Start processing... ' + @DeleteStep +'at'+ CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

      END

WHILE 1=1

      BEGIN

            BEGIN TRANSACTION

            select 'Deleted stmt started for  Errors', CONVERT(varchar,getdate(),100)

                  DELETE TOP (@SummaryRowCount) FROM dbo.Errors 

                  WHERE SessionStartTime < @dt

SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT

               

              if @RowsAffected=0 and @ErrorCode=0

BEGIN

            

                    break; 

                    end 

               

  IF @@ERROR <> 0

   BEGIN

    select 'Entered if loop Delete Errors', CONVERT(varchar,getdate(),100)

    ROLLBACK TRANSACTION

    select 'roll back happened Delete Errors', CONVERT(varchar,getdate(),100)

    GOTO ErrExit

   END

  ELSE

   COMMIT TRANSACTION

   select 'COMMIT TRANSACTION happened Delete Errors', CONVERT(varchar,getdate(),100)

END

 

 

-- Delete Transaction Completed

      IF @Debug = 1

      BEGIN

            SELECT @DebugMsg = 'Delete Process completed at ' + CONVERT(varchar(30), getdate())

            PRINT @DebugMsg

            SELECT 'Transaction Completed'

      END

 

GOTO ProcExit

 

ErrExit:

 

      IF @@TRANCOUNT <> 0

      BEGIN 

            ROLLBACK TRANSACTION

     END

 

GOTO ProcExit

 

      SET @ErrorMsg = 'Delete failed: ' + @DeleteStep 

      RAISERROR (@ErrorMsg, 16, 1)

 

ProcExit:

Open in new window

I have modified the script.For debuging purpose i have added the time,statistics io,time.I will include no count later.Please suggest,if i need to modify anything in the script to gain performance.
Should i include while exist select stmt?If you how to do it without looping the select.Whuch i like i did before.
jogo:thanks for the advice. i was able to find the ccause.

jogos

You keep repeating same questions. Please use advice

<<Should i include while exist select stmt?>>
No, and so you are told since first comment.
And if you have measured (profiler) or dmv on how many times it is executed and how many resources it takes you would know why.
And if you have measured the same execution with and without the while-exists you should have facts and don't

<<If you how to do it without looping the select.Whuch i like i did before.>>
Don't understand this part.

But basicly your looping for controling the batchsize still exists, only you don't have an extra statement to test.

<<Please suggest,if i need to modify anything in the script to gain performance.>>
Default values for your delete-batches seem small to me and you should increase that while monitoring that the gain on this procedure has no fatal impact on other process that are more time-critical Jogos#a38591099.
If you have to delete 10.000 records you can do that without repeating it 10 times for 1000 records. But if you have to delete 500.000 records it will definitly be better not to launch a delete 500 times. But is deleting it in 1 time, 5 times or 50 times better that's what your monitoring must tell.

And the rest of the gain is outside this procedure (index/statistics definition and maintenance, server/disk configuration and other processes that are running on same moment). After a significant delete it will be usefull to update statistics at the end (as dtodd already mentioned) but that's a trade-off between do you mix index and statistics-maintenance with business logic? My opinion is doing this in a different step of your job or at least with a safety you can run without.

If you plan your normal index and statistics-maintenance carefully you must always take into account you do that with knowledge of the big actions that can influence index/statistics healt.

VIVEKANANDHAN_PERIASAMY

ASKER

My repeative asking questions is not to criticise you.Is to understand the concepts and to catch the experts thoughts thoroughtly.That's reason i'm using EE rather than other forums.
So please bare with my questions.

>>My opinion is doing this in a different step of your job or at least with a safety you can ru without.<<

Since the 14 steps has individual batches, how does configuring it through different steps in the job will take effect?

Index statistics we run at 2'c ,once in a day.We don't index rebuild/reorganise.

Initially we were having it,but we faced few issues.
Since there are ~1 millions records gets inserted in span of 1 hour.Index goes to 98% fragmented in 4 hours.So situation became like,we need to run index rebuild every 4 hours once snd we can't downtime for database morethan 2 mins.

We don't know ,how to go about this?Your thought could help us.

One more question, during index online rebuld will the database be offline for any seconds?Or will there be any impact like enviroment which has huge number of insert statement.

Our database will be performing only insert &delete operation.There is no update operation will be performed.

jogos

<<>>My opinion is doing this in a different step of your job or at least with a safety you can ru without.<<

Since the 14 steps has individual batches, how does configuring it through different steps in the job will take effect?
>>
That quote was on index rebuild or reorganise appending to your job in a seperate step.

<<Our database will be performing only insert &delete operation>>
If you don't query your tables you don't need a lot of indexes. And if your indexes don't represent that loadorder then you will definitly have fragmentation.

With online rebuild your database will not be offline.

<<My repeative asking questions is not to criticise you.Is to understand the concepts and to catch the experts thoughts thoroughtly.>>
Exact one month ago Saurv to loose the EXISTS, a few clarifictions ans suggestions of Saurv, myself and dtodd let me frase this

<<But start using the information given, chances that there will come another brilliant refrasing of your code having more impact as the given are not so big.>>
And still you don't have returned any information on what the impact off that change and playing with batchsizes.
That was the easy start with your code.

jogos

<<Since there are ~1 millions records gets inserted in span of 1 hour.>>
And you delete records in batches of 1000?

Dirk Haest

I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

jogos

Question abandonned by author, no response on performance-gain by suggested improvements.

Coding optimisation given from beginning
https://www.experts-exchange.com/questions/27927474/Need-a-help-in-tuning-the-one-stored-procedure.html?anchorAnswerId=38575464#a38575464

Other performance-advice (monitoring for indexes and batchsizes) are also given
https://www.experts-exchange.com/questions/27927474/Need-a-help-in-tuning-the-one-stored-procedure.html?anchorAnswerId=38591099#a38591099
https://www.experts-exchange.com/questions/27927474/Need-a-help-in-tuning-the-one-stored-procedure.html?anchorAnswerId=38595794#a38595794
https://www.experts-exchange.com/questions/27927474/Need-a-help-in-tuning-the-one-stored-procedure.html?anchorAnswerId=38600333#a38600333