OPTIMIZING LEFT JOIN in SQL SERVER
<pre><code>SELECT h.*
FROM  h
 LEFT JOIN  au ON au.ID = h.Key AND au.RaID = 40190 AND h.EntityType = 'Detail'
 LEFT JOIN  ip ON ip.ID = h.Key AND ip.RaID = 40190 AND h.EntityType = 'itempart'
WHERE 
coalesce(au.id,ip.id) is not null
</code></pre>

<p>Anyone have a good idea how to optimize this? h is HUGE history log table.</p>