Geek asks and answers

Performance difference: select top 1 order compared to min (val)

The question is simple. Which request will be faster:

SELECT TOP 1 value FROM table ORDER BY value

or

SELECT TOP 1  MIN(value) FROM table

We can assume that we have two cases: case 1. There is no index and case 2. With an index by value.
Any ideas appreciated. Thank you

+5
source share
1 answer

In the absence of an index:

  • MIN (value) should be implemented O (N) times with one scan;
  • TOP 1 ... ORDER BY will require O (N Log N) time due to the specified sorting (unless the DB engine is smart enough to read intentions that I would not want to rely on in production code).

When an index exists:

  • Both should only require O (1) times using the index.
+9

More articles:


All Articles