June 30, 2017 at 9:17 am
I'd like to have a more light-hearted post this Friday--maybe a bonding experience. At my position now, I'm taking the lead on setting programming guidelines, which I feel is a difficult task considering the nature of our work. I've managed to convince my team that anything written shouldn't be "you must code this way", but instead "you want to accomplish X, and here's an example of how you can code it". That way, when we get new systems under our responsibility, the technology updates, or we learn more, we don't have to revise our policies. We simply take another approach to accomplish our goal.
But I've also worked in some jobs where coding policy was strictly enforced, even if it got in the way. I'd like to have a fun sharing of coding policies we had to follow that made our jobs more difficult. I'll start with some of the most difficult.
A SELECT statement can not have more than two tables joined together--Use a subquery or modify a temp table
I think the goal was to report routines more readable, but as our database became more normalized, every report ended up being a stored procedure and the server frequently hung due to tempdb hanging.
A routine cannot have more than 200 lines of code (including comments)
This was more understandable--make SPs have a specific singular purpose, and this was written by a data warehouse architect who used a staging database; but when it was applied to our operational database it had the side effect of using global temp tables and usp_<blah>_part2/part3/part4. Especially when we added more operational projects and had to run complex multi-step calculations.
Any calculation that occurs more than once must be put into a function
This was very well-intentioned from a development standpoint, as we frequently calculated values based on rates that were stored in a config table. I wrote an SP that needed to update over a million rows based on a daily rate. At the start, I queried the config table once to get the daily rate and put it in a variable, then ran an update statement with:SET NewValue = OldValue * @DailyRate
But I had to change it to:SET NewValue = fn_AdjustValue(OldValue, 'DailyRate')
where the function would query the config table for the correct rate, and then calculate the value. So many of our jobs had this, and we frequently locked our tables as we queried a single config table over a million times per update. On the bright side, we had only had to change one function should we need a different method of getting the daily rate.
June 30, 2017 at 4:02 pm
Wow. While I understand the paranoid basis of those rules, they're just silly even as suggestions. I feel for ya. The one about using functions is probably the worst of them all but it's also the easiest to prove that it's not only silly, but downright stupid, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2017 at 9:02 am
This is silly. It may have good intentions, but what if you have a well-structured design and need data from 4 tables? You'd have to query the first 2 and store the result in a temp table. Then query the temp table and table 3 and put that into another temp table. Finally, query the newest temp table and table 4 and put that into a final temp table. Last, do whatever it is you want to do with the final temp table. I'd consider this to be a silly approach. Yes, it could be an efficient approach, but not always. It depends on what you're doing, but mandating only 2 tables max is nuts.
Heh - I have stored procedures with a lot more lines than that. Some of them have more than 200 lines of comments. 😛
I can see this leading to a complex procedure calling a stored procedure, which then calls another stored procedure, and so on. This would lead to global temp tables (see the first rule) and unnecessarily-complicated logic spread over multiple procedures to perform a single task. This is, in my opinion, intentionally creating technical debt.
gdpollock - Friday, June 30, 2017 9:17 AMAny calculation that occurs more than once must be put into a function
This was very well-intentioned from a development standpoint, as we frequently calculated values based on rates that were stored in a config table. I wrote an SP that needed to update over a million rows based on a daily rate. At the start, I queried the config table once to get the daily rate and put it in a variable, then ran an update statement with:SET NewValue = OldValue * @DailyRate
But I had to change it to:SET NewValue = fn_AdjustValue(OldValue, 'DailyRate')
where the function would query the config table for the correct rate, and then calculate the value. So many of our jobs had this, and we frequently locked our tables as we queried a single config table over a million times per update. On the bright side, we had only had to change one function should we need a different method of getting the daily rate.
This one's just flat-out dangerous. Requiring a function for something simple is a great way to kill performance and virtually guarantee that the code won't perform well.
I like the way you're approaching writing coding standards. There are some absolutes in SQL, but the thing about them is that as soon as you define one, there's an exception to it. It certainly isn't an easy task and I hope management doesn't expect it to be done in a week.
Looking at the rules you posted about a previous job, I do have one absolute: I'm glad I don't work there or have to deal with rules like those. 😉
July 1, 2017 at 1:33 pm
I for one enjoy style guides that address common optimization areas that people forget are overlook due to lazyness. I have not had the opportunity to create one for SQL, but I'm thinking about it for my team.
If you need some reference, here is a good one that I enforce on my team when it comes to Python development. It's been great for us and I really love the results of how it impacted simply the readability of our code across the team.
Here is the global one they have that includes Bash, R, C++ and others.s
Every major open-source project has its own style guide: a set of conventions (sometimes arbitrary) about how to write code for that project. It is much easier to understand a large codebase when all the code in it is in a consistent style.
July 7, 2017 at 8:58 am
Thanks for all your replies everyone. I was hoping to see other experiences too--I can't be the only one who worked at companies like this. I'm happy to say my current job is more goal-focused. I do feel that it's very difficult to write policies for coding and we should stick to more guidelines.
One step I've been using is to put a "goal" on any policies, and that the text of the policy should be second to the goal. This goal could be to make the code more readable, or make the task run faster, or something we can all agree is the priority for the process. That way if during development for a specific project, it's found that following the policy actually contradicts the goal, we can decide to make an exception. An example would be if your ETL process uses a template SSIS package, but one of your dimensions is so out-there, you need multiple layers of ETL just to get the job done and have to use a custom SSIS job. Do any of you have a wide range of projects you work on, or are you more in cookie-cutter environments?
July 7, 2017 at 11:12 am
What's interesting is how these misguided policies actually encourage bad SQL programming practices.
"A SELECT statement can not have more than two tables joined together..."
Hey, boss-man, when you say tables, that applies to views too, right? OK, just asking.Thanks.
"A routine cannot have more than 200 lines of code (including comments)"
No problemo; I don't comment my code anyhow, but just let me just run it though RedGate Format SQL and get rid of those pesky line breaks.
"Any calculation that occurs more than once must be put into a function."
Again, no problemo, we do ALL our calculations on the front end.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 7, 2017 at 12:00 pm
Heh. Doing all the calculations on the front end reminds me of how many applications I have encountered where the quote was "We maintain referential integrity in the application."
In writing coding standards, I have been trying to add not only a what, but a why as well. Optimally with an example of how it can screw up someone's day. For my money knowing the why of a coding standard is better than simply knowing the what.
July 8, 2017 at 5:12 pm
crow1969 - Friday, July 7, 2017 12:00 PMHeh. Doing all the calculations on the front end reminds me of how many applications I have encountered where the quote was "We maintain referential integrity in the application."In writing coding standards, I have been trying to add not only a what, but a why as well. Optimally with an example of how it can screw up someone's day. For my money knowing the why of a coding standard is better than simply knowing the what.
Absolutely agreed with that. So far as I'm concerned, without the "Why", there is no "What". I refuse to support a "5 Monkeys" standard or a Draconian Edict especially if the edict is more of a personal preference than anything substantial. I also find that knowing the "Why" prevents furthering old wives tales standards and supposed "Best Practices" that have been spawned because of them or due to the "I wanna be cool too" bandwagon effect where someone with a high profile says something and initially gets a good response by equally well meaning but ignorant folks and it goes viral from there.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply