• NULLPTR (unregistered)

    SELECT NULL into nullptr;

  • (nodebb)

    Why to DBA's think they do not need peer reviews???

  • PJ (unregistered) in reply to TheCPUWizard

    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!

  • LCrawford (unregistered)

    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.

  • my name is missing (unregistered)

    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.

  • Hans (unregistered)

    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 ...

  • MiserableOldGit (unregistered)

    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.

  • (nodebb)

    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.

  • (nodebb) in reply to TheCPUWizard

    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.

  • Kattman (unregistered)

    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.

  • (nodebb) in reply to MiserableOldGit

    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.

    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.

  • Erlando (unregistered)

    700 lines? Amateurs! Right now (as in right now) I'm looking at a 3000+ lines stored procedure.

  • 🤷 (unregistered)

    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!

  • MiserableOldGit (unregistered) in reply to Jaime
    -- so they added an aggregate function. I'm sure their rationale is that the server told them to.

    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?

  • (nodebb)

    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.

  • Bruce W (unregistered) in reply to Kattman

    Still waiting for my old code to show up here.

    We all have this fear if we are honest.

  • sebt (unregistered) in reply to Jaime

    <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.

  • Angela Anuszewski (google) in reply to Bruce W

    not me - but that's only because if someone posts anything from me, they will probably wind up in jail.

  • (nodebb) in reply to Angela Anuszewski

    (still waiting for their old code to show up...}

    not me - but that's only because if someone posts anything from me, they will probably wind up in jail.

    What - was your code pure childporn?

  • Bruce W (unregistered) in reply to Angela Anuszewski

    not me - but that's only because if someone posts anything from me, they will probably wind up in jail.

    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>

  • Jay (unregistered)

    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?

  • MiserableOldGit (unregistered) in reply to Jay

    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.

  • I'm not a robot (unregistered)
    If I'm reading it correctly, the sub-query has no alias to identify it
    Each of the two subqueries that appear in that snippet has an alias: one is Gender and the other is Category.
  • Your Mammas name (unregistered)

    More proof that SQL isn't fit for purpose.

  • sizer99 (google) in reply to TheCPUWizard

    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.

  • (nodebb) in reply to Your Mammas name

    More proof that SQL isn't fit for purpose.

    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?"

  • MiserableOldGit (unregistered) in reply to sizer99
    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.

    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 ....

  • I dunno LOL ¯\(°_o)/¯ (unregistered)

    If stored procedures are your hammer, every row of data looks like a nail.

    ...or something like that.

  • (nodebb)

    ... 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.

  • Barf4Eva (unregistered) in reply to Erlando

    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. .

  • Barf4Eva (unregistered) in reply to Hans

    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.

  • DBA Of One (unregistered) in reply to MiserableOldGit

    The recurring nightmare of modern management theory. Cheaper/ shinier = better.

  • Raj (unregistered) in reply to Erlando

    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).

  • Hasseman (unregistered)

    Been there, done that.

    Difference was that I was the HPC and the system was build by HPCs 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.

  • Earth's Mellow Monk (unregistered) in reply to Raj

    "Atlas Shrugged is not a novel to be tossed aside lightly. It should be thrown with great force."

  • (nodebb) in reply to Barf4Eva

    I have written SQL for almost 2 decades

    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.

  • MiserableOldGit (unregistered) in reply to Jaime
    I only choose to write non-trivial things in TSQL once in a while.

    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.

  • (nodebb) in reply to MiserableOldGit

    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.

  • Ville (unregistered) in reply to Erlando

    While I do consider 700 line SPs amateurish, I consider 3000+ line SPs even more amateurish.

    Professionals know when to stop

  • sizer99 (google) in reply to MiserableOldGit

    This reply (starting with 'It's a great marketing strategy') really deserves to be featured. It's the source of so much WTF.

  • Barf4Eva (unregistered) in reply to Jaime

    So true... I think about a couple massive legacy systems we have written PURELY in TSQL.... <shudder>

  • Darkenon (unregistered)

    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.

Leave a comment on “Hop Scotch”

Log In or post as a guest

Replying to comment #511706:

« Return to Article