It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table.
In code:
SELECT
TempTable.Col1,
TempTable.Col2,
TempTable.Col3,
JoinedTable.Col1,
JoinedTable.Col2,
(
SELECT TOP 1
ThirdTable.Col1 -- Which is ThirdTable's Primary Key
FROM
ThirdTable
WHERE
ThirdTable.SomeColumn = JoinedTable.SomeColumn
) as ThirdTableColumn1,
(
SELECT TOP 1
ThirdTable.Col1 -- Which is also ThirdTable's Primary Key
FROM
ThirdTable
WHERE
ThirdTable.SomeOtherColumn = JoinedTable.SomeColumn
) as ThirdTableColumn2,
FROM
#TempTable as TempTable
LEFT JOIN
JoinedTable
ON (TempTable.PKColumn1 = JoinedTable.PKColumn1 AND
TempTable.PKColumn2 = JoinedTable.PKColumn2)
WHERE
JoinedTable.WhereColumn IN (1, 3)
This is an exact replica of my query.
If I remove the two sub selects, it runs just fine and quickly. With the two sub selects, I get about 100 records per second, which is extremely slow for this query because it should return almost a million records.
I've checked to see if every table has a Primary Key, they all do. They all have Indexes AND statistics for their important columns, like the ones in those WHERE clauses, and the ones in the JOIN clause. The only table with no primary key defined nor index is the temporary table, but it's not the problem either because it's not the one related to the slow sub selects, and as I mentioned, with no sub selects it runs just fine.
Without those TOP 1
it returns more than one result, and raises an error.
Help, anyone?
EDIT:
So the execution plan told me I was missing an Index. I've created it, and recreated some of the other indexes. After a while, the execution plan was using them, and the query now runs fast. The only problem is I'm not succeeding in doing this again on another server, for the same query. So my solution will be to HINT which index SQL Server will use.