| Author |
Topic |
|
kawadeprasad
Starting Member
13 Posts |
 Posted - 2012-05-10 : 07:19:54
|
| Why SQL query executes faster when column names are mentioned in select block and executes slower when * mentioned in Select block.Thanks in advance!!! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-05-10 : 07:54:44
|
| because the optimiser needs to work out all the fields in the table when using * and doesn't when not. The other over-riding factor is later development. If you use select * and then later add a field to the table, any front end apps or even procedures etc using select * have the possibility of failing. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-10 : 07:55:10
|
| When you list the columns by name, if you are listing all the columns that would be retrieved with SELECT *, there should not be any performance difference. If there is, I have the same question you are asking.If you are listing only a subset of the columns, efficiencies resulting from reduced work in query processing, input/output, network traffic etc. would be the explanation for the better performance. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-10 : 07:57:37
|
quote:
because the optimiser needs to work out all the fields in the table when using * and doesn't when not.
Would/can this cause measurable performance difference? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-05-10 : 07:59:03
|
Not unless your table was stupidly wide..  |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-05-10 : 09:44:10
|
Or if you are doing the vendor slime "SELECT * FROM TABLE1,TABLE2,TABLE3 ....." and only using the FIRST ROW of the FIRST COLUMN in the return.  I have seen this in PRODUCTION code. A simple "SELECT MY_ID FROM TABLE1 WHERE FOO = BAR" would have sufficed, but they simply copied code from another select, needlessly cross joining to 2 other tables, and returned ALL of the rows across the network.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-05-10 : 18:35:53
|
| Internally ,If you use * in select query in that case sql engine perform the conversion from * to all your table columns that slowdown the process. If you directly mention the all column name in select query the sql engine does need to do conversion because it has all columns that needs to be retrieved . |
|
|
|
kawadeprasad
Starting Member
13 Posts |
Posted - 2012-05-11 : 01:36:03
|
| Thanks for the info.Just to confirm. If I mention all column names in Select clause and in other query I mentioned * then which query will execute faster.By the comments I have received it will be either 1 st query or execution time will be same.Please confirm.Thanks to all the people who replied. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-05-11 : 08:31:52
|
| Best practice dictates that you list all columns EXCEPT when doing an EXISTS clause. I am sure someone can chime in when you MIGHT use SELECT * in some other case. If you want to know which is faster, run your query both ways and see.If speed is identical, you should list all columns so that you are complying with best practice. How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2012-05-11 : 09:46:39
|
Note that the issue isn't using * in a SELECT or not, it is what you are selecting from. It is no problem to use * when selecting from a CTE or derived table where the columns are definied within your code:select x.*, x.a + x.b as totalfrom( select .... ) x ... assuming that you want all columns returned. That scenario is separate from doing a "SELECT *" from a table or view where the columns are not explicitly defined within your current SQL.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-05-11 : 11:26:33
|
quote:
Originally posted by DonAtWork
Best practice dictates that you list all columns EXCEPT when doing an EXISTS clause. I am sure someone can chime in when you MIGHT use SELECT * in some other case. If you want to know which is faster, run your query both ways and see.If speed is identical, you should list all columns so that you are complying with best practice. How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp
I don't even use * in this situation, I just do a SELECT 1 FROM ... |
|
|
|
|
Query Optimization