title: Sybase query optimization
body: <p>I'm seeing how we can improve the performance of the following sybase query. Currently it takes about 1.5 hrs.</p>
  
  <pre><code> CREATE TABLE #TempTable
   (
   T_ID numeric,
   M_ID numeric,
   M_USR_NAME char(10),
   M_USR_GROUP char(10),
   M_CMP_DATE datetime,
   M_CMP_TIME numeric,
   M_TYPE char(10),
   M_ACTION char(15),
   )
  
  
  select
  T.M_USR_NAME,
  T.M_USR_GROUP,
  T.M_CMP_DATE,
  T.M_CMP_TIME,
  T.M_TYPE,
  T.M_ACTION
  from #TempTable T, AUD_TN B
  where T.M_ID=B.M_ID
  and T.T_ID in 
  (
     select M_NB from TRN H where (M_BENTITY ="KROP" or M_SENTITY = "KROP")     
  )
  
  UNION
  
  select
  A.M_USR_NAME,
  A.M_USR_GROUP,
  A.M_DATE_CMP,
  A.M_TIME_CMP,
  A.M_TYPE,
  A.M_ACTION
  from AUD_VAL A, TRN H
  where A.M_DATE_CMP  &gt;= '1 May 2012' and A.M_DATE_CMP &lt;= '31 May 2012'
  and A.M_ACT_NB0=H.M_NB
  and (H.M_BENTITY ="KROP" or H.M_SENTITY = "KROP") 
  
  UNION
  
  select
  TR.M_USR_NAME,
  TR.M_USR_GROUP,
  TR.M_DATE_CMP,
  TR.M_TIME_CMP,
  TR.M_TYPE,
  TR.M_ACTION
  
  from TRN_AUD TR, TRN H
  where TR.M_DATE_CMP  &gt;= '1 May 2012' and TR.M_DATE_CMP &lt;= '31 May 2012'
  and TR.M_ACT_NB0=H.M_NB
  and (H.M_BENTITY ="KROP" or H.M_SENTITY = "KROP") 
  
  DROP table #TempTable
  </code></pre>
  
  <p>Any help is greatly appreciated. Please note the following</p>
  
  <p>The only table which is not indexed above is AUD_TN</p>
  
  <p>Cheers
  RC</p>