My SQL Query Questions

Last Friday I posted a SQL query question to SO. It was about get records between boundaries. Quickly I got several good answers.

I noticed one comment about performance issue by Piotr Auguscik:

U can use max/min to get value u need. Order by +top 1 isnt best way to get max value, what i can see in ur querys. To sort n items its O(n to power 2), getting max should be only O(n)

This is very interesting comment. I think about it, and I realize it is true. To sort a list of n items, it will take about n power of 2 by using bubble sort algorithm; while just to find out the max, it will do one loop of n at most. This is a very simple computer science concept. However, if it is an index column in the query table, I think that it should have no difference between TOP 1 + ORDER BY and MAX + GROUP BY.

Today I raised another question related to this issue again to SO. I got a very nice explanation and a tip to optimize the query and tip on how to turn on statistics on IO and TIME.

Here are queries:

SELECT
  dv1.timestamp, dv1.value
FROM
  myTable AS dv1
WHERE
  dv1.value = @value
  dv1.timestamp
  BETWEEN (
    SELECT TOP 1 dv2.timestamp
    FROM myTable AS dv2
    WHERE dv2.value = @value AND
      dv2.timestamp <  '@START_DATE'
    ORDER BY dv2.timestamp DESC
  )
  AND (
    SELECT TOP 1 dv3.timestamp
    FROM myTable AS dv3
    WHERE dv3.value = @value AND
      dv3.timestamp <  '@END_DATE'
    ORDER BY dv3.timestamp ASC
  )
ORDER BY dv1.timestamp

-- alternative way

SELECT
  dv1.timestamp, dv1.value
FROM
  myTable AS dv1
WHERE
  dv1.value = @value
  dv1.timestamp
BETWEEN (
  SELECT MAX(dv2.timestamp)
  FROM myTable AS dv2
  WHERE dv2.value = @value
  GROUP BY dv2.value
)
AND (
  SELECT MIN(dv3.timestamp)
  FROM myTable AS dv3
  WHERE dv3.value = @value
  GROUP BY dv3.value
)
ORDER BY dv1.timestamp