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.
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?
Would you do a "performance table" with the good PK of table 2 in it?
Would you use a pregenerated/temp table?
How would Google do? I'm REALLY INTERESTED!
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 )