-3

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
) 
5
  • 2
    The table definitions and execution plan would help. Oh, and the exact SQL-Server version (2000, 2005, 2008, 2008R2, 2012). Nov 23, 2012 at 19:04
  • Sorry I can't give much informations. I need every single idea ;) Nov 23, 2012 at 19:22
  • 5
    ...as well as existing indexes, and whether you can add indexes... Nov 23, 2012 at 19:22
  • 6
    Do you really need to do a SELECT * ? That pretty much ensures that the query will have to either do a lot of key lookups, or go for a clustered index scan..... try to limit the number of columns that you return from your query, and explicitly specify those columns! That might open the opportunity for a covering index.
    – marc_s
    Nov 23, 2012 at 19:23
  • I'm not using SELECT * in the real query. I have to return like 30 columns. Maybe I should check if a covering index on some tables could speedup. Thx for the help Nov 26, 2012 at 17:58

2 Answers 2

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
3
  • Could you append a bit of explanation? Nov 24, 2012 at 9:43
  • I'm supposing the CROSS APPLY will be executed once and produce insane perfo? Nov 26, 2012 at 18:05
  • @Naster: CROSS APPLY is supposed to work faster in such cases.
    – a1ex07
    Nov 26, 2012 at 18:58
0

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))
1
  • Your right, the subquery is running for every row from the master query but it's not doing a CROSS JOIN. There are many rows of table 2 for 1 row of table 1. I have to get the first row of table 2 for the corresponding row of table 1. Doing the correct join in the join clause will in theory perform better then in the where clause because I have like 20 joins and the other joins will be executed on a filtered data. More info of the processing order : msdn.microsoft.com/en-us/library/ms189499.aspx Nov 26, 2012 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.