Timeline for Query Performance difference select * or specific columns
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 24, 2024 at 6:35 | vote | accept | Syeda Maira Saad | ||
| Dec 10, 2023 at 23:57 | answer | added | Rick James | timeline score: 0 | |
| Dec 10, 2023 at 14:11 | answer | added | Paul W | timeline score: 1 | |
| Dec 10, 2023 at 13:43 | answer | added | ibre5041 | timeline score: 1 | |
| Dec 10, 2023 at 13:07 | history | edited | William Robertson |
edited tags
|
|
| Dec 10, 2023 at 12:56 | comment | added | William Robertson |
* will be expanded internally at parse time to the full list of visible columns for that table or view. Perhaps the interviewer was thinking of the cost of doing the dictionary lookup to find all the columns, but that will be negligible and the parser has to do the same checks either way. So short answer is no difference.
|
|
| Dec 10, 2023 at 6:05 | comment | added | Syeda Maira Saad | it contain all 100 coulmns | |
| Dec 10, 2023 at 4:42 | comment | added | Jon Heller |
@SyedaMairaSaad It might help us understand the problem if you can fill in the ... a bit. How does it end? Do you mean the second query lists all 100 columns, col1,col2,col3, ..., col100? Or does it only list some subset of the columns?
|
|
| Dec 9, 2023 at 19:23 | comment | added | jarlh | I'd expect same execution plan. | |
| Dec 9, 2023 at 15:30 | comment | added | ValNik |
My be select a.col1.a.col2,a.col3........ from Application_user a query have "permanent" stored query plan, when as in the other case select * from query plan generated always new.
|
|
| Dec 9, 2023 at 15:22 | comment | added | Syeda Maira Saad | It row store database (MYSQL or oracle); interviewer said select * from table_name has low performance, but I don't understand how | |
| Dec 9, 2023 at 15:13 | comment | added | MatBailie | If it's a column store database, reading fewer columns results in reading fewer pages from the disk. If you have a single index covering the chosen columns, it results in reading fewer pages from disk. Usually (in row store databases) there is no difference. But, as a whole, you're better of learning about join methods (hash, nested loop, merge, etc), indexes, and execution plans. | |
| Dec 9, 2023 at 15:13 | comment | added | Syeda Maira Saad | Yes but this was my interview question, performance difference | |
| Dec 9, 2023 at 15:10 | comment | added | Dai |
Performance isn't the main reason you should avoid using SELECT * in a query...
|
|
| S Dec 9, 2023 at 15:07 | review | First questions | |||
| Dec 10, 2023 at 14:16 | |||||
| S Dec 9, 2023 at 15:07 | history | asked | Syeda Maira Saad | CC BY-SA 4.0 | created from wizard |