CODEWITHSUNDEEP
sqlsql-serveroracle-databasecommon-table-expression
juergen d
juergen d

Reputation: 204756

Optimize CTE query

I have a big join of 15 tables. Simplified it looks like this

WITH CTE AS
(
  SELECT A.ID,
         B.K,
         X.Date,
         X.ID,
         ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY X.date ASC, x.id ASC) RN
  FROM A
  INNER JOIN B ON A.ID = B.ID        
  INNER JOIN C ON B.ID = C.ID    
  LEFT JOIN X ON (X.G = A.ID
                 OR X.H = B.ID
                 OR X.I = C.ID)
)
SELECT * 
FROM CTE
WHERE RN = 1

Execution time is about 1 second mostly because of the last JOIN of X. To avoid duplicates I added the ROW_NUMBER part and turned it into a CTE.

How could I optimize this preferably using ANSI SQL since it is running on SQL-Server and Oracle?

Upvotes: 0

Views: 668

Answers (1)

juergen d
juergen d

Reputation: 204756

I got it. It is super fast like this (0.1 seconds):

  SELECT A.ID,
         B.K,
         X.DATE,
         X.ID,
  FROM A
  INNER JOIN B ON A.ID = B.ID        
  INNER JOIN C ON B.ID = C.ID    
  LEFT JOIN 
  (
     SELECT G, H, I, ID, DATE,
     ROW_NUMBER() OVER (PARTITION BY G ORDER BY DATE ASC, ID ASC) RN
     FROM X
  ) X ON RN = 1 AND (X.G = A.ID
                     OR X.H = B.ID
                     OR X.I = C.ID)

Upvotes: 4

Related Questions