Question: I have the following query (slightly amended for clarity):

```
CREATE PROCEDURE Kctc.CaseTasks_GetCaseTasks
@CaseNumber int
... other parameters
,@ChangedBefore datetime
,@ChangedAfter datetime
AS
SELECT Kctc.CaseTasks.CaseTaskId
  ...blah blah blah
  FROM Kctc.CaseTasks
  ... some joins here
  WHERE  
  ... some normal where clauses
  AND 
  (
    (@ChangedAfter IS NULL AND @ChangedBefore IS NULL)
    OR
    EXISTS (SELECT *
               FROM Kctc.FieldChanges
               WHERE Kctc.FieldChanges.RecordId = Kctc.CaseTasks.CaseTaskId AND 
                     Kctc.FieldChanges.TableName = 'CaseTasks' AND 
                     Kctc.FieldChanges.DateOfChange BETWEEN
                         ISNULL(@ChangedAfter, '2000/01/01') AND
                         ISNULL(@ChangedBefore, '2050/01/01'))
  )

```

This query times out whenever the user specifies values for `@ChangedBefore` or `@ChangedAfter`, therefore invoking the subquery.

The subquery checks for the existence of a record in the table called `FieldChanges` (which effectively records changes to every field in the `CaseTasks` table).

Querying `FieldChanges` isn't very efficient because it involves filtering on the text field `TableName` which isn't indexed. And I know that subqueries are inherently inefficient.

So my question in general is, is there a way to redesign the query so that it performs better? 

I can't think of a way to express the subquery as a join while still returning just one `CaseTask` row when there are multiple associated `FieldChanges` (i.e. preserving the EXISTS semantic). I haven't yet indexed the `TableName` field of the `FieldChanges` table because I'm hesitant about indexing text fields.

So what should I do?

Answer: 
As a first cut you might try putting an index on the table Kctc.FieldChanges on the RecordId, TableName, and DateOfChange fields (one single index with all three fields) and see if that helps.

Share and enjoy.
This is **not going to be a "good" solution**, but it might be better than what's happening right now: 

```
SELECT Kctc.CaseTasks.CaseTaskId
  ...blah blah blah
  FROM Kctc.CaseTasks
  ... some joins here
  LEFT JOIN (
     SELECT RecordID
     FROM Kctc.FieldChanges
     WHERE Kctc.FieldChanges.TableName = 'CaseTasks'
     AND Kctc.FieldChanges.DateOfChange BETWEEN
                         ISNULL(@ChangedAfter, '2000/01/01') AND
                         ISNULL(@ChangedBefore, '2050/01/01')
     GROUP BY RecordID
  ) AS MatchingChanges ON Kctc.CaseTasks.RecordId = MatchingChanges.RecordId
  WHERE  
  ... some normal where clauses
     AND (MatchingChanges.RecordID Is Not Null OR ((@ChangedAfter IS NULL AND @ChangedBefore IS NULL))

```

Depends on exactly what the query plan is - if it was performing the subquery repeatedly, this formulation might help.