SQL where clause with nested case statements optimisation

Question

Below is my WHERE clause and there is repetition in it (particularly with the two @subgroup variables), so I was wondering if there is anything I can do to optimise the code. My code works fine as is, but if I can get it prettier that would be great. Firstly a quick explanation.

I have five parameters that populate from asp dropdowns. For the purposes of this question I've populated them with data:

DECLARE
@DataCollection VARCHAR(50) = '2013/14 - Autumn 1 - Targets',
@StuYear VARCHAR(2) = '11',
@SubjectName VARCHAR(100) ='English',
@TeachingGroup VARCHAR(25) = 'Select All',
@SubGroup VARCHAR(15) = 'FSMYes',
@SubGroup2 VARCHAR(15) = 'SENS'

The where statement enables the result set to be filtered based on the values in the dropdowns.

@DataCollection, @StuYear and @SubjectName are all mandatory and have default values in the dropdown. The rest are optional.

The variables for @subgroup are populated based on hardcoded values in the dropdown, so a parameter is passed and if that parameter value matches the condition in the WHERE clause then a particular db value is filtered to in a particular column of the table.

Here is my SQL:

WHERE
[StuYear] = @StuYear AND
[DataCollection] = @DataCollection AND
[Name] = @SubjectName AND (
    @TeachingGroup = 'Select All' OR 
    [TeachingGroup] = @TeachingGroup
) AND (
   @SubGroup = 'Select All' OR
   Gender = CASE --Gender
           WHEN @SubGroup = 'GenF' THEN 'F'
           WHEN @SubGroup = 'GenM' THEN 'M'
       END 
   OR
   LEFT(ks2av,1) = CASE --Attainer
           WHEN @SubGroup = 'High' THEN '5'
           WHEN @SubGroup = 'High' THEN '5'
           WHEN @SubGroup = 'Middle' THEN '4'
           WHEN @SubGroup = 'Low' THEN '3'
           WHEN @SubGroup = 'Low' THEN '2'
           WHEN @SubGroup = 'Low' THEN '1'
       END
   OR
   PupilPremium = CASE --Pupil Premium
           WHEN @Subgroup = 'PPYes' THEN 'Yes'
       END 
   OR
   FSM = CASE --Free School Meals
           WHEN @Subgroup = 'FSMYes' THEN 'Yes'
       END
   OR
   SEN = CASE --SEN
           WHEN @Subgroup = 'SENA' THEN 'A'
           WHEN @Subgroup = 'SENP' THEN 'P'
           WHEN @Subgroup = 'SENS' THEN 'S'
           WHEN @Subgroup = 'SENNo' THEN 'N'
       END
   OR
   (@SubGroup='SENYes' AND SEN IN ('A','P','S')) --SEN Yes
   OR
   EAL = CASE --English as an Additional Language
           WHEN @Subgroup = 'EALYes' THEN 'Yes'
       END
   OR
   LAC = CASE --Looked After Children
           WHEN @Subgroup = 'LACYes' THEN 'Yes'
       END
   OR --Gifted & Talented       
   GandT = CASE 
           WHEN @Subgroup = 'GandTYes' THEN 'Yes'
       END
   OR --Gifted Only       
   Gifted = CASE 
           WHEN @Subgroup = 'GiftedYes' THEN 'Yes'
       END
   OR --Talented Only       
   Talented = CASE 
           WHEN @Subgroup = 'TalentedYes' THEN 'Yes'
       END
)AND (
   @subgroup2 = 'Select All' OR
   Gender = CASE --Gender
           WHEN @subgroup2 = 'GenF' THEN 'F'
           WHEN @subgroup2 = 'GenM' THEN 'M'
       END 
   OR
   LEFT(ks2av,1) = CASE --Attainer
           WHEN @subgroup2 = 'High' THEN '5'
           WHEN @subgroup2 = 'High' THEN '5'
           WHEN @subgroup2 = 'Middle' THEN '4'
           WHEN @subgroup2 = 'Low' THEN '3'
           WHEN @subgroup2 = 'Low' THEN '2'
           WHEN @subgroup2 = 'Low' THEN '1'
       END
   OR
   PupilPremium = CASE --Pupil Premium
           WHEN @subgroup2 = 'PPYes' THEN 'Yes'
       END 
   OR
   FSM = CASE --Free School Meals
           WHEN @subgroup2 = 'FSMYes' THEN 'Yes'
       END
   OR
   SEN = CASE --SEN
           WHEN @subgroup2 = 'SENA' THEN 'A'
           WHEN @subgroup2 = 'SENP' THEN 'P'
           WHEN @subgroup2 = 'SENS' THEN 'S'
           WHEN @subgroup2 = 'SENNo' THEN 'N'
       END
   OR
   (@subgroup2='SENYes' AND SEN IN ('A','P','S')) --SEN Yes
   OR
   EAL = CASE --English as an Additional Language
           WHEN @subgroup2 = 'EALYes' THEN 'Yes'
       END
   OR
   LAC = CASE --Looked After Children
           WHEN @subgroup2 = 'LACYes' THEN 'Yes'
       END
   OR --Gifted & Talented       
   GandT = CASE 
           WHEN @subgroup2 = 'GandTYes' THEN 'Yes'
       END
   OR --Gifted Only       
   Gifted = CASE 
           WHEN @subgroup2 = 'GiftedYes' THEN 'Yes'
       END
   OR --Talented Only       
   Talented = CASE 
           WHEN @subgroup2 = 'TalentedYes' THEN 'Yes'
       END
)

Answer 1

All of these ORs will tend to optimize poorly because a plan will be chosen for one path and then re-used on several others where it doesn't make a lot of sense. Two options you can consider:

1. Dynamic SQL. This allows each specific code path to be compiled and optimized separately. You can prevent plan cache bloat by using the optimize for ad hoc workloads configuration option, which prevents new plans from being fully cached until they are used a second time. Quick example:

   DECLARE @sql NVARCHAR(MAX) = N'SELECT ... 
     WHERE StuYear = @StuYear
       AND DataCollection = @DataCollection
       AND Name = @SubjectName';
   
   -- assuming optional parameters can't be NULL:
   IF @TeachingGroup <> 'Select All' THEN 
     SET @sql += N' AND TeachingGroup = @TeachingGroup';
   
   -- several more of these
   
   EXEC sp_executesql @sql,
     N'@StuYear VARCHAR(2), @DataCollection VARCHAR(50),
       @SubjectName VARCHAR(100), @TeachingGroup VARCHAR(25)',
     @StuYear, @DataCollection, @SubjectName, @TeachingGroup;
   

2. Add OPTION (RECOMPILE) to the query. This will cost a bit more in terms of overall compilation than dynamic SQL, since each of those versions will not compile every single time, but the added benefit here is it can also thwart parameter sniffing (where different parameter values can drastically affect the effectiveness of a chosen plan).

As an aside, something like this:

SEN = CASE --SEN
       WHEN @Subgroup = 'SENA' THEN 'A'
       WHEN @Subgroup = 'SENP' THEN 'P'
       WHEN @Subgroup = 'SENS' THEN 'S'
       WHEN @Subgroup = 'SENNo' THEN 'N'
   END

Can be simplified to:

SEN = CASE WHEN @Subgroup LIKE 'SEN[APSN]%' THEN SUBSTRING(@Subgroup, 4, 1) END