Queries are fast separtely, but slow when joined via subquery

I'm a part-time accidental DBA and I've run into problem. Unfortunately, do to some restrictions on how the software is running, I can't do the obvious and turn the sub-query into a join. Is there a way to index this thing to have it perform better?

I have select statement that on its own, takes about 2 seconds to return 28000 records. When I add a subquery to the SELECT, it explodes into over a minute and change.

The subquery, separately, also takes about 2 seconds to return data.

ADDENDUM: The XML for the execution plan.

The OpenIssuesTBL.AssignedBy and OpenIssuesTBL.Resp columns are not stored as wild cards. We pass variables in to them in code, the wildcards are just so I can grab all the records in a similar way as the 'show all records' button we have.

To explain the design a little bit, our code can add additional columns on the fly by using these sub-queries in the select statement. We pull them from another table and just insert them into the query we use to populate a search screen. Most of the time, this works without a problem. For some reason, this query is just dog slow.

```
SELECT OpenIssuesTBL.IssueID
, OpenIssuesTBL.Category
, qryCOCompanyDBQualUnion.CompanyName
, CASE
WHEN bitConfidential = 0
THEN OpenIssuesTBL.Issue
ELSE 'Confidential'
END AS Issue
, OpenIssuesTBL.OpenDate
, OpenIssuesTBL.Priority
, OpenIssuesTBL.Duration
, OpenIssuesTBL.DueDate
, OpenIssuesTBL.Resp
, CASE
WHEN bitConfidential = 0
THEN OpenIssuesTBL.Progress
ELSE 'Confidential'
END AS Progress
, OpenIssuesTBL.ClosedDate AS StatusDate
, OpenIssuesTBL.AssignedBy
, DBQualityUtilityTBL.CompanyName AS Site
, tblIssueTypeList.strIssueType
, OpenIssuesTBL.strIssueStatus
, OpenIssuesTBL.strProductNum
, OpenIssuesTBL.ynSystemGenerated
, OpenIssuesTBL.strSuggestion
, OpenIssuesTBL.strSource
, OpenIssuesTBL.memIssueDialog
, tblIssueTimeLookup.strTimeD

Your Select statement with TOP 1 in a sub query is a killer. When you are running your query, this statement will run for each row. depending on number of rows your main statement is returning, you can imagine the time it is going to take. In my opinion, what you can try is using your view's Select statement with GROUP BY as common table expression (or change the view to use group by, if possible) so it always returns a top record a particular Id. (I can not see your table design so I am not sure which aggregate you can use to GROUP BY your statement but May be group it with MAX(IssueId)) once you have that, all you need is simple INNER JOIN or LEFT OUTER JOIN in your main query.

Hope this is making sense.

StackExchange Database Administrators Q#116384, answer score: 2