Question: **Problem**

So the situation that I am facing here with this SQL Query, is that it is taking about 12 seconds to run making the screen super slow.

**Goal**

Do the necessary changes in order to improve the performance and make it faster. I was thinking about instead of the `OR` in the Where clause to use the `UNION`?

```
SELECT Tool.*, Interview.*
    FROM Tool
INNER JOIN Interview ON Interview.Id = Tool.InterviewId
WHERE (Tool.ToolTypeId = @ToolTypeId 
AND Tool.Is_Active = 1
AND Tool.InterviewId = @InterviewId
AND Tool.ToolId = @ToolId
AND Tool.CustomerId = @CustomerId)

OR Tool.Id = (
    SELECT TOP 1 SubTool.Id
            FROM Tool SubTool
    INNER JOIN Interview subInterview ON subInterview.Id = SubTool.ToolId 
            WHERE SubTool.ToolTypeId = @ToolTypeId
            AND SubTool.Is_Active = 1  
            AND SubTool.InterviewId != @InterviewId  
            AND SubTool.ToolId = @ToolId
            AND subTool.CustomerId = @CustomerId
            AND convert(datetime, subTool.DateTime, 120) < @ToolDateTime
            ORDER BY subTool.DateTime DESC, subTool.StartDate DESC, 
                subTool.EndDate, subTool.Id DESC
)

    ORDER BY Tool.StartDate, Tool.Id

```

***NOTE:*** I believe the actual query output is not necessary in this case, since we are looking for some structural issues that might be impacting the performance.

Answer: 
I would suggest rephrasing the query to eliminate the subquery in the `WHERE` clause.

If you are looking for one row in the result set regardless of conditions, you can use:

```
SELECT TOP (1) Tool.*, Interview.*
FROM Tool JOIN
     Interview
     ON Interview.Id = Tool.InterviewId
WHERE Tool.ToolTypeId = @ToolTypeId AND
      Tool.Is_Active = 1
      Tool.ToolId = @ToolId AND
      Tool.CustomerId = @CustomerId AND
      (Tool.InterviewId = @InterviewId OR
       convert(datetime, Tool.DateTime, 120) < @ToolDateTime
      )
ORDER BY (CASE WHEN Tool.InterviewId = @InterviewId THEN 1 ELSE 2 END),
         Tool.DateTime DESC, sTool.StartDate DESC, Tool.EndDate, Tool.Id DESC;

```

Your final `ORDER BY` suggests that you are expecting more than one row for the first condition. So, you can use a subquery and window functions:

```
SELECT ti.*
FROM (SELECT Tool.*, Interview.*,
             COUNT(*) OVER (PARTITION BY (CASE WHEN Tool.InterviewId = @InterviewId THEN 1 ELSE 0 END)) as cnt_interview_match,
             ROW_NUMBER() OVER (ORDER BY subTool.DateTime DESC, subTool.StartDate DESC, subTool.EndDate, subTool.Id DESC) as seqnum
      FROM Tool JOIN
           Interview
           ON Interview.Id = Tool.InterviewId
      WHERE Tool.ToolTypeId = @ToolTypeId AND
            Tool.Is_Active = 1
            Tool.ToolId = @ToolId AND
            Tool.CustomerId = @CustomerId AND
            (Tool.InterviewId = @InterviewId OR
             convert(datetime, Tool.DateTime, 120) < @ToolDateTime
            )
      ) ti
WHERE InterviewId = @InterviewId OR
      (cnt_interview_match = 0 AND seqnum = 1);

```

Note that the subquery requires that the columns have different names, so you might need to fiddle with that.

Then, you want an index on `TOOL(ToolTypeId, Is_Active, ToolId, CustomerId, InterviewId, DateTime)`. I assume that `Interview(Id)` is already indexed as the primary key of the table.
My interpretation of what your query does (and by inference, what you want it to do) is different from @GordonLinoff's.

---

Gordon's queries could be paraphrased as...

* If there are rows with `Tool.InterviewId = @InterviewId`...
	+ Return those rows and only those rows
* If there are no such rows to return...
	+ Return the latest row for other `InterviewId`'s

---

My understanding of what your query actually ***does*** is that you want both possibilities returned together, at all times.

* This ambiguity is an example of why you ***really*** should include example data.

For example, this would be a re-wording or your existing query *(using `UNION` as per your own suggestion)*...

```
WITH
  filter_tool_customer AS
(
    SELECT Tool.*, Interview.*
      FROM Tool
INNER JOIN Interview ON Interview.Id = Tool.InterviewId
     WHERE Tool.ToolTypeId = @ToolTypeId 
       AND Tool.Is_Active  = 1
       AND Tool.ToolId     = @ToolId
       AND Tool.CustomerId = @CustomerId
),
  matched AS
(
  SELECT *
    FROM filter_tool_customer
   WHERE InterviewId = @InterviewId
),
  mismatched AS
(
  SELECT TOP 1 *
    FROM filter_tool_customer
   WHERE InterviewId <> @InterviewId
     AND DateTime    <  CONVERT(VARCHAR(20), @ToolDateTime, 120)
ORDER BY DateTime DESC,
         StartDate DESC,
         EndDate,
         Id DESC
),
  combined AS
(
  SELECT * FROM matched
  UNION ALL
  SELECT * FROM mismatched
)
SELECT * FROM combined ORDER BY StartDate, Id

```