January 3, 2024 at 12:00 am
Comments posted to this topic are about the item Fixing Poor Programming Practices
January 3, 2024 at 12:19 pm
I was shocked to find a DB deployment pipeline that was written using the Talend ETL tool. The tool attempted to be a SQL parser dictating what SQL could and could not be supported.
That eliminated quite a lot of the really useful SQL constructs that have been appeared since ANSI SQL-92. It also reformatted SQL files so they existed on a single line. The WHERE clause in the code below would join the comment on Line 2 so all tax_rate values would become 0.175
UPDATE finance.sales_transactions
SET tax_rate = 0.175 -- This is a legally mandated 17.5% value
WHERE sales category = '.....etc'
To add insult to injury the pipeline was excessively slow to operate and would often fail if too many other ETL jobs started.
I dread to think how much time was spent developing that Talend based deployment pipeline or who in a position of power thought it was in any way sensible.
I replaced this horror with a simple migration based approach somewhat similar to FlyWay and running the DB command line client. Most DB deployments (and the rare rollbacks) from that point on became "blink and you miss it" fast and utterly reliable.
One of the worse development practises is having a deployment pipeline and allowing certain people to bypass it for poorly articulated reasons.
Sometimes you need a "break glass" approach but this is rather like the SA login. There are extremely rare cases where you need it but they are ever so extremely rare. What happens is that the bypassing of the pipeline starts to spread. Things are forcibly deployed that cause breakages elsewhere and the breakages blamed on the pipeline, not the people who bypassed it.
The fix is as follows
If there are difficulties getting buy-in for the above, liaise with any regulatory/compliance personnel you may have. The technical solution is likely the easy bit. The regulatory/compliance folk can be a great help with the human solution.
January 3, 2024 at 5:46 pm
I could probably cause WordPress to blow up with all of the poor practices I've had to fix.
Here is the issue of the day:
We have a large Synapse instance, it's about 10TB. There is a new schema, which contains the scrubbed and cleaned set of "gold" data. No dupes, etc. The ETL pipelines created, if executed, will create duplicate data in the final tables. You cannot execute these pipelines twice. They were executed once in dev, once in QA, and deployed to production. And we have duplicate data because they run every day!
And possibly the worst of all time:
Small shop, 5 developers, one QA person, one DBA, one "analysist". The design decision was that if a dev was working on a module, they didn't want to cause issues with another dev working on the same, or different module that interacted with the same database table. So, developer A would create a trigger on the table(s) that performed whatever operation on the affected tables. Developer B had to interact with the same tables, so he created a trigger. And on and on.
The transaction table, with millions of rows of about 500 GB in size, had 43 triggers. These in turn updated or inserted into 26 other tables, and 22 times these triggers updated or inserted into the transaction table. All with NOLOCK.
When I asked what was the factors that made you do things this way, the answer was that we didn't want to worry about what anyone else was doing. You sit within 10 feet of each other, and work from the same ticketing system, don't you talk to each other?
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/
January 3, 2024 at 6:00 pm
Very interesting topic, Steve. I've been in this field for a long time, so I have encountered this issue more than once. I wasn't prepared for it, so I basically winged it, doing the best I could. I look forward to reading what others say.
I'll relate two stores, one with a positive outcome and the other not a positive outcome. I'll do the positive one, first.
Many years ago, at a different company, I was working on a small team of developers writing an application to be used by fellow employees. We were working with the old Visual Basic (not VB.NET). I think it was VB 4, but it might have been VB 3. Back then VB was primitive. It didn't have basic functions like string replace, so we had to write our own. One of my colleagues, a junior developer, tried to write a string replace. Her approach was weird. She processed the original string, looking for the character or characters that needed to be replaced; when her routine found a matching string, she replaced it, then started at the beginning of the string again. This worked fine, if the sought for string was something like "2" and the replacement string was "3", but it failed horribly if the sought for string was "2" and the replacement string was "24". It would cause a stack overflow, crashing the app. I asked her if I could work on it. She granted me permission to do so. I fixed it by doing the replacement but picking up where I'd left off in the string, to continue to look for the sought for string. So, this was a win, we worked together well. She did most of the work, I just had to do a couple tweaks to fix it.
The second example is in my current job with one of my coworkers who is still working here. About 5 years ago we were starting on replacing Excel and MS Access apps with WPF apps. My coworker used what's known as the KeyValuePair struct in the code. He uses it quite often. If you look up the Microsoft documentation, you'll see that the firsts argument is the key, and the second argument is the value the key points to. My colleague has a unique way of writing his code, which results in him putting the value in the first argument and the key into the second argument. Once, when he was sick, I had to work on the code which was failing. Because I consulted the Microsoft documentation, I spent hours thinking it was the way it should be, until I realized that my coworker ignored how it should be to do it the way he wanted to do it. I made the necessary correction and left him an email describing what I'd done and why. When he returned he was extremely angry at me. He spent the whole night rewriting everything I had done. I said that it wasn't consistent with the documentation. He said, "I DON'T CARE!!!". I brought this to our boss and HR person. Both defended my coworker to do what he wanted to do, whenever he wants to do it, to anyone he wants to do it to. This guy has carte blanche to behave badly to everyone and defended by all authority. So, consequently I try to never work with him on anything. I suspect the rest of my colleagues do the same.
Kindest Regards, Rod Connect with me on LinkedIn.
January 3, 2024 at 6:06 pm
Once a few years ago, at a prior job, I went on a sales engagement with one of our sales people. At one point I got called into a meeting in a conference room with a bunch of people and where the sales person introduced me as a "SQL expert". On the projector screen they were looking at a bunch of the (potential) client's database code which was supposedly SQL Server. They had a whole system built by one person who then left for another company. They were paging down the code on the screen and looking at me to make a comment. Usually I recognize SQL code and could come up with something to say. In this case tho I didn't recognize what was going on at all. I said something like "there's a lot of code I'll have to look at it in detail..." Later, I got a chance to look at the code and see what was going on. This person had (patiently hard) coded complete HTML documents contained in custom error messages in t-SQL. It was all concatenated together which resulted in pages and pages of HTML symbols mixed with quote escaped text, + signs, and line breaks. Thankfully we didn't end up going forward with the project
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 3, 2024 at 6:24 pm
Very interesting topic, Steve. I've been in this field for a long time, so I have encountered this issue more than once. I wasn't prepared for it, so I basically winged it, doing the best I could. I look forward to reading what others say.
I'll relate two stores, one with a positive outcome and the other not a positive outcome. I'll do the positive one, first.
...
These seem like training issues, which are probably cause most of the common programming issues that need to be fixed.
January 4, 2024 at 8:32 am
Very interesting topic Steve. What I have observed very common is the data type mismatch in LINQ queries which causes the queries to do Table scan instead of seek even when there are proper indexes. People are very much interested in using GUID as primary key column which I still don't understand whether it's a good practice
January 4, 2024 at 10:33 am
Rod, I've found that solutions to human behaviour, such as finding reasons to want to bypass a CI/CD pipeline, are not permanent.
The danger with any process is that it is susceptible to being unscrupulously portrayed as a blocker rather than as an enabler. Decisions are not always made with full possession of the facts.
For me this underlines why it is so important to address problems with a CI/CD pipeline (and opportunities to improve it) as a matter of priority. Its credentials as an enabler needs to be unimpeachable to guard against the silver tongued cowboy coders of the IT world.
January 4, 2024 at 4:06 pm
Rod, I've found that solutions to human behaviour, such as finding reasons to want to bypass a CI/CD pipeline, are not permanent.
The danger with any process is that it is susceptible to being unscrupulously portrayed as a blocker rather than as an enabler. Decisions are not always made with full possession of the facts.
For me this underlines why it is so important to address problems with a CI/CD pipeline (and opportunities to improve it) as a matter of priority. Its credentials as an enabler needs to be unimpeachable to guard against the silver tongued cowboy coders of the IT world.
I like what you've said here, David. Yes, human behavior is at the root of many, maybe all, the issues I've faced over the years, at the various places I've worked.
You've touched upon an important topic to me and that's developing a CI/CD pipeline. Over the last 4 years as I've learned more about CI/CD in Azure DevOps Services and GitHub Actions I've really come to want CI/CD pipelines to make builds and deployments consistent and reliable. The old TFS we have here had its own brand of CI/CD, although some of that has become obsolete (i.e.: XAML builds). I'm involved in a major project to migrate us to GitHub, with its GitHub (GH) Actions. It has lots of promise. But it will be the attitudes from people here which has the potential of killing GH Actions usage. We have hundreds of projects in TFS. (I don't know how many, because TFS allows you to put multiple projects into one TFS Project and we've got over 100 TFS projects. I've discovered several TFS Projects with lots of unrelated Visual Studio projects within them.) Anyway, most of my coworkers favor building and deploying straight out of Visual Studio, as has been done since the 1990s. They are very resistant to GH Actions. All it takes is one failed GH build for them to claim that "GitHub Actions aren't ready for production usage and may never be. So, I'll continue to build and deploy from VS 2013 indefinitely." Totally disregarding the fact that any process isn't likely to work the first time. They need testing and refinement, before it will work consistently. But my colleagues would rather look for the first excuse they can find, to claim changing to a new process is never going to work.
I will say that I have seen encouraging signs. Recently, while working on migrating a project to GH I discovered that someone had already created a GH Action there and it was working!! Honestly, this is AWESOME!!!!!!! They did this all by themselves, without any prompting from me. WOOHOO!! The downside to this discovery is that in all cases the people making these innovative changes are younger and contractors. Thus, if money gets tight, they'll be either furloughed or let go. Meanwhile the regular employees aren't interested in adopting anything current, let alone new.
Kindest Regards, Rod Connect with me on LinkedIn.
January 4, 2024 at 4:58 pm
Very interesting topic Steve. What I have observed very common is the data type mismatch in LINQ queries which causes the queries to do Table scan instead of seek even when there are proper indexes. People are very much interested in using GUID as primary key column which I still don't understand whether it's a good practice
That's not a LINQ issue. It's an ORM or framework that always passes in nvarchar by default, or another mismatched datatype. It's certainly a programmer/programming issue though.
GUIDs as a PK are nice when you might need to generate them on multiple machines, or you generate them on the client for the db.
January 4, 2024 at 6:06 pm
One thing, Rod, to think about is complaints or concerns about tech not being ready could be true. They could also be fear that if we stop using VS2013, I'll be replaced because I don't know GH Actions.
Some people don't want to learn, some are afraid of learning.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply