aspose file tools*
The moose likes JDBC and the fly likes select * vs select column1,column2 .. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select * vs select column1,column2 .." Watch "select * vs select column1,column2 .." New topic
Author

select * vs select column1,column2 ..

sumitha sudhakar
Greenhorn

Joined: Aug 09, 2010
Posts: 21
Dear friends,

which is better select * or select column1,column2 ... which leads to better performance.
Sridhar Santhanakrishnan
Ranch Hand

Joined: Mar 20, 2007
Posts: 317
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

Joined: Aug 09, 2010
Posts: 21
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

Joined: Mar 20, 2007
Posts: 317
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

Joined: Aug 09, 2010
Posts: 21
Thanks for the information.
Sridhar Santhanakrishnan
Ranch Hand

Joined: Mar 20, 2007
Posts: 317
Welcome.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Kiaamaa Liammes
Ranch Hand

Joined: Oct 03, 2009
Posts: 32

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


SCJP ,SCWCD
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: select * vs select column1,column2 ..