Optimize right joins

Question

The following query is working as expected. But I guess there is enough room for optimization. Any help?

SELECT   a.cond_providentid,
      b.flag1
FROM     c_master a
WHERE    a.cond_status = 'OnService'
ORDER BY a.cond_providentid,
         a.rto_number;

Answer 1

May I suggest placing the query within your left join in a database view - in that way, the code can be much more cleaner and easier to maintain.

Also, check the columns that you often use the most.. it could be a candidate for indexing so that when you run your query, it can be faster.

You also might check your column data types... I see that you have this type of code:

(CASE WHEN b.tray_type IS NULL THEN 1 ELSE 0 END) flag2

If you have a chance to change the design for your tables, (i.e. b.Tray_Type to bit, or use a computed column to determine the flag) it would run faster because you don't have to use Case statements to determine the flag. You can just add it as another column for your query.

Hope this helps! :)

Ann