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,

Gordon Linoff · Accepted Answer · 2019-03-05 13:03:10Z

2

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.

answered Mar 5, 2019 at 13:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave Smith Over a year ago

thanks for the reply, just to be clear, by "second index" you mean an index on externalsystemreferenceid ? why would it not be helpful on eventcode ?

Gordon Linoff Over a year ago

@DaveSmith . . . Because most rows probably pass the WHERE condition.

ScaisEdge · Accepted Answer · 2019-03-05 13:03:02Z

1

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

answered Mar 5, 2019 at 13:03

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Optimization of a SQL LEFT OUTER JOIN without index

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related