title: SQL inner join, execution time
body: <p>I have this painstakingly slow query, and have no clue what the problem is. I have tried adding multiple where clauses but with no effect. Execution time is well over 5 minutes.</p>
  
  <p>Goal is to select the last time that a product in LM_MAT is used. This is determined from the stock mutations; LMH_MAT. LMH_MAT does have 160K records, but doing a Select * from lmh_mat takes only 16 ms. </p>
  
  <p>I have determined the solution to do so from <a href="http://blog.twinharbor.com/2010/11/16/sql-joins-that-limit-results-from-one-of-the-tables/" rel="nofollow">this site</a>.</p>
  
  <p>Removing the second sub-query (select top 1...) does bring the execution time to just under 6 seconds, put removes the entire purpose: selecting the last time a product is used.</p>
  
  <p>Platform: Sybase Advantage Data Architect. </p>
  
  <p>I hope someone can help me!</p>
  
  <p>Kind regards,
  Lucas</p>
  
  <p>The query:</p>
  
  <pre class="lang-SQL prettyprint-override"><code>select
   lm_matn,
   LM_OMSCH,
   lm_hoev,
   lm_minh,
   lm_preh,
   lm_datin, 
   XDate
  from lm_mat A
  left join (select lmh_matn, lmh_id, cast(lmh_date as sql_date) as XDate
             from lmh_mat) F
             on A.lm_matn = F.lmh_matn 
             AND F.lmh_id in 
               (select top 1 FF.lmh_id 
                from lmh_mat FF 
                where FF.lmh_matn=A.lm_matn 
                order by FF.lmh_date desc)
  group by 
    lm_matn,
    LM_OMSCH,
    lm_hoev,
    lm_minh, 
    lm_preh,
    lm_datin, 
    XDate
  </code></pre>