|
|
|
|
|
|
by craig_asp
2481 days ago
|
|
|
Nobody is saying that it's not a good habit to form. It would be better to advise to review your column lists and remove redundant columns. Firstly because schemas change, and then because yes, you might hit an index (especially if you know it's there, as you should) and get that speed-up as well. My issue with this advice is that it enforces the idea that "less columns in select list" = "less data accessed" _in all cases_, which as we all agree is not true. Even more so if you have a relatively well designed database, with no crazy amount of views on views (or any for that matter), UDFs, huge columns with binary data, etc, etc. PS: "...result of UDFs - they will be getting run per value per row..." -> not always.. Inline tvfs get expanded, so you should probably be quite careful with all other user defined functions in SQL Server anyway. |
|
|
I for one try to avoid absolutes like that. "less columns in select list = potentially less data accessed & processed" is a better wording. Along with "and there are no cases, unless there is a QP bug around the matter than I'm unaware of, where it changes things for the worse" if I'm feeling more wordy.
The problem with that though is the some (I've worked with some difficult people!) see the "potentially" as a reason to just drop in the * "for now" - their reasoning being it is a premature optimisation. Why type the extra if it won't definitely improve performance, despite the warnings that while it might not affect performance now it may in future, possibly due to changes elsewhere making this a trickier fix point to find, and even ignoring the performance issue it is good practise for other reasons.
> more so if you have a relatively well designed database
Oh, how I long for such nirvana!
> Inline tvfs get expanded
But scalar functions do not, well not until you are using 2019 which is due for release soon, and even then only some can be. And even if your SVF doesn't result in any reads, so inlining in this sense doesn't matter, there is extra CPU work going on.
(assuming SQL Server of course, details will of course differ elsewhere)