• (unregistered)

    You don't have to be an SQL genius to see that for one thing, they're doing the same query over and over and over again to get different columns, instead of selecting multiple columns in one query.  I bet eliminating that would help a bit.

  • (cs) in reply to

    To the previous poster -

    I disagree.  Those individual select statements are needed in order to name the column in the result set (ie, "AS [do.own.adr.City]").   I don't think there is a way to do that (create named derived columns) while selecting multiple columns from the source table.

    Please correct me if I am mistaken.

  • (unregistered)

    WTF!

    Do you think that SQL was generated by a graphical tool? Who could actually write all that out and then sit back and think to themselves "yeah, that's some nice work"?

    I've written some pretty hairy SQL in my time but this makes me look like a clean lean querying machine by comparison!

  • (unregistered)

    I see a lot queries like this where I work.  But the problem isn't that a single query is nasty, it is that they are using nested views from hell.  "Hey, but the SQL statement I am using is simple, so it must be fast".  WRONG. 


  • (unregistered)

    "(written by an American company who shall remain nameless, but which claims to be "the global leader in providing supply chain execution and optimization solutions")."  ---  I thought you were talking about my company till I read the SQL.  Scary stuff

  • (cs) in reply to Blue
    Blue:
    To the previous poster -

    I disagree.  Those individual select statements are needed in order to name the column in the result set (ie, "AS [do.own.adr.City]").   I don't think there is a way to do that (create named derived columns) while selecting multiple columns from the source table.

    Please correct me if I am mistaken.



    select column1 as c1, column1 as c2 from foo  is one way to rename the column in the results. 

    If I had to deal with this and couldn't modify the code, I think I would just create my own client program to query the data in a sane fashion.
  • (cs)

    Yeah, nested views can be optimized into a select statement like the one here.  This would be written better as a DLL that returned a rowset/recordset, keeping the statements simple.  That is one huge query.

  • (unregistered)
  • (cs)

    Oh boy, where do I begin? First off, M@nh@77@n Associates is a crap company. This isn't the first case where people have complained about their services and support. Second, that's an interesting view. That's the first time I've seen SQL that long [:O]. Being inexperienced in SQL, it was a WTF in itself to me. I'm used to "SELECT d., p. FROM table_d d, table_p p WHERE d.id = p.id &&.."; you know... simple things. I'd like to see that stored procedure some day, but yet... I'm still praying to God, Allah, Buddah, and <insert other deity name here> that I don't ever have to deal with it.

  • (unregistered)

    Wow that is bad.

    I am trying to figure out this one: 

    [code language="vb"]
    SELECT DISTINCT ... NULL AS [d.shti.f.fcb.adr.Address1] ...
    [/code]
    why!? That makes absolutely no sense!!

    And here I been late on projects because I understand that I'm a junior programmer and been trying my damndest not to create WTFs like this ;)  


  • (unregistered)

    That's an ugly baby alright!

    There's one thing I don't understand though... Your license prevents you from changing the SQL code, but it doesn't prevent you from publishing it?

  • (cs)

    Marcus :
     I discussed this with my manager, but it turns out that we don't actually have rights to modify the code, we only own a license to run it!!!

    While the bits are sitting on your computer, you have the right to rearrangement those bits however you damn well feel.  You would not be "changing" their code (which is on *their* computers) but bypassing it for a better written inhouse-developed module.   (You will not be able to sell the hybrid)

  • (cs)

    Whenever you have a big SQL statement like this, if you see the word DISTINCT in the beginning, it means the guy had no idea what we was doing.

    There's at least 50 columns in here defined simply as NULL !  Truly insane.   I would suspect the database structure is a mess as well.

    Good luck, Marcus!


  • (cs) in reply to Scott

    Scott, your solution doesn't work when I try it in SQL Query Analyzer against SQL Server 2003.

    Given Table1 with columns Col1 and Col2, and Table2 with columns Somecolumn, Othercolumn, and Id.

    SELECT
        col1,
        col2,
        (SELECT somecolumn AS col3, othercolumn AS col4 FROM table2 where table2.id=testvalue)
    FROM
        table1
    WHERE
        table1.[id]=12345

    yields the error:
    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


  • (unregistered) in reply to


    <font face="Lucida Console, Courier" size="2">
    SELECT DISTINCT ... NULL AS [d.shti.f.fcb.adr.Address1] ...</font>

    <font face="Arial">This code is obviously generated (by the column names). The NULL as is to set up a column, but put nothing in it.

    Now it all makes sense: someone used some tool to pick columns and then the tool generated the POS SQL you see here.

    A solution depends upon what you want to do with the data. If you're doing batch reporting (as opposed to real-time), you could simply set up a DW and drop the data into a single table. Smoke and reload on a regular basis (every day, every few hours, whatever). Then you just point your reporting tool towards the DW and you're gold. If you need realtime reports or you're using the data in other ways, then without being able to touch the DB, you're screwed, my friend.

    You can take comfort in the fact that most companies build tools without setting up a database using a well-thought-out schema. If I had a buck for  every time I've had to work on a DB that used varchar data fields for record numbers, composite keys to link tables, stacked fields (single field, comma-separated data), or had views that didn't have a single WHERE, then I could take off for 6 months.
    </font>
  • (cs) in reply to Blue

    Blue --

    I think the point is, if you need more than 1 column from a related table, you DON'T do this.  you JOIN to the table and return all the columns you need.  

    Even if you do only need 1 column from a related table, it is often more efficient and cleaner to use a join rather than a correlated SELECT.

    To be honest, the SQL is not worth analyzing to closely-- it is a complete mess.    Again, seeing that DISTINCT tells you "don't bother reading further !"    Not to mention the 15 correlated SELECT's in a row, all from the same table.

  • (unregistered)

    I have not dug into this view fully but it might be possible to turn it into an indexed view. Your select times would go down but any inserts or updates to the base tables would slow.

    You will need to check the requirements of Indexed Views.

    --John

  • (unregistered)

    Ahhh...I'm completely lost! [Z] What does all this convoluted SQL mean? Helllp Meeeee!

    This is some really messed up stuff dude.

  • (cs) in reply to Blue

    blue, your query should be:

    SELECT
        col1,
        col2,
        somecolumn as col3,
        othercolumn as col4
    FROM
        table1
    full outer join
         (SELECT somecolumn AS col3, othercolumn AS col4 FROM table2 where table2.id=testvalue) x
     WHERE
        table1.[id]=12345


    In reality the full outer join would probably be some inner join but because this is all contrived bs, who cares.

  • (cs) in reply to Scott

    I just love this forum software.  I had to copy that and paste that 0.5 point type into notepad to actually read it.

    To both who replied to me, I agree that JOINs are the best way to get the additional columns in the result set.   I was struggling too hard to make ANY sense out of that SQL to see the bigger picture.

    As a previous poster said, it's far too messed up to make sense of any of it.


  • (unregistered)

    [:|]

    a wtf has never made me speechless before

  • (unregistered)

    If you really can't change anything, try adding some indexes.   Try using the Index Tuning Wizard to see what you get from it.

  • (unregistered)

    btw, looks like they're hiring:

    http://www.manh.com/_op=267/careers/apply.html  

  • (unregistered)

    http://www.manh.com :
    "As the global leader in providing supply chain execution and optimization solutions, Manhattan Associates offers Integrated Logistics Solutions™—a source-to-consumption solution that provides enhanced value throughout your enterprise and extended enterprise. Our integrated yet modular approach enables you to benefit from the power of an integrated solution to maximize efficiency across the forward and reverse supply chains. Or you can focus on specific operational opportunities by implementing target solutions with your existing systems. Either way, your company will realize rapid return on investment through quick deployment and seamless integration."

    Oh.. roflol ..

    They make me go craaazy
  • (cs)

    I can't believe that nobody is willing to step up and defend this code!

    All the righteous d00dz must be on vacation.

    Or perhaps it is, actually, indefensible! WTF!

  • (cs) in reply to Dylan
    Dylan:
    I can't believe that nobody is willing to step up and defend this code!

    All the righteous d00dz must be on vacation.

    Or perhaps it is, actually, indefensible! WTF!



    I actually was trying, but in a very minor detail...

  • (cs) in reply to
    :
    I have not dug into this view fully but it might be possible to turn it into an indexed view. Your select times would go down but any inserts or updates to the base tables would slow.

    You will need to check the requirements of Indexed Views.

    --John



    Throwing an indexed view at this is about as good as tossing a can of gas on a barbeque to try and put it out.  The pref implications on a 20+ table indexed view just makes me shudder.

    <o:p></o:p>You’re not going to get much in trying to tune this. It just needs to be scrapped and re written.

  • (cs)

    What's the WTF?...

    Just throw more hardware...

    [;)]

    I'm kidding... realy... This is a WTF.

    As someone already said... a SELECT DISTINCT in a complex query it's a very good hint of a query that has incomplete conditions (or missing joins). In my team the keyword is almost banned (by code reviews).
    I have to say that I don't trust in subquerys... however I loathe subquerys as column expressions. Today WTF is an example of why... so... I can't defend today' WTF... maybe tomorrow...[:P]

  • (unregistered)

    WTF???

  • (unregistered)

    I'd modify the Database and anything else you want.  F@#k'em,  if it sucks that badly then go right ahead and change it. 

    What are they going to do?  Come in and audit the Stored procs against a printout of what they have?  Remember, stuff under a maintenance contract gets changed all the time by who knows who at the company.  If they were to acutally look you can just plead dumb and say they modified it at some stage "I don't remember when or by whom, but you guys did it"

  • (unregistered)

    The real WTF is whats wrong with your scroll bars :P  

  • (unregistered)

    Also note the use of "magic numbers" in the code:

        (SELECT     Code_desc
          FROM          sys_code
          WHERE      code_type = 521 AND appid = 3 AND Code_Id = STR(ch.QualityCode)) AS [dc.cnti.q.Desc]

    That STR function call looks dodgy too. If QualityCode has to be converted, does that mean it's a number, and Code_Id is text? [:S]

  • (cs)
  • (cs)

    You people are making fun of this when you should be taking notes... these are über programmers who have progressed to such deep understanding of computer systems and logical structures that, not only are they able to visualize the entire codebase of an exponential number of N-tier solution in their heads, but when they sit down to write that code from memory, they are obfuscating it as they type!

    Amatuers... all of you

    [:P] <-- obligatory

  • (cs)

    I've been back to this thread three times today and I can't help laughing each time.

    I don't know why you'd want to modify anything. Figure out what you like most about the app, figure out roughly how it works (obviously, this is really just the best obfuscation method of all!), and then tell them they can have their contract back and get a team together to write something quick and scalable and useful in a few weeks. Then sell it and rake in the cash, your competition obviously couldn't outcode a chimp.

  • (unregistered) in reply to Isac

    pure genius, isac. pure genius.

  • (cs)

    As a Data Warehouse architect and developer, I've seen some interesting SQL in source systems along the way. A top enterprise CRM system had my record with a query involving 44 tables...

    But this leaves me speechless![:#]

  • (cs)

    Gee, whomever wrote this must be a real evil genius to write something that only they themselves can understand. Force a poor company to use your system and forbid them to modify it themselves. Require them to sign a maintenance program and just provide sloppy solutions. No wonder people can get rick fast in the IT industry. You just have to pretend you're good and make lots of false claims and false promises...

    I know one thing... If I would write such a piece of SQL stuff, my teacher would really send me back to kindergarden so I can learn the things I should have learned the past 16 years... Geez, what a piece of [edit]...

  • (unregistered)

    Proof that cursors are not the root of all evil!

    The Board of that company must have Unisys stocks. Appropriate hardware to execute that view is a Unisys ES 7000.

     

     

  • (unregistered) in reply to JamesCurran
    JamesCurran:

    Marcus :
     I discussed this with my manager, but it turns out that we don't actually have rights to modify the code, we only own a license to run it!!!

    While the bits are sitting on your computer, you have the right to rearrangement those bits however you damn well feel.  You would not be "changing" their code (which is on *their* computers) but bypassing it for a better written inhouse-developed module.   (You will not be able to sell the hybrid)

    Nope, sorry, that's wrong. If you have signed a license with someone you have entered into a contract which alters your rights with respect to anything the contract governs, including your ability to use your own hardware and the bits in it. They might have the moral right, but they might not have the legal right. One would want to review the license/contract carefully to find potential loop-holes.

  • (unregistered)

    Someone mentioned "hiring". There is a certain irony that people who write SQL like this are still being hired...and being compensated very well for it. It's all about "padding" that resume.

  • (unregistered) in reply to

    Katja: is that picture really of you? If so, has anyone ever said you look a lot like Renee Zweileger? (if that's how you spell it)

  • (unregistered)

    That's Manhattan Associates, Inc. right? I guess I must be the 100th to google it.

  • (cs) in reply to

    ANONYMOUSSSSSS:
    Renee Zweileger? (if that's how you spell it)
    Renee Zellweger

    <FONT style="BACKGROUND-COLOR: #efefef">Zweileger = "Two casually?" LOL</FONT>

  • (unregistered)

    Hello. I'm a recent college grad with a computer science degree. Unfortunately I have no real world experience, but I would like to be a SQL administrator and be paid a minimum of $100K/yr. plus full benefits and perks. Should I simply pad my resume accordingly, or are there any other steps I should take?

  • (unregistered) in reply to
    :
    Hello. I'm a recent college grad with a computer science degree. Unfortunately I have no real world experience, but I would like to be a SQL administrator and be paid a minimum of $100K/yr. plus full benefits and perks. Should I simply pad my resume accordingly, or are there any other steps I should take?

    You used the correct terminology when you said you "would like" these things. Sorry to burst your bubble, but you have a long way to go in terms of experience and most of all...common sense.
  • (cs)

    <FONT style="BACKGROUND-COLOR: #ffffff">Any chance running it through SQL Profiler might help?</FONT>

    Possibly upgrade the machine, give it way more CPU and much faster drives that you'd think it needs (15k SCSI with tons of cache/ RAID for example)?

    I always got a lot of gains from Oracle by working with the query plan and optimizer hints... never had to do this with SQL Server though.

  • (unregistered)

    <FONT style="BACKGROUND-COLOR: #efefef">At least all the text lines up nicely in the query.  WTF indeed.</FONT>

  • (unregistered)

    pick up a shotgun. aim at database server. conserve sanity. fire.

  • (unregistered) in reply to

    Grab DBA by lapels. Slap around incessantly. Shake violently while screaming profanity. Smile.

Leave a comment on “One View to Rule Them All”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article