title: TSQL Performance issues using DATEADD in where clause
body: <p>I have a query using the DATEADD method which takes a lot of time.
  I'll try to simplify what we do.
  We are monitoring tempretures and every 5 minutes we store the highest temp and lowest temp in 
  table A</p>
  
  <p>Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;MaxTemp&nbsp;|&nbsp;MinTemp<BR>
  2011-09-18  |   12:05:00   |   38.15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;38.099<BR>
  2011-09-18  |   12:10:00   |   38.20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;38.10<BR>
  2011-09-18  |   12:15:00   |   38.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;38.17<BR>
  2011-09-18  |   12:20:00   |   38.21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;38.20<BR>
  ...<BR>
  2011-09-19  |   11:50:00   |   38.17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;38.10<BR>
  2011-09-19  |   12:55:00   |   38.32&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;38.27<BR>
  2011-09-19  |   12:00:00   |   38.30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;38.20<BR></p>
  
  <p>Date/Time columns are of type date/time (and not datetime)</p>
  
  <p>In another table (Table B) we store some data for the entire day, where a day is from NOON (12PM) to noon (not midnight to midnight).</p>
  
  <p>So table B columns include:
  Date (date only no time)<BR>
  ShiftManager<BR>
  MaxTemp (this is the max temp for the entire 24 hours starting at that date noon till next day noon)<BR>
  MinTemp<BR></p>
  
  <p>I get table B with all the data and just need to update the MaxTemp and MinTemp using table A<BR>
  For example:For 09/18/2011 I need the maximum temp reading that was between 09/18/2011 12PM and 09/19/2011 12PM.<BR>
  In the TableA sample we have above, the returend result would be 38.32 as it is the MAX(MaxTemp) for the desired period.</p>
  
  <p>The SQL I'm using:</p>
  
  <pre><code>update TableB
  set MaxTemp = (
  select MAX(HighTemp) from TableA
  where
  (Date=TableB.Date and Time &gt; '12:00:00') 
  or 
  (Date=DATEADD(dd,1,TableB.Date) and Time &lt;= '12:00:00')
  )
  </code></pre>
  
  <p>And it takes a lot of time (if I remove the DATEADD method it is quick).</p>
  
  <p>Here is a simplified sample that shows the data I have and the expected result:<BR></p>
  
  <pre><code>DECLARE @TableA TABLE ([Date] DATE, [Time] TIME(0), HighTemp DECIMAL(6,2));
  DECLARE @TableB TABLE ([Date] DATE, MaxTemp DECIMAL(6,2));
  
  INSERT @TableA VALUES
  ('2011-09-18','12:05:00',38.15),
  ('2011-09-18','12:10:00',38.20),
  ('2011-09-18','12:15:00',38.22),
  ('2011-09-19','11:50:00',38.17),
  ('2011-09-19','11:55:00',38.32),
  ('2011-09-19','12:00:00',38.31),
  ('2011-09-19','12:05:00',38.33),
  ('2011-09-19','12:10:00',38.40),
  ('2011-09-19','12:15:00',38.12),
  ('2011-09-20','11:50:00',38.27),
  ('2011-09-20','11:55:00',38.42),
  ('2011-09-20','12:00:00',38.16);
  
  INSERT @TableB VALUES
  ('2011-09-18', 0),
  ('2011-09-19', 0);
  
  -- This is how I get the data, now I just need to update the max temp for each day
  
  with TableB(d, maxt) as
  (
  select * from @TableB
  )
  update TableB
  set maxt = ( 
  select MAX(HighTemp) from @TableA 
  where
  (Date=TableB.d and Time &gt; '12:00:00')
  or
  (Date=DATEADD(dd,1,TableB.d) and Time &lt;= '12:00:00')
  )
  
  select * from @TableB
  </code></pre>
  
  <p>Hope I was able to explian myself, any ideas how can I do it differently? Thx!</p>