Once this issue was identified, I created another procedure with a different approach, to return the same data.
Create Procedure select_Proc2
@Key1 int=0,
@Key2 int=0
As
BEGIN
IF @Key1 <>0 and @Key2<>0
Select key3
From Or_Table
Where Key1 =@Key1 AND
Key2 =@Key2
ELSE
IF @Key1<>0
Select key3
From Or_Table
Where Key1 =@Key1
Else
Select key3
From Or_Table
Where Key2 =@Key2
END
GO
This causes a change in the execution plan.
The tests were also carried out with an increase data load (a 5x increase on key2 data), as created by the script below.
— Insert Additional Test Data
Declare @Key1 int, @Key2 Int
Set @Key1 =1
While @Key1<100
BEGIN
Set @Key2 =21
While @Key2<100
BEGIN
Insert Or_Table ( Key1, key2, Key3)
Values(@Key1, @key2, ‘Data ‘+Convert(varchar, @Key1) +’, ‘+Convert(varchar, @Key2) )
Set @Key2 =@Key2+1
END
Set @key1= @Key1+1
END
The resulting change in the execution plan is drastic.
In comparing the two plans, it becomes clear that with the latter stored procedure, SQL Server is in a position to recompile the stored procedure and thereby decide on what index should be used.
In summary, when using parameter validations with “OR” clauses in combining two conditions, be aware of the potential issues that could arise. This does not mean that the “OR” clause does not have any positive uses; it is definitely handy to simplify and reduce code, in maintaining readability, and can be used in different scenarios. If the WHERE clause in question is SARG-able, (that means the WHERE clause can use indexes) the “OR” clause will be useful. A condition like (Key1 =@Value1 OR Key1 = @Value2) is far better than re-writing the entire code with an IF condition per key. You can also use the OR clause with parameters against non SARG-able fields, as the lack of indexes will mean no additional processing overhead will occur.
The performance of the stored procedure can be optimized yet further. As SQL Server keeps only one execution plan per stored procedure, that means IF conditions may force SQL Server to re-create another execution plan when different parameter values are used. In the scenario above, creating separate stored procedures for each IF condition would result in three distinct execution plans which will reside in memory and can be re-used. Unfortunately, this approach would result in changes to code, in the consuming application, and is not practical.
]]>