November 10, 2015 at 9:58 am
Steve Jones - SSC Editor (11/10/2015)
Here's a good look at some reasons why you might want to change code. It's long, but it's really interesting.
Interesting that it's called "2 Minutes to Better Code," but it lasts an hour and 54 minutes.
Don Simpson
November 10, 2015 at 10:28 am
Saw quite a bit of scary code in the late '90's and early 2000's. Code that was written by people who jumped on the tech bandwagon. One developer formerly was a photocopy repairman. But the best was when the guy fixing my car A/C was telling me he was getting out of the business to become a Lotus Notes developer.
All the more reason to use continuous integration with strong testing.
The more you are prepared, the less you need it.
November 10, 2015 at 10:30 am
Steve Jones - SSC Editor (11/10/2015)
GilaMonster (11/10/2015)
I've recently seen a system with many user-defined functions, each with thousands of lines of code, often calling each other. Tests or no tests, I'm not touching that.Write a test. You might be surprised how much it helps.
It doesn't need a couple of tests unfortunately. It needs tearing down and re-architecting from scratch. Probably starting with lots of unit tests, but the only thing that's going to fix the mess they've got themselves into is a rewrite (and probably not in T-SQL)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2015 at 11:36 am
DonlSimpson (11/10/2015)
Steve Jones - SSC Editor (11/10/2015)
Here's a good look at some reasons why you might want to change code. It's long, but it's really interesting.Interesting that it's called "2 Minutes to Better Code," but it lasts an hour and 54 minutes.
It iterates :w00t:
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 10, 2015 at 12:02 pm
Even with a declarative set-based language like SQL, some developers (well, more like a lot of them) still manage to produce big piles of incomprehensible spaghetti code. Fortunately, however, it's a lot easier to refactor or optimize a database one procedure, view, or select statement at a time without breaking the overall cohesion.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 10, 2015 at 12:23 pm
I've done a fair bit of software archaeology and what I have found is that quick fixes are built upon quick fixes. This isn't to imply that this is layers like an onion. Such systems tunnel and spread seemingly at random. The word metastasise comes to mind.
The time required to work out how it works and what the implications are is entirely unpredictable. Chances are you will break something important as a side effect.
Where I have had the chance to work it through I've often found the systems to be doing several simple things in overcomplicated ways. What should have happened is that the systems should have been broken down into loosely coupled strongly cohesive parts.
In other cases processes have been put in place to pander to the whim of someone with a big mouth and enough political clout to get away with a really bad idea. For example a report that excludes certain web analytic events because "these can't possibly come from real customers". A more honest interpretation is " those figures mean I haven't delivered on my bonussed target and damage the business case for my next pet project ".
Once you get into that territory then correcting something becomes undesirable to the business users because consistency is valued more than accuracy. Any code rewrite faces the challenge of writing good code but reproducing the effect of a fudge
November 10, 2015 at 1:12 pm
Just started a new role and taken over from a keen developer whi loved his SQL. I see functions which call functions which are based on views which call functions to transform data from the application tables and may be referencing data in tables created by functions referenced elsewhere.
If that is not bad enough the base structure is an accounting database which relies on accounting periods and is recorded as such; thus accounting period 1 in 2015 is actually April but the functions and views use intermediate tables, created for the purpose by the developer, which change the accounting period, to actual date; each function using part or whole of intermediary data, or both in one case; the penultimate contains only real dates not accounting.
If this is not bad enough some functions contain hard coded financial (cost) data used to create statements; I hve not found yet if these values are actually stored in the dataase or if\how they are passed to the accounting tables.
Sometimes you have to think "Best left alone" until someone says "can you change the charge for xyz and add $1.28".
There is no documentation, afterall databases are simple, arent they?
Sometimes best left alone may be best while everyone is working.....Or am I going slightly mad???
...
November 10, 2015 at 1:39 pm
Fear is sometimes good. 🙂 It may not reflect a well-run development process, but sometimes fear of changing code is actually warranted - it can reflect an honest admission of ignorance about the code in question.
And regarding tests, one hopes there is enough information to reconstruct enough of the requirements (even digging through code comments if that's all that's available) to make proper tests. As I once heard from a developer, one must test against requirements or else there is no way to check whether the code really works as intended.
Just my two cents.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 10, 2015 at 1:39 pm
GilaMonster (11/10/2015)
I've recently seen a system with many user-defined functions, each with thousands of lines of code, often calling each other. Tests or no tests, I'm not touching that.
lol +1
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 10, 2015 at 3:13 pm
When I first started my current job about 5 years ago, pretty close to the first week or so I was working I noticed the production SQL server being slowed to a crawl. I tracked it to a query being run by an accountant; so I went over to accounting and asked what they were doing.
Turns out, at the end of every month, in preparation for final close of a month, accounting would run this query directly from Management Studio... they'd simply update the month and year and let it go, and then paste the results into Excel. Why accounting had Management Studio, or direct access to production SQL, were questions for later in the day. I decided to look at the query.
The query itself looked simple... SELECT <some columns> FROM MonthlyClose WHERE CURMONTH = <the month> and CURYEAR = <the year>
But then I looked at the view MonthlyClose. :blink:
It was comprised of a join between two other views - their names don't matter - what matters is that each of those views was comprised of other views; as were the next views, and the next views, and the ones after that. All in all I think I counted 27 total views. I mapped the thing out and it took an entire white board.
And it wasn't just the sheer numbers of views, no... every single one of them was pulling TOP 100 PERCENT and ordering the results. All 27 of them. At one point, whoever wrote this mess made one view that was three other views union'ed together; but then in a higher up view he selected everything from that view and still union'ed the original three views to same result.
At one point there was a CASE that did absolutely nothing except possibly take up cycles. It was literally something along the lines of "CASE WHEN EXISTS (some query) THEN 1 ELSE 1 END" which, honestly, I don't even want to guess what the thought process was.
Some of the views were nothing more than selecting from another (already sorted) view and then sorting the results.
Overall, it would take between 40-45 minutes to run for a single month. I ended up rewriting the whole mess as a stored procedure and making it an SSRS report. Runtime: 2 minutes.
November 10, 2015 at 3:32 pm
GilaMonster (11/10/2015)
I've recently seen a system with many user-defined functions, each with thousands of lines of code, often calling each other. Tests or no tests, I'm not touching that.
I want to laugh at that but... years and years ago when I first got started with SQL, I and a couple of other developers were tasked with building a system to track our sales team and their sales. And our approach was to do everything we could in functions. And so we had a whole lot of functions that did simple things, that would be called by bigger functions, which would be called by even bigger functions, etc.
Looking back, I can actually visualize how much more efficiently (and easily!) we could have built the entire thing using SQL the right way.
November 10, 2015 at 6:46 pm
Eric M Russell (11/10/2015)
A foolish man builds his business on top of a million lines of source code.
Hmmm.... wonder how much code Google or Ebay has.
--Jeff Moden
November 10, 2015 at 6:52 pm
Gary Varga (11/10/2015)
Jeff Moden (11/10/2015)
Gary Varga (11/10/2015)
I joined a project part way though and got ridiculed the week when I added the first unit test project to the solution. It had 1 test.By then end of the project it had hundreds and proved invaluable.
The overhead of adding the first test is too costly for the benefits from just one test but the return on a significant number of unit tests is immense. NOTE: Significant here is totally dependent upon specifics.
Hi Gary,
Not having touched any front-end code for well over a decade, I'm admittedly a bit lost when people say things like "added a unit test project to the solution". What does that project consist of and (just looking for a high level explanation) how does it test front-end code?
This was .NET so some of that is just the lingo for that technology stack.
A .NET solution is a collection of .NET projects. In this case one being a unit test project. This unit test project is a container to hold the definitions of the unit tests and used to group them together in order to execute them in a single run.
Often these unit tests are used to check the database interaction, the application logic and the presentation logic. Sometimes, albeit rarely, the UI is tested like this as well.
Does that answer your question Jeff?
It does, indeed. It sounds like it could become a full time job just keeping such tests up to date almost as if it were an application itself. Do they also serve to expedite QA and UAT (and I can't actually understand how they might be used by either, to be honest) or do they "just" make unit sanity checks easier/more consistent for Developers?
--Jeff Moden
November 11, 2015 at 12:06 am
Andrew..Peterson (11/10/2015)
Saw quite a bit of scary code in the late '90's and early 2000's. Code that was written by people who jumped on the tech bandwagon. One developer formerly was a photocopy repairman. But the best was when the guy fixing my car A/C was telling me he was getting out of the business to become a Lotus Notes developer...
At one place a couple of us freelancers with a while in the industry after computer related degrees of various sorts we assisting a person new to team leading with recruitment. The position was temporary and for a project in mid-flow that was short on team members so needed someone knowledgeable regarding the technology with the experience applying it. We informed the recruitment agencies of this situation and the criteria.
The comedy benchmark was "The Tiler". We received a CV for someone who eight months previously was a kitchen and bathroom tiler and had no relevant education nor training. He might have been suitable for a junior position and may have even advanced quickly in such a situation. He was not suitable to be "parachuted in" for a position that required a level of experience in software development that takes years or decades.
That recruitment agency was immediately told that they were on their last chance. They would have been dropped but each of the other recruitment agencies engaged were almost as bad.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 11, 2015 at 2:48 am
cphite (11/10/2015)
...It was comprised of a join between two other views - their names don't matter - what matters is that each of those views was comprised of other views; as were the next views, and the next views, and the ones after that. All in all I think I counted 27 total views. I mapped the thing out and it took an entire white board.
...
Sounds like something I'm looking after. I can barely explain how much I dislike working on this system, it's virtually impossible to rewrite the views without a chunk of time I cannot take, as there are multiple procedures dependent on them. You start trying to work out what is happening but my poor brain cannot keep the whole thing in RAM, so by the time I get to the bottom of the view stack I've forgotten what was happening. Also because I am not always clear on the requirements (they're not written down and there are no comments) I have to go by trial and error on matching results and the aggregates, unions and distincts seem to have a lot of different, weird, consequences.
It will be rewritten from scratch before too long fortunately.
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply