CODEWITHSUNDEEP
sqlif-statementssissql-delete
PKirby
PKirby

Reputation: 889

Alternative to IF Statement for deletion

I have the following script running in a SSIS Execute SQL Task which is taking exceptionally long to execute :

DECLARE @Year int
DECLARE @Month int

SET @Year = YEAR(GETDATE())
SET @Month = MONTH(GETDATE())

IF @Month IN (1,2,3,4,5,6,7,8,9)
 BEGIN

  SET @Year = @Year-1

  DELETE FROM GLYTDMovement 
  WHERE CGYear >= @Year
   AND Entity NOT LIKE 'TT%'

 END
ELSE
 BEGIN

  SET @Year = @Year

  DELETE FROM GLYTDMovement 
  WHERE CGYear >= @Year
   AND Entity NOT LIKE 'TT%'

 END

Any advise on how to adjust to improve performance?

Upvotes: 0

Views: 41

Answers (2)

Tyron78
Tyron78

Reputation: 4187

You might want to try the following:

DECLARE @Year int
DECLARE @Month int

SET @Year = YEAR(GETDATE())
SET @Month = MONTH(GETDATE())

DELETE FROM GLYTDMovement 
WHERE Entity NOT LIKE 'TT%'
  AND ((CGYear >= @Year-1 AND @Month <= 9) OR (CGYear >= @Year AND @Month >= 10))

Furthermore it might be worth a try to evaluate the current dates year and month outside of this statement and then use an expression in the delete, which will look something like this:

...AND ((CGYear >= 2021-1 AND 3 <= 9) OR (CGYear >= 2021 AND 3 >= 10))

SQL Server / SSIS tend to be less performant when dealing with variables in the WHERE condition.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your code is executing exactly one DELETE statement. There is little that you can do to speed that up -- no loops, joins, or anything else that comes to mind.

What are possible issues?

  • The table may be really, really big and the WHERE clause requires full table scans.
  • Lots and lots of rows may be being deleted.
  • Resource contention and locks.

There is little that you can do about the second two. The first is tricky but you might find that a simple index on (Entity, CGYear) speeds the queries.

For a more focused index, you can create a filtered index:

create index fidx_GLYTDMovement_year on GLYTDMovement(year)
    where Entity >= 'TT' and Entity < 'TU';

This is equivalent to your logic (assuming a reasonable collation) but filtered indexes do not support LIKE.

Then be sure to use the same WHERE clause in the query:

WHERE CGYear >= @Year AND
      Entity >= 'TT' and Entity < 'TU'

Upvotes: 1

Related Questions