title: SQL Optimize query to get max value
body: <p>I have the following query. The part where I do the </p>
  
  <pre><code>     select for max(Entered_On)..... 
  </code></pre>
  
  <p>takes the longest. If I comment out the where cause, it run fast. </p>
  
  <pre><code>      Select   dlp.ParamID, dp.ParamName   
        from data_LocP  dlp 
        inner join data_In  di on dlp.LocID = di.LocID
        inner join data_Parms dp on dp.ParamID = di.ParamID
        inner join map_Loc ml on ml.LocId =  dlp.LocId
        where di.Entered_On = (select max(Entered_On ) from data_In where LocId = dlp.LocID
        and ParamId = dlp.ParamID)
  </code></pre>
  
  <p>I was wondering if there is a way to optimize this query as the part where I get the Max(Entered_On) takes the longest time. As a FYI, I need the Entered_On to be the max date for a given LocId and ParamId.</p>
  
  <p>Here is a more extended code but does not give the max:</p>
  
  <pre><code>      select   *  FROM
        (
          SELECT dlp.ParamID, dp.ParamName, dlp.LocID,  ml.LocName , di.Entered_On, (GETUTCDATE() -  dlp.FreqDays) DueDate, dlp.FreqDays,  
          a.CompanyId, a.SiteID, 
            ROW_NUMBER() OVER (PARTITION BY dlp.LocId, dlp.ParamID 
                            ORDER BY di.Entered_On DESC)
               as RowNum            
  
        from data_LocParams dlp 
        inner join data_Input di on dlp.LocID = di.LocID
        inner join data_Parameters dp on dp.ParamID = di.ParamID
        inner join map_Locations ml on ml.LocId =  dlp.LocId
        left join
       (
         select ml.LocId,ms.CompanyId, ms.SiteId  
         from map_Sites ms 
         join map_WaterSystems mw
         on ms.SiteID = mw.SiteID
         join map_Locations ml
         on ml.SysID = mw.SysID
        ) a 
        on a.LocId = dlp.LocID   
        where   dlp.FreqDays is not null AND dlp.FreqDays &lt;&gt; ''      
     ) as a WHERE a.RowNum = 1 and Entered_On &lt; (GETUTCDATE() -   FreqDays)
  </code></pre>