Optimisation sub query SQL Server

Question

I would like to optimise the query below because it's actually taking 40% of the time in the real one. I HAVE to get the first line in the table 2 for each line in the table 1. The relationship is 1 to N between the tables.

I also tried with a MIN() for the subquery but I did not had any gain.

I'm open to any solutions.

1. Is there any ways to put the PK of the table 2 inside an index and link directly the good row of the table 2 to the table 1?

2. Would you do a "performance table" with the good PK of table 2 in it?

3. Would you use a pregenerated/temp table?

4. How would Google do? I'm REALLY INTERESTED!

Query:

SELECT * 
FROM TABLE1 T1 
INNER JOIN TABLE2 T2 ON T2.PK = 
(
    SELECT TOP 1 sT2.PK 
    FROM TABLE2 sT2 
    WHERE sT2.FK = T1.PK
    ORDER BY sT2.DATE_X
) 

Answer 1

I'd try

SELECT * 
FROM TABLE1 T1 
CROSS APPLY 
(
  SELECT TOP 1 * FROM TABLE2 sT2 
  WHERE sT2.FK = T1.PK
  ORDER BY sT2.DATE_X
)b

Answer 2

I can't tell exactly what you're doing. I can't tell if you're trying to CROSS JOIN Table1 with a subset of Table2, or if there's actually a relationship there.

The query you listed looks like it's doing a complete implicit CROSS JOIN of Table1 and Table2 because your join condition ends up as T2.PK = T2.PK, but then you filter the second T2.PK with your subquery to those that have a matching T1.Fk. I have to think that the system is running your subquery for every row because it is written in such a confusing manner.

Try this:

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
   ON T1.Pk = T2.Fk
WHERE T2.Date_X = (SELECT MIN(Date_X) FROM Table2 WHERE Fk = T2.Fk)

Or this:

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
   ON T1.Pk = T2.Fk
WHERE T2.Pk IN (SELECT Pk FROM Table2 GROUP BY Fk HAVING Date_X = MIN(Date_X))