We have an large table (4 million rows +) that we query frequnetly

most queries run fine using a non-clustered index on 5 columns (call them a,b,c,d,e !!) - a is a datetime field that cuts down most of the rows

however we have one query that joins to a second mush smaller table to get the values for c,d,e

ie SELECT * from big_table big INNER JOIN little_table little
ON big.c = little.c and big.d = little.d and big.e = little.e
WHERE big.a = 'date' and little.f = 'value'

Now the problem is that it only uses the index to select on date(a) before filtering in the c,d,e restraints - execution time is in the 45 second region as opposed to 1 second where we specify the values of c,d,e in the query explicitly

Can I force the query to calculate the possible values of c,d,e first and then use this to retrieve the rows required - I can only think of building up the values using a cursor first and then passing this into the statement, whoch seems very clunky