2

Why does the sql query execute faster if I use the actual column names in the SELECT statement instead of SELECT *?

Tony
  • 9,672
  • 3
  • 47
  • 75
Liran Ben Yehuda
  • 1,568
  • 3
  • 12
  • 23
  • 1
    Are you saying `SELECT *` versus `SELECT list of all columns in the table`? If so there should be almost zero difference. If you mean `SELECT *` versus `SELECT list of only some of the columns in the table` then that is easily explained. – Martin Smith Apr 06 '11 at 12:08
  • 2
    Are you asking this question because you have seen a difference in a system you are using or because you have heard you should not use `SELECT *`? – Tony Apr 06 '11 at 12:11
  • what database product are you using? – Bob Jarvis - Слава Україні Apr 06 '11 at 13:02

4 Answers4

5

A noticeable difference at all seems odd... since I'd expect it to be a very minuscule difference and am intrigued to test it out.

Any difference might in a statement using Select * might be due to it taking extra time to find out what all of the column names are.

Tim
  • 1,549
  • 1
  • 20
  • 37
3

Because depending on the query it has to work out if there are unique names, what they all are, etc. Where as, if you specificy it, its all done for it.

BugFinder
  • 17,474
  • 4
  • 36
  • 51
  • But it still has to verify the specified columns exist etc. – Martin Smith Apr 06 '11 at 12:11
  • Oh it does, but if you've said tablea.id, tableb.something.. and theres id in both, it doesnt have to try and workout which id its using or if id is ambiguous, the difference should be little though Id have thought in terms of time. I'd love to seem some timings showing anything much than a couple of milliseconds. – BugFinder Apr 06 '11 at 13:46
1

Generally, the more you tell it, the less it has to calculate. This is the same for many systems.

Billy Moon
  • 57,113
  • 24
  • 136
  • 237
0

Its possible the performance is way better when you select certain column names than Select *, one good reason, just check whether, you have used the columns which are already indexed, in this case, the optimizer will make a plan to select all the data only from index instead from actual table. But check the plan once for sure.

Naga
  • 1
  • 1