- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
SELECT NULL into nullptr;
Admin
Why to DBA's think they do not need peer reviews???
Admin
Who will do the peer reviews? Another HPC of course!
1st HPC: Here's my code. Does it look OK to you? 2nd HPC peer reviewer: What? Where are all your pointless cross-joins? Why do you only have three pointless temporary tables? Get rid of all those explanatory comments and do it again!
Admin
My recurring nightmare is that my multi-level sub-queries (actually as table-valued functions() in SQL Server) will end up creating one of these grinding queries and bring the whole place to a halt.
Admin
I worked one a product search engine two decades ago that some "expert" had built that worked great on a handful of items. One you pointed it at the full db, the 4 page sql statement at the core took a minute to return a single result on production hardware. I rewrote it to extract the data into memory once a week (it did not change more than that) and did all the work in memory and handled thousands of queries at a time with sub second response.
An old definition of an "expert" is someone 40 miles away from home.
Admin
A 700 line stored procedure? I had the "pleasure" of working with a system where a frequently called stored procedure called another 40 procedures and functions, up to 5 levels deep. Also full of temp tables to pass data along ...
Admin
Why would they do a MAX on a title field? I can't think of an answer that wouldn't point to a desperately WTFy schema, or at least some horrible inability to understand basic normalisation.
Admin
One of the systems at work does this. We merged with another company and kept their system and our system completely separate rather than have a project to consolidate the data (that would take too much time instead of pushing forward, you see), and their system is really just reverse-engineering the COTS system they had been using before so a lot of their stuff still calls down into the old system's tables, including a lot of synonym and, of course, it's on another server so it has to use network hops.
Admin
I had a SQL-focused developer say to me once: we should put all of our business logic in stored procedures; that way we can deploy them without code review or change control.
On a side note - the company I currently work for has written many things that are nearly identical to today's submission.
Admin
As I have worked as an HPC for most of my career, I can attest to the fact that most HPC's are not really experts but rather those of us that prefer to solve one problem and move to the next leaving behind 5 we created ourselves. I have tried hard not to do this, but I am sure somewhere along the lines I have done this and perhaps worse. Still waiting for my old code to show up here.
Admin
Because they have a group by. they added the title field to the select list and got this error message --
Column 'title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
-- so they added an aggregate function. I'm sure their rationale is that the server told them to.
Admin
700 lines? Amateurs! Right now (as in right now) I'm looking at a 3000+ lines stored procedure.
Admin
I have seen views based on views which where in turn based on views. But I never had encountered a view that was based on a view on a remote server. Wow!
Admin
I'm going to go out on a limb and guess that their sql editing tool of choice made that very easy .... MS Access QBE perchance?
Admin
The root cause of this is, as usual, the attempt to use 3rd party business software. Hence the need for multiple databases, replication, and multi network hop SQL queries. Dear business people! If you're big enough that you need software, write your own. Otherwise, use Excel/ Access. Do not buy 3rd party software. It never works.
Admin
We all have this fear if we are honest.
Admin
<Why would they do a MAX on a title field? Because they have a group by>
You forgot that they only put the GROUP BY in because their initial attempt returned duplicate rows. And the HPC "solution" of sticking in a DISTINCT didn't fix the problem.
Which is a pity, because SELECT DISTINCT is usually(*) a reliable indicator that what you're about to read is going to pipe more WTF-ery into your brain than you would wish on your worst enemy.
(*) usually. DISTINCT has its uses. 99% of the time it's not used for one of these uses, but to cover up major WTF.
Admin
not me - but that's only because if someone posts anything from me, they will probably wind up in jail.
Admin
(still waiting for their old code to show up...}
What - was your code pure childporn?
Admin
Angela: Wow, this code is BAD. Boss: Well, it's for a critical top secret system. As long as it works. Angela: I can't unsee it! I'm beginning to hallucinate! It's calling to me! Boss: Not again... <pulls Neuralyzer from pocket>
Admin
How does this JOIN work?
LEFT JOIN ( (SELECT ItemNo, Code AS GenderCode, NULL AS CategoryCode FROM rsoption WHERE [Key] = 'GENDER') AS Gender FULL OUTER JOIN (SELECT ItemNo, NULL AS GenderCode, Code AS CategoryCode FROM rsoption WHERE [Key] = 'PRODUCTCATEGORY') AS Category ON Gender.ItemNo = Category.ItemNo ) ON Gender.ItemNo = T.ITEMNO OR Category.ItemNo = T.ITEMNO
If I'm reading it correctly, the sub-query has no alias to identify it, but somehow you can join on the nested aliases. This does not work in my copy of MS SQL Server 2016, but even so, if that's the case then why bother bracketing the FULL JOIN in the first place?
Admin
When you create a subquery containing tables joined using full outer or union it all appears (outside the parantheses) as if it is the first table/select statement ... Gender, I think you probably know that though. I am assuming ithas to be joining the whole subquery presented as Gender as joining to one part of it would be nuts. Although I'm surprised (like you) that join works without the whole subquery having an alias. I'll take your word for it on 2016 I would always declare an alias without thinking about it, just too damned confusing and verbose not to.
Mind you, I'd never intentionally write a monster like that ... although I have inherited plenty to "optimise" over the years. Perhaps that's just a paste error or some anonymisation goof by Remy.
Admin
Admin
More proof that SQL isn't fit for purpose.
Admin
Hacks of all types dislike having their codes and designs reviewed, but this is less 'DBA' and more 'HPC'. They're getting paid crazy amounts of money to parachute in and crank out something that barely works on a (usually) crazy schedule, then GTFO. Maintainability is the last thing they care about.
Admin
A single development artifact that is dependent on the names and properties of a half dozen different entities is a bug waiting to happen. I am entirely unable to fathom how people write these things and go to sleep at night.
Every time any has ever told me "use stored procedures", I think, "Why would I use a technology that has no strong type checking, a procedural language that looks right out of the 1970's, a horrible debugger, a weak function library, weak development tools, and very little support for code modularity?"
Admin
It's a great marketing strategy I've seen banging out for 25 years. Rock up, tell the client to "divide their business into logical function areas" and identify "best of breed" solutions for each area, which means they just draw lines between the illogical responsibilities of existing departments and then get confused. The HPC then tells them to analyse the market, they are, of course, equally incapable of this artificial task so the HPC does it for them.
Now the company has been completely dragged into the borg, they sign an endless succession of third party and outsourcing agreements with software providers who (unknown to them) are just friends of the HPC and kicking back some wedge, natch.
What's left is "integration". The meat and potatoes that I think most of us get to do for a sordid living. Sometimes the HPC succeeds and they end up with such a level of control over their client they practically own them ... this often moves on to the hell of a leveraged buy out. Sometimes they fail, the client fails, the HPC got their wedge and they move on. In this case the client escaped and is hoping someone can dig them out of the mess, only they really don't understand how they got into it, after all, those guys had such nice suits, and lovely business cards, and fancy cars, and you programmer types all wear faded jeans and t-shirts and 3-day stubble, and that's just the girls ....
Admin
If stored procedures are your hammer, every row of data looks like a nail.
...or something like that.
Admin
... and MAX(dbo.TRIM(x)) means doing a string operation on every value before figuring out which is the greatest. Extra fun if applying that to a whole table-scan. More so if the table scan is traversing the network first. Being dbo.TRIM makes a local function, where a better-formed query with remote view could ask the remote database to provide filtered results.
Admin
Yeah, totally agree. Hell, I wanted to recall my horror of some bad ITV function someone wrote over a decade ago that still gets used here... Was reviewing it in all its 40+ join misery... plenty of which is hidden behind views, and views that make perhaps some of the joins redundant. .
Admin
I have written SQL for almost 2 decades, and this has got to be one of the WORST things I've seen people do... 5 levels of nesting?? I am VERY sorry. :(
I usually start the barfing process at 3 levels deep, dependent on how the procedures get nested.
Admin
The recurring nightmare of modern management theory. Cheaper/ shinier = better.
Admin
3000 lines? That's nothing. In a reporting system I once had to debug machine-generated SQL queries of terrifying proportions, some had over a megabyte, and I'm talking purely select/join/group by, not DDL or inserts with data.
That means a single SQL query the length of an Ayn Rand novel, with group by clauses the length of John Galt speech in Atlas Shrugged (for those who haven't had the pleasure yet, that speech alone lasts for 3h in the audiobook version).
Admin
Been there, done that.
Difference was that I was the HPC and the system was build by HPC
s from well known companies and Im and form a small one. Reached a gain of more than 36.000% in speed. This was 25 years ago.Admin
"Atlas Shrugged is not a novel to be tossed aside lightly. It should be thrown with great force."
Admin
I've been doing MSSQL since 1995. I'm very happy I have the level of familiarity I have to figure out other people's messes, but nowadays, I only choose to write non-trivial things in TSQL once in a while. Database side work is great when you need to speed up a problem section of a process, but it should almost never be your first choice.
Admin
Isn't that about being rewarded for doing it right in the first place? Most normal tasks shouldn't need massive complicated gobs of sql to complete, they come about because the schema(s) are inappropriate for the domain or just plain awful.
I remember a guy telling me once that when he interviewed people for database programming roles he would give them paper and a pen and ask them to write a chunk of SQL to move through a table using a cursor ... if they could do it from memory there was no way he was letting them near his databases! A bit harsh, but it did make me laugh, and I think if it every time I open a procedure and see either that anti-pattern or the outer-join-everything-and-then-filter one.
Admin
I agree.
I work with people that think SQL first. I was working on a problem where a particular unique customer wasn't being identified properly. The conversations floating around me were all using language like "I think it need to be changed to LIKE 'xyz%' instead of LIKE '%xyz%'". It turns out that a string.Contains("xyz") needed to be changed to start.StartsWith("xyz"), but it was very informative to see that a half dozen people jumped to the conclusion that a UI feature would have been implemented in the underlying query. BTW, these records weren't being filtered, the xyz records were simply being displayed differently.
Not surprisingly, the code I'm left to maintain has SQL statements embedded in the UI layer. Literally. I have at least a hundred aspx pages with SqlDataSources that have queries in the page markup. Some have queries with cross server joins, and some have queries on views that reference remote servers.
Admin
While I do consider 700 line SPs amateurish, I consider 3000+ line SPs even more amateurish.
Professionals know when to stop
Admin
This reply (starting with 'It's a great marketing strategy') really deserves to be featured. It's the source of so much WTF.
Admin
So true... I think about a couple massive legacy systems we have written PURELY in TSQL.... <shudder>
Admin
I guess it's one of the perks of the job, as an HPC you aren't around long enough to have to clean up your mess.