2

I need to get a record of value and timestamp by the max of timestamp. The combination of value and timestamp is a primary key. It seems that there are two ways to get max/min value. One query example is by using TOP 1 + ORDER BY:

SELECT TOP 1
  value, timestamp
FROM myTable
WHERE value = @value
ORDER BY timestamp DESC

Another one is by MAX() + GROUP BY:

SELECT value, max(timestamp)
FROM myTable
WHERE value = @value
GROUP BY value

Is the second one is better than the first one in terms of performance? I read one person's comment of "to sort n items by first one is O(n power of 2), second O(n)" to my previous question. How about the case I have index on both value and timestamp?

Community
  • 1
  • 1
David.Chu.ca
  • 37,408
  • 63
  • 148
  • 190

2 Answers2

3

If you don't have a composite index on (value, timestamp) then they will be poor and probably equally poor at that.

With an index, they'll probably be the same thanks to the Query Optimiser.

You can also quickly test for yourself by using these to see resources used:

SET STATISTICS IO ON
SET STATISTICS TIME ON

...but the best way is to use the Graphical Execution Plans

You should see huge differences in the IO + CPU with and without an index especially for larger table.

Note: You have a 3rd option

SELECT @value AS value, max(timestamp)
FROM myTable
WHERE value = @value

This will return a NULL for no rows which does make it slightly different to the other two

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

For anyone who finds this in a search and wants to know about Postgres (not applicable to OP), if the column is indexed the plans will be identical.

Andrew Lazarus
  • 18,205
  • 3
  • 35
  • 53