June 17, 2015 at 4:55 am
vliet (6/17/2015)
As much as I do agree with all the arguments against using SELECT * I still think that your developer does not have to change this bad habit until it becomes a business case. If and only if BOTH performance does suffer AND your management does not want to double the processing power, then you might have a strong point against the current approach. If it ain't broke, don't fix it. I know that will leave you with a very unpleasant feeling about this whole case, but such is life. Mention it to the right people, and as soon as the problems you predicted will surface, you'll only have to say: I told you so. Remember, if these problems do surface, you'll probably have to work together with this developer to fix them, whether you like it or not. Sometimes all you can do about it is making clear to anyone you are not the one to blame.
The developer doesn't have to change, but should. It's always easier to make performance improvements or other changes when you're not in the middle of a crisis.
June 17, 2015 at 9:15 am
vliet (6/17/2015)
As much as I do agree with all the arguments against using SELECT * I still think that your developer does not have to change this bad habit until it becomes a business case. If and only if BOTH performance does suffer AND your management does not want to double the processing power, then you might have a strong point against the current approach. If it ain't broke, don't fix it. I know that will leave you with a very unpleasant feeling about this whole case, but such is life. Mention it to the right people, and as soon as the problems you predicted will surface, you'll only have to say: I told you so. Remember, if these problems do surface, you'll probably have to work together with this developer to fix them, whether you like it or not. Sometimes all you can do about it is making clear to anyone you are not the one to blame.
The problem is that there are "code smells" (developers can relate to that term because they coined the phrase) where it's known that something will eventually "give" and, as Ed Wagner alluded to above, it will always happen when you can least afford it.
As another frequent poster on these fine forums has as a tag line in his signature... "I'm a DBA. It's not my job to solve problems. It's my job to PREVENT them". Developers really need to get onboard with that philosophy.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2015 at 9:42 am
patrickmcginnis59 10839 (6/16/2015)
ZZartin (6/5/2015)
EdVassie (6/5/2015)
You could try a little role-play.Call your developers into a meeting, and have a box of paper left outside the room.
Tell a developer you need some paper and ask them to bring the box in. Take a sheet and ask them to put the box outside again.
Repeat the process with someone else, and say they need to bring in the whole box because that is like doing a SELECT *.
Then ask a third person, and say this time they only need to bring in one sheet because they are doing a SELECT col1.
Seeing some of the impact may help prove your point.
I still stand by just changing the column order on them and watching them cry while trying to trouble shoot why all their SELECT * queries break.
Then guess who gets the blame for breaking the app?
Besides, if the app uses column names, the app might continue to function. I just tried it here with vb, changed the table to have one more column, and the selected column is now in position 4 instead of position 3 (ordinal position) and it was handled correctly.
Bonus points if you can convince management there's a need to reorganize the columns and it shouldn't have any impact on the developers anyways since they're following best practices right?
Well I wouldn't do that unless there were REALLY a reason to rearrange columns. Otherwise, I'd be lying and heaven forbid, I couldn't produce a believeable reason for rearranging columns and the developers would also now be all too willing to blame the breakage on me.
I've been in a similar position and I gotta tell you, if the apps working ok, and nobody appreciates the correctness of what I'm saying, I'd drop the issue. Essentially, I bring issues like this up as nicely as I can WITHOUT actively trying to break apps, and if I don't make a sale, then thats the end of that.
Whether it breaks the app is entirely dependent on the app and how it's coded, and you wouldn't do this in a production environment that's what dev environments are for, also it should be a change you can flip back very quickly 🙂
Sometimes the only way people will accept they're wrong is if you prove them wrong. For example a perfectly plausible scenario is that columns are added in a certain order in one environment then added in a different order when migrated to another environment, while that's a problem it's really a problem if the developers are relying on column order and now it's not caught until it's in production a change which won't break if SELECT * is not being used.
Just for a quick fun comparison on performance against a random table with ~194000 rows and 450 columns,
SELECT *
SQL Server Execution Times:
CPU time = 6521 ms, elapsed time = 60020 ms.
SELECT <PKEY> and some random columns
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 1512 ms.
June 17, 2015 at 9:59 am
Jeff Moden (6/17/2015)
As another frequent poster on these fine forums has as a tag line in his signature... "I'm a DBA. It's not my job to solve problems. It's my job to PREVENT them". Developers really need to get onboard with that philosophy.
Ignoring things because they aren't a problem yet actually has an ok car analogy.
You don't rotate the tires - you haven't had an accident yet!
You don't change the oil - it could start more smoothly, but hey, it works!
You don't clean the windshield - you have to squint a little, but you've had no accidents yet!
Everything's fine until either it doesn't start, or you crash and the car's not worth repairing... and you can't do business anymore.
June 17, 2015 at 10:58 am
ZZartin (6/17/2015)Sometimes the only way people will accept they're wrong is if you prove them wrong. For example a perfectly plausible scenario is that columns are added in a certain order in one environment then added in a different order when migrated to another environment, while that's a problem it's really a problem if the developers are relying on column order and now it's not caught until it's in production a change which won't break if SELECT * is not being used.
I don't doubt the "select *" having disadvantages, but I wouldn't make the change behind the dev's back or anything like that. In all likelyhood, I'm betting that their use of "select *" is for screen use where the response time is ok. It would be worth mentioning to them that some situations react poorly to using "select *", but it sounds like the original poster already went this route. Maybe write his concerns to management in an email to at least document his concerns would be a good move.
I wouldn't arbitrarily try to make it fail behind the devs back though.
June 17, 2015 at 2:54 pm
patrickmcginnis59 10839 (6/17/2015)
ZZartin (6/17/2015)Sometimes the only way people will accept they're wrong is if you prove them wrong. For example a perfectly plausible scenario is that columns are added in a certain order in one environment then added in a different order when migrated to another environment, while that's a problem it's really a problem if the developers are relying on column order and now it's not caught until it's in production a change which won't break if SELECT * is not being used.
I don't doubt the "select *" having disadvantages, but I wouldn't make the change behind the dev's back or anything like that. In all likelyhood, I'm betting that their use of "select *" is for screen use where the response time is ok. It would be worth mentioning to them that some situations react poorly to using "select *", but it sounds like the original poster already went this route. Maybe write his concerns to management in an email to at least document his concerns would be a good move.
I wouldn't arbitrarily try to make it fail behind the devs back though.
I strongly agree with that. There's a deep enough gap between some Dev teams and DBAs without starting these kinds of games. There's no need to go behind anyone's back to do anything. Everyone should be promoting communication... not sneaking around behind it. It just makes the existing problem much worse. If one can't get concurrence, then they haven't presented the problem correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2015 at 2:07 am
When the developer said that using SELECT * means all the data is there for him, ready to use, did you ask him if he also declares 100 integer variables a1...a100 at the top of every procedure he writes, just in case he needs to use one of them? 🙂
June 18, 2015 at 2:21 am
Teach him to just drag the columns from Object Explorer (or get SQL Prompt). Easy fixes for the dev that aren't a big deal.
June 18, 2015 at 5:44 am
Steve Jones - SSC Editor (6/18/2015)
Teach him to just drag the columns from Object Explorer (or get SQL Prompt). Easy fixes for the dev that aren't a big deal.
The key word in that sentence is "teach" and it can solve the problem, but they have to be willing to learn.
June 18, 2015 at 9:50 am
Does this app/database have any VARBINARY(MAX) columns?
Your demonstration that SELECT * is a resource hog will be made as forcefully as a.... high velocity pork chop. 🙂
Rich
June 18, 2015 at 10:48 am
Jeff Moden (6/17/2015)
patrickmcginnis59 10839 (6/17/2015)
ZZartin (6/17/2015)Sometimes the only way people will accept they're wrong is if you prove them wrong. For example a perfectly plausible scenario is that columns are added in a certain order in one environment then added in a different order when migrated to another environment, while that's a problem it's really a problem if the developers are relying on column order and now it's not caught until it's in production a change which won't break if SELECT * is not being used.
I don't doubt the "select *" having disadvantages, but I wouldn't make the change behind the dev's back or anything like that. In all likelyhood, I'm betting that their use of "select *" is for screen use where the response time is ok. It would be worth mentioning to them that some situations react poorly to using "select *", but it sounds like the original poster already went this route. Maybe write his concerns to management in an email to at least document his concerns would be a good move.
I wouldn't arbitrarily try to make it fail behind the devs back though.
I strongly agree with that. There's a deep enough gap between some Dev teams and DBAs without starting these kinds of games. There's no need to go behind anyone's back to do anything. Everyone should be promoting communication... not sneaking around behind it. It just makes the existing problem much worse. If one can't get concurrence, then they haven't presented the problem correctly.
But what about when it's not about a DBA vs Dev argument, it's a Dev vs Dev argument one of whom has a stronger DB background and one who doesn't? I certainly wouldn't go to a web developer and start dictating how to code websites but I'm also not going to listen to a dev without a strong DB background try to defend bad coding standards just because he's lazy, if that means proving a point in a dev environment after talking has failed then so be it.
Also I'd be willing to bet that if the original poster dug around a little he'd find issues that using select * has already caused, whether that be something breaking unexpectedly, bad performance or just other developers having a hard time understanding the code.
June 18, 2015 at 9:44 pm
ZZartin (6/18/2015)
I'm also not going to listen to a dev without a strong DB background try to defend bad coding standards just because he's lazy.
That's precisely what I was talking about earlier. Prove it to him with code in a mentor-like but still peer-minded manner. If he still doesn't go for it, either take it up to the next level of authority or let him fail. Remember that "developers" like this are poison for the rest of the team and need to be dealt with but, if you can't prove it with code, there is no argument. Of course, if there is no management support on any of this, its nearly a total lost cause (I've left two companies just for such a reason). And, yes, I know I'm just preaching to the choir here because you already know according to what you said also said, quoted below...
Also I'd be willing to bet that if the original poster dug around a little he'd find issues that using select * has already caused, whether that be something breaking unexpectedly, bad performance or just other developers having a hard time understanding the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2015 at 8:29 am
Jeff Moden (6/18/2015)
...if you can't prove it with code, there is no argument.
...
I believe that part of proving it with code is having valid, reliable measurements. Another part is measuring things they care about, or that are related to what they care about.
What's important to the dev member?
What's important to the dev team lead?
What's important to the dev team as a whole?
What's important to management?
"Performance" can be translated as "Less expensive and less frequent required hardware upgrades" or "Cheaper Cloud bills due to higher efficiency - doing more with less" or "Not causing the entire business to come to a screeching halt when too many slow operations happen at the same time, such as as the end of the quarter/year or during an audit".
June 19, 2015 at 8:46 am
Nadrek (6/19/2015)
Jeff Moden (6/18/2015)
...if you can't prove it with code, there is no argument.
...
I believe that part of proving it with code is having valid, reliable measurements. Another part is measuring things they care about, or that are related to what they care about.
What's important to the dev member?
What's important to the dev team lead?
What's important to the dev team as a whole?
What's important to management?
"Performance" can be translated as "Less expensive and less frequent required hardware upgrades" or "Cheaper Cloud bills due to higher efficiency - doing more with less" or "Not causing the entire business to come to a screeching halt when too many slow operations happen at the same time, such as as the end of the quarter/year or during an audit".
Almost Exactly. +1000
The only exception that I would have to take here is that management needs to get on board with the things you've listed in your final paragraph whether it's currently important to them or not. So do the Devs. I say "So do the Devs" because they ARE being paid not to "just" do a job, but to do the job well. And, except for getting their attention, I'm not sure I care what's important to the Devs or the Dev Team. If it's not right, it's not right whether "right" seems to be a minority or not. The old saw about "Never mistake the concurrence of the crowd for the wisdom of the group" comes to mind on things like this.
The unfortunate part is that it's sometimes very difficult to get the crowd to see the importance of doing it "right" and what "right" means. If it's overwhelmingly impossible to do so because of the entrenchment of ignorance or arrogance on the part of the leaders, it might be time to move on.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2015 at 9:01 am
The unfortunate part is that it's sometimes very difficult to get the crowd to see the importance of doing it "right" and what "right" means. If it's overwhelmingly impossible to do so because of the entrenchment of ignorance or arrogance on the part of the leaders, it might be time to move on.
When do you make this call? What factors do you use to say it's time to give up?
When you stop trying to prevent problems, and are simply running around behind the devs fixing issues.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply