Sql: Speed Improvement - Left Join On Cond1 Or Cond2

August 09, 2024 Post a Comment

SELECT DISTINCT a.*, b.* FROM current_tbl a LEFT JOIN import_tbl b ON ( a.user_id = b.user_id OR ( a.f_name||' '||a.l_nam

Solution 1:

Looks like you can easily avoid the string concatenation:

OR ( a.f_name||' '||a.l_name = b.f_name||' '||b.l_name)

Change it to:

OR ( a.f_name = b.f_name AND a.l_name = b.l_name)

Solution 2:

Rather than concatenating first and last name and comparing them, try comparing them individually instead. Assuming you have them (and you should create them if you don't), this should improve your chances of using indexes on the first name and last name columns.

SELECTDISTINCT  a.*, b.*FROM             current_tbl a
LEFTJOIN        import_tbl  b 
                 ON ( a.user_id = b.user_id 
                   OR (a.f_name = b.f_name and a.l_name = b.l_name)
                 )

Solution 3:

If people's suggestions don't provide a major speed increase, there is a possibility that your real problem is that the best query plan for your two possible join conditions is different. For that situation you would want to do two queries and merge results in some way. This is likely to make your query much, much uglier.

One obscure trick that I have used for that kind of situation is to do a GROUP BY off of a UNION ALL query. The idea looks like this:

SELECT a_field1, a_field2, ...
  MAX(b_field1) as b_field1, MAX(b_field2) as b_field2, ...
FROM (
      SELECT a.field_1 as a_field1, ..., b.field1 as b_field1, ...
      FROM current_tbl a
        LEFT JOIN import_tbl b
          ON a.user_id = b.user_id
    UNION ALL
      SELECT a.field_1 as a_field1, ..., b.field1 as b_field1, ...
      FROM current_tbl a
        LEFT JOIN import_tbl b
          ON a.f_name = b.f_name AND a.l_name = b.l_name
  )
GROUPBY a_field1, a_field2, ...

And now the database can do each of the two joins using the most efficient plan.

(Warning of a drawback in this approach. If a row in current_tbl joins to multiple rows in import_tbl, then you'll wind up merging data in a very odd way.)

Incidental random performance tip. Unless you have reason to believe that there are potential duplicate rows, avoid DISTINCT. It forces an implicit GROUP BY, which can be expensive.

Solution 4:

I don't really understand why you're concatenating those strings. Seems like that's where your slowdown would be. Does this work instead?

SELECTDISTINCT  a.*, b.*FROM             current_tbl a 
LEFTJOIN        import_tbl  b  
                 ON ( a.user_id = b.user_id  
                   OR ( a.f_name = b.f_name AND a.l_name = b.l_name) 
                )

Solution 5:

Here is Yet Another Ugly Way To Do It.

SELECT a.*
  , CASEWHEN b.user_id IS NULL THEN c.field1 ELSE b.field1 ENDas b_field1
  , CASEWHEN b.user_id IS NULL THEN c.field2 ELSE b.field2 ENDas b_field2
  ...
FROM current_tbl a
  LEFT JOIN import_tbl b
    ON a.user_id = b.user_id
  LEFT JOIN import_tbl c
    ON a.f_name = c.f_name AND a.l_name = c.l_name;

This avoids any GROUP BY, and also handles conflicting matches in a somewhat reasonable way.

Lab Sql