Optimization of a SQL LEFT OUTER JOIN without index

Question

I have a SQL request that produce a view on my SQL Server that I need to optimize :

SELECT some_stuff 
FROM some_table 
INNER JOIN another_table
       ON some_condition
LEFT OUTER JOIN third_table
       ON third_table.responsetype = another_table.eventcode
WHERE  ( another_table.externalsystemreferenceid <> '' )

From the execution plan in SQL server, it is the LEFT OUTER JOIN that takes much of the time (83%).

My first idea was to put an index on another_table.eventcode (there is already one on third_table.responsetype) but this table is frequently updated and the cost of having to update an index very often is probably not worth it.

So I have two questions :

• Is there a way to optimize the LEFT OUTER JOIN without index and how ?
• (A more theorical one to better understand index) Let's say I want to use index without regard to the impact, on which part should I put it and why ?
  • ON third_table.responsetype = another_table.eventcode (on which one? both?) ?
  • or WHERE ( another_table.externalsystemreferenceid <> '' ) ?

Thanks in advance for any response,

Answer 1

The indexes that you want are:

• thirdtable(responsetype, . . . ) -- the . . . is for the other referenced columns
• anothertable(externalsystemreferenceid, eventcode . . )

The second index is probably not helpful, unless almost all the values are blank in anothertable.

You should check to see if the index updates on thirdtable are expensive. If this is an issue, you may need two copies of your database -- one for transaction processing and another for queries such as the one you want to implement.

Answer 2

You could use a composite index on table another_table

columns  (  eventcode, externalsystemreferenceid ) 

and you should also add to the composite index also the another_table column that are involved in your JOIN another_table ON some_condition