title: sql iteration vs group by, massive performance difference
body: <p>I've been given a simple task: to select 'id', min and max values for every unique id in the table. So I wrote simple <code>group by</code> but the query took ages to execute (30-60 seconds)</p>
  
  <pre><code>SELECT     CHPDataElement.DataElementID, MIN(CHPDataElementData.UTCDataTime) AS MinDataTime, MAX(CHPDataElementData.UTCDataTime) AS MaxDataTime
  FROM         CHPDataElement INNER JOIN
                        CHPDataElementData ON CHPDataElement.DataElementID = CHPDataElementData.DataElementID
  GROUP BY CHPDataElement.DataElementID
  
  order by 
  CHPDataElement.DataElementID
  </code></pre>
  
  <p>So I started working on improvement. And came up with simple iteration that returns the same data in 0.3-0.5 seconds.</p>
  
  <pre><code>declare @result table
  (
      DataElementID int,
      MinDataTime datetime NULL,
      MaxDataTime datetime null
  )
  
  declare @currentID int
  declare @nextID int
  declare @time datetime
  
  insert into @result (DataElementID, MinDataTime, MaxDataTime)
  select DataElementID,null,null from CHPDataElement
  order by DataElementID
  
  select top 1 @currentID=DataElementID from @result 
  
  while @currentID is not null
  begin
      print @currentID
  
  
      select top 1 @time=UTCDataTime  from CHPDataElementData
      where DataElementID = @currentID 
      order by UTCDataTime asc
  
      update @result set MinDataTime = @time
      where DataElementID = @currentID
  
      select top 1 @time=UTCDataTime  from CHPDataElementData
      where DataElementID = @currentID 
      order by UTCDataTime desc
  
      update @result set MaxDataTime = @time
      where DataElementID = @currentID
  
  
  
      set @nextID = null
      select top 1 @nextID=DataElementID from @result where DataElementID &gt; @currentID
      set @currentID = @nextID
  end
  
  select * from @result
  </code></pre>
  
  <p>Could anybody explain why the 'group by' is so inefficient compared to the second query?</p>