SQL IF EXISTS with OR Condition

Question

I am facing a performance issue with a SQL statement. I have noticed a performance degradation in one SQL statement in one of a procedure.

SQL Statement:

IF EXISTS(SELECT TOP 1 FROM TABLE1 WHERE COLUMN1 = 'XYZ')  OR @ISALLOWED = 1
BEGIN
    -- SQL Statements
END

I couldn't able to understand why OR statement with IF Exists statement is causing performance issue in above query. Because if I rewrite the above statement like this:

DECLARE @ISVALUEEXISTS BIT
SET @ISVALUEEXISTS = 0

IF EXISTS(SELECT TOP 1 FROM TABLE1 WHERE COLUMN1 = 'XYZ')
    SET @ISVALUEEXISTS = 1

IF (@ISVALUEEXISTS = 1 OR @ISALLOWED = 1 )
BEGIN
     --SQL Statements
END

Then performance issue is gone. So, I'm couldn't able to understand how and why OR condition with IF Exists statement is causing problem.

Anyone has any idea about this?

Answer 1

If you have this query inside stored procedure this could happen because of parameter sniffing.

Try something like this to check it:

declare @ISALLOWED_internal
select @ISALLOWED_internal = @ISALLOWED

IF EXISTS(SELECT TOP 1 FROM TABLE1 WHERE COLUMN1 = 'XYZ')  OR  @ISALLOWED_internal = 1
BEGIN
    -- SQL Statements
END