Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
) 
share|improve this question
2  
The table definitions and execution plan would help. Oh, and the exact SQL-Server version (2000, 2005, 2008, 2008R2, 2012). –  ypercube Nov 23 '12 at 19:04
    
Sorry I can't give much informations. I need every single idea ;) –  Naster Nov 23 '12 at 19:22
5  
...as well as existing indexes, and whether you can add indexes... –  Aaron Bertrand Nov 23 '12 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 '12 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 –  Naster Nov 26 '12 at 17:58
add comment

2 Answers

up vote 1 down vote accepted

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

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))
share|improve this answer
    
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 –  Naster Nov 26 '12 at 18:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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