DELETE tp
  FROM TESTPO tp
 WHERE STATUS = 1
   AND EXISTS(SELECT * 
           ...

If they are different collations then you will have...

I didn't mean that as a slam.  It's just not possible to post general guidlines because everyone's stuff is different.  I just watched the following YouTube on the subject......

It would be helpful if you knew which particular statements within the stored procedure are the long running ones. The first thing to do is identify which statement(s) are taking...

Really you should have the databases set to the same collation as the tempDB as if you create a temporary table you will have problems joining it to your database...

I think the most obvious way to rewrite that query would be:

SELECT u.UserId
  FROM UserTable u 
 WHERE EXISTS(SELECT * 
     ...

;WITH myTable AS
(
    SELECT *
      FROM (VALUES (1,10,'Deny','Denied','a','04/05/2020'),
           ...

An FK is a constraint that enforces the values in the column(s) are present in the primary-key/unique-index of the related table.

It allows you to create more general purpose flexible SQL statement because the full text of the SQL statements may be unknown at compilation

Rather than 'compilation', I'd suggest 'time...

An alternative is like below.  Which performs better depends on total data volume and index(es) present:

Edit: I'm at work so I have only a limited time to review...

.

• This reply was modified 5 years, 4 months ago by Jonathan AC Roberts. Reason: deleted

Heh... let the silliness continue.  I just got an email saying...

KILL 99.9% OF BACTERIA & HARMFUL MICROBES

FORMS A PROTECTIVE SHIELD THAT DELAYS GROWTH OF HARMFUL MICROBES ON INTERIOR...

The fastest way for you to do this is in SSMS Object Explorer, select the "Stored Procedures" node, then press F7. In the right-hand pane then select all the stored...

Install this stored procedure

IF Object_Id(N'INFGenerateObjectScript', N'P') IS NULL
BEGIN
    EXEC ('CREATE PROCEDURE [dbo].[INFGenerateObjectScript] AS BEGIN SELECT 1 END')
END
GO 
 
GO 
-- **********************************************************************
-- PROCEDURE INFGenerateObjectScript
-- Description:...

Why don't you have your stored procedure code in a source control system?