CODEWITHSUNDEEP
sqlperformancesql-server-2008
PSK
PSK

Reputation: 17943

SQL query optimization for multiple column comparison

I have written a SQL query in following two ways, as the underlying data is huge so both the queries are taking lot of time to fetch the data.

Approach 1:

 SELECT A.Col1, B.Col2
  FROM TableA A 
  LEFT JOIN TableB B
  ON ((A.C1= B.C1 AND A.C2= B.C2) OR (A.C1=B.C2 OR  A.C2 = B.C1))
  WHERE B.Col2 IS  NULL

Approach 2:

  SELECT A.Col1, B.Col2
  FROM TableA A 
  WHERE NOT  EXISTS
  (
  SELECT 1 FROM TableB B
  ((A.C1= B.C1 AND A.C2= B.C2) OR (A.C1=B.C2 OR  A.C2 = B.C1))
  )

Can anyone of you suggest a better approach of implementing this.

Upvotes: 2

Views: 174

Answers (1)

Ulises
Ulises

Reputation: 13429

Your queries look fine (although I don't think they are equivalent). However, I think the way to go here is to create indexes on A.C1, A.C2 and B.C1 and B.C2. This will greatly speed things up.

Upvotes: 1

Related Questions