• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select * vs select column1,column2 ..

 
sumitha sudhakar
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear friends,

which is better select * or select column1,column2 ... which leads to better performance.
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That kind of depends on the table in question and the data set required.
If your table has only 2 columns, both select * and select column1,column2 shouldnt be different from one another in terms of performance.

But, say your table has 100 columns and you need only 2 in your result set, that would mean that you are asking for a lot of data back when you are going to use only 2% of it. Moreover, if some of these columns are large in size (say BLOBs) your performance might take a noticeable hit.

Besides, explicitly mentioning the columns you require makes for better reading and hence better maintainability.
 
sumitha sudhakar
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the response.
i meant to ask
select column1,column2...column15 from table
Vs
select * from table .
having around 15 columns.
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Like I said, there shouldnt be much of a difference between the two, if you are retrieving all the columns in the table. There might be an extra overhead in the select * query where the database has to determine what columns exist in the table.
 
sumitha sudhakar
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the information.
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sridhar Santhanakrishnan wrote:That kind of depends on the table in question and the data set required.
If your table has only 2 columns, both select * and select column1,column2 shouldnt be different from one another in terms of performance.

But, say your table has 100 columns and you need only 2 in your result set, that would mean that you are asking for a lot of data back when you are going to use only 2% of it. Moreover, if some of these columns are large in size (say BLOBs) your performance might take a noticeable hit.

Besides, explicitly mentioning the columns you require makes for better reading and hence better maintainability.


Specifying the columns is good, because if the table definition changes your code using that SQL statement will still work. The performance differences are a red herring. Most databases and drivers do not return binary data until it is accessed, so even if you select a field with blob data in in then don't use this data you are unlikely to see much, if any, difference in the size of the data in memory or the time taken to retrieve it. Its simplistic to think of databases accessing data on a per field basis; that's not how they work. They will access data using a different block (e.g. pages) which you cannot directly map to a logical thing (e.g. a field or a row). The usual bottleneck in database access is performance of the query, not the performance of the data retrieval. Try running a query with different types of selects - selecting columns and selecting * - and getting an explain plan for them. I would be very surprised if there were any difference in the execution.
 
Kiaamaa Liammes
Ranch Hand
Posts: 32
Eclipse IDE Java Objective C
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * also brings back the Who fields like insert date , user id .. etc which are generally not required .

I found this interesting article :

http://stackoverflow.com/questions/208925/is-there-a-difference-betweeen-select-and-select-list-each-col
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is an important note in the article pointed to by Kiaamaa. In some databases, if there is an index defined on selected columns, the query might be answered by reading just the index, skipping the table, therefore potentially allowing the database to use a more efficient path to access and return all required data. Of course, if all columns from a table are being selected, then there is no difference.

I personally strongly prefer to explicitly name the columns in the select clause, even if all of them are being selected. It servers as a self-documenting feature to me as to which columns are really processed by the code and furthermore allows me to search for all usages of a table or column throughout my project (I use constants for every table and column in my schema, which makes this feasible).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic