• Abso (unregistered) in reply to dgvid
    dgvid:
    In a config file?! Good grief. This is what the Windows Registry is there for people!

    Don't be silly. Registry entries are limited to 1024 characters.

  • (cs)

    What is a better way of doing this in SQL?

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE( REPLACE(REPLACE( REPLACE(Documents.Destination,' ',''),')', ''),'(',''),'-',''),'/',''),'.',''),'*',''), ',',''),';',''),'',''),'-','')

  • JayC (unregistered)

    It's kinda a wtf, but I won't call it horrendous: a. certainly shouldn't be all on one line. Better to be rewritten in a way usable as a stored proc. at least the query plan would be saved. b. there seems to be replaces which could, at the least, be placed in a user defined function. TRWTF is that standard SQL doesn't have anything to aggregate character class replacements into a single statement. c. That's one mother of a query. I just bet it can be broken down into views that could (and should) be used elsewhere. d. There appear to be a few redundancies to the query itself... but sometimes that's good in a query, performance wise. e. as mentioned, there are SQL injection issues in various parts of the query.
    "SELECT TOP REPLACE_NUMBER_OF_ROWS_TO_RETRIEVE" " AND History.TRDateTime > 'REPLACE_WHERE_CLAUSE_CRITERIA' " even if there's no issue with injection with the software as written, the query plan is going to be generated every time the SQL is executed.

    But there are some Possible benefits. a. you get all the data you need with a single transaction. Transactions are expensive. (not that you couldn't get the data you need in multiple queries within a single transaction, either, but that could potentially be more complicated. I'm not sure how SQL server handles locking tables for transactions in joins, but it seems to do pretty well in avoiding transactions where the locking order causes the whole transaction to fail. If you start handling that manually, you may potientially cause more transaction locking issues (but then again, you may be able to avoid them, too). b. as it is gotten within a single transaction, you can be certain the data is, to whatever transaction isolation level set, valid.

    Also, you CAN index views, but regardless, if the views aren't indexed, the query plan generator would likely be doing the same thing with the views as with the original query.

  • (cs) in reply to electric boogaloo
    electric boogaloo:
    I'm pretty sure if I worked with the author of this monstrocity, I would have shocked him repeatedy with a cattle prod.
    I think most devs would agree that you'd have every right to do so.

    btw, excellent username; can't stop laughing.

  • by (unregistered) in reply to boog
    boog:
    by:
    Views and functions can't be indexed...
    Not so. Several databases do support indexing functions/expressions, and some even support indexes on views.
    by:
    ...(at least not views that come from more than one table, which defeats the purpose of views IMO)... At least if it's a join, the query optimizer may be able to make the query a little more effecient than it is written.
    I believe that in most cases (if not all), the view becomes part of the query, so it's fair game for the optimizer as well. Views don't typically store data, just the SQL that identifies them, so the optimizer must parse the view as well as the rest of the query.

    Of course this means that views generally don't need indexes, because the underlying (multiple) tables' indexes are at the optimizer's disposal.

    Sorry, I should have specified SQL Server... An assumption I made. Can't speak for Oracle, TerraData, etc.

  • (cs)

    To me TRWTF is anyone say this is done for optimization. How fast does reading the configuration file have to be? Most programs read it once at startup, put the results into some king of global var and then never tough the configuration file ever again. So how does it really matter how "fast" it is to read it from the database? How slow is 5-6 readable queries compared to this monster?

  • frits (unregistered) in reply to electric boogaloo
    boog:
    I'm pretty sure if I worked with the author of this monstrocity, I would have shocked him repeatedy with a cattle prod.
    I'm pretty sure that if I was the one who worked with you, I'd have brought you up on assault charges.

    Your not too bright, are you?

  • (cs) in reply to Abso
    Abso:
    dgvid:
    In a config file?! Good grief. This is what the Windows Registry is there for people!
    Don't be silly. Registry entries are limited to 1024 characters.
    You guys are my heroes.
    by:
    I've seen stuff like this before, and it's terribly ineffecient. It looks like they're trying to generate massively complex reports (which obviously isn't a bad thing) from an RDB, likely a live application DB. This screams for OLAP or data warehousing... It's funny because this is the result of "business" wanting "up-to-the-second, real-time" reporting (which is never used). Then they cringe when someone generates a report that takes 8 minutes to create, which kills performance across the rest of the application.
    I would bet real money that your theory is correct. It's amazing how many requests my department gets for real-time reports, and very enjoyable to listen to my manager on the conference calls asking the requesters to justify it. And the dead silence that usually follows ...
  • (cs) in reply to ObiWayneKenobi
    ObiWayneKenobi:
    Lockwood:
    Omg omg omg omg! It hurts so bad. Make the bad man stop!!!!!

    Okay, Lockwood, show us on the doll where the bad SQL touched you.

    It tried to perform an invalid INSERT INTO query.

  • by (unregistered) in reply to frits
    frits:
    boog:
    I'm pretty sure if I worked with the author of this monstrocity, I would have shocked him repeatedy with a cattle prod.
    I'm pretty sure that if I was the one who worked with you, I'd have brought you up on assault charges.

    Your not too bright, are you?

    That's assuming you were able to walk to the HR department under your own power, or you're not locked in the tape vault...

    PS - I do realize you're trolling, I just wanted to throw in a BOFH reference.

    CAPTCHA: genitus - how appropriate

  • airdrik (unregistered)

    Who's to say that this query isn't already using views?

  • by (unregistered) in reply to airdrik
    airdrik:
    Who's to say that this query isn't already using views?

    True, however this leads to another WTF, and that is "where are the naming standards", which on a (very likely) 100+ table DB, it could get a little messy.

    But like another poster said, this could be an intention "f-you" to all developers who have to maintain this beast.

    captcha - uxor

  • (cs) in reply to SCSimmons
    SCSimmons:
    It's amazing how many requests my department gets for real-time reports, and very enjoyable to listen to my manager on the conference calls asking the requesters to justify it.
    Ehh?? Whasthisnow?

    Your manager doesn't just say "okay, when do you want it"?

  • Stan (unregistered) in reply to frits

    Bloody hell, someone needs relax a bit. And, it's "you're not too bright". But you knew that.

  • (cs)

    If that runs, it is GENIUS.

  • (cs)

    I am always amazed that business managers will say "I MUST have real-time reports" but are unwilling to actually portion off the required budget to then set up even a basic OLAP data mart to do it, but have the audacity to whine like babies when the reports or other parts of a system take a huge amount of time to run because it has to query dozens of tables on a production system that is being used elsewhere.

  • Detritus (unregistered) in reply to Robyrt
    Robyrt:
    But imagine how easy this is to deploy! Just update one line in a config file and your good to go!

    FTFY.

  • Spearhavoc! (unregistered) in reply to ObiWayneKenobi
    ObiWayneKenobi:
    I am always amazed that business managers will say "I MUST have real-time reports" but are unwilling to actually portion off the required budget to then set up even a basic OLAP data mart to do it, but have the audacity to whine like babies when the reports or other parts of a system take a huge amount of time to run because it has to query dozens of tables on a production system that is being used elsewhere.

    Gotta gleam the cube, man!

  • by (unregistered) in reply to Spearhavoc!
    Spearhavoc!:
    ObiWayneKenobi:
    I am always amazed that business managers will say "I MUST have real-time reports" but are unwilling to actually portion off the required budget to then set up even a basic OLAP data mart to do it, but have the audacity to whine like babies when the reports or other parts of a system take a huge amount of time to run because it has to query dozens of tables on a production system that is being used elsewhere.

    Gotta gleam the cube, man!

    Manager: What, you're telling me that I can't have a blazing fast enterprise application running on a massive relational database AND have real-time repoting of hugely complex reports for free?!?!?!?!

    You're fired!

    captcha: damnum - again, how appropriate (one more and it's a captcha hat-trick for me!)

  • by (unregistered) in reply to ObiWayneKenobi
    ObiWayneKenobi:
    I am always amazed that business managers will say "I MUST have real-time reports" but are unwilling to actually portion off the required budget to then set up even a basic OLAP data mart to do it, but have the audacity to whine like babies when the reports or other parts of a system take a huge amount of time to run because it has to query dozens of tables on a production system that is being used elsewhere.

    This: I remember once having to write a type of "points" system that would limit the amount of reports a user can generate per (configurable timespan). They can generate 50 "easy" reports before hitting their limit, but if they generate, for example, a massive year end report, then they won't be able to generate another for an hour/day/whatever.

    This had the nice side-effect of forcing the business user to actually THINK about what they want before just asking for 200k records, which they would dump to excel and promptly never look at again.

    captcha: genitus... HAT-TRICK! woo hoo!

  • imgx64 (unregistered)

    Gigantic SQL query aside, I can see two problems:

    1- It's not escaped, there are loose (no, I don't mean lose) ampersands in there! 2- Why is the query an attribute and not the content of the tag? I.e.,

    <add key="sqlSource" value="SELECT TOP ..." />

    as opposed to

    <add key="sqlSource">SELECT TOP ... </add>

    But of course, TRWTF is SQL (or was it XML? I can never remember).

  • (cs) in reply to boog
    boog:
    by:
    Views and functions can't be indexed...
    Not so. Several databases do support indexing functions/expressions, and some even support indexes on views.

    Indexing a computed column isn't so bad, but setting up the indexed view in SQL Server is so tricky on any substantial database that it's fairly accurate to say the indexed view is "not supported"

  • Jeff (unregistered)
    "From the first minute of the first hour of the first day of my job," Aaron writes, "I knew I had an epic WTF on my hands."
    I think, as long as the marriage / other forms of shafting haven't been consummated yet, you can still leave without taking any points on your record.
  • by (unregistered) in reply to jasmine2501
    jasmine2501:
    boog:
    by:
    Views and functions can't be indexed...
    Not so. Several databases do support indexing functions/expressions, and some even support indexes on views.

    Indexing a computed column isn't so bad, but setting up the indexed view in SQL Server is so tricky on any substantial database that it's fairly accurate to say the indexed view is "not supported"

    Thanks, that's what I actually meant but I forgot the exact details since it was 4-5 years since I came across this issue. I can recall many rediculous limitations, and ending up abandoning it altogether.

  • Harrow (unregistered)

    The complex replacements seem to be replacing the hyphen twice. When I see this I must suspect that another character was meant, and thus the code is not eliminating everything that the programmer expects it to.

    -Harrow.

  • backForMore (unregistered) in reply to Spearhavoc!
    Spearhavoc!:
    ObiWayneKenobi:
    I am always amazed that business managers will say "I MUST have real-time reports" but are unwilling to actually portion off the required budget to then set up even a basic OLAP data mart to do it, but have the audacity to whine like babies when the reports or other parts of a system take a huge amount of time to run because it has to query dozens of tables on a production system that is being used elsewhere.

    Gotta gleam the cube, man!

    +1 for wrist guards and pool copers

  • (cs) in reply to Harrow
    Harrow:
    The complex replacements seem to be replacing the hyphen twice. When I see this I must suspect that another character was meant, and thus the code is not eliminating everything that the programmer expects it to.

    -Harrow.

    I suspect you are wrong. There are two different hyphen characters, but not all character sets support both, so the second one got converted to the first in the process of moving from the code editor to the web page.

  • Grammer Nazi (unregistered) in reply to Detritus
    Detritus:
    Robyrt:
    But imagine how easy this is to deploy! Just update one line in a config file and your good to go!

    FTFY.

    No, the original poster was correct, and you're just an idiot.

  • FTFY (unregistered) in reply to electric boogaloo
    electric boogaloo:
    I'm pretty sure if I worked with the author of this monstrocity, I would have prodded him repeatedy with cattle.
  • Neville Flynn (unregistered)

    It's Great Sqlthulhu!

  • Cedric (unregistered)

    Worked with that product for a few years. We all miss the old RAIMA database !!!

    hehehe

  • BC (unregistered)

    I don't get what this code is supposed to do. My best guess is that it tries to delete all of human history?

  • Gunslinger (unregistered) in reply to frits
    frits:
    boog:
    I'm pretty sure if I worked with the author of this monstrocity, I would have shocked him repeatedy with a cattle prod.
    I'm pretty sure that if I was the one who worked with you, I'd have brought you up on assault charges.

    Your not too bright, are you?

    Neither are you. It would actually be battery, not assault. And then you could have made the pun yourself.

  • michael scott (unregistered) in reply to Mr. TA
    Mr. TA:
    That's huge.

    thats what she sad

  • Neville Flynn (unregistered) in reply to michael scott
    michael scott:
    Mr. TA:
    That's huge.

    thats what she sad

    Why would she be sad about that?

  • Jeff (unregistered) in reply to Neville Flynn
    Neville Flynn:
    michael scott:
    Mr. TA:
    That's huge.

    thats what she sad

    Why would she be sad about that?

    Because, she felt obligated to say it was huge, even though it really wasn't. Or so I heard.

  • Abso (unregistered) in reply to Abso
    Abso:
    dgvid:
    In a config file?! Good grief. This is what the Windows Registry is there for people!

    Don't be silly. Registry entries are limited to 1024 characters.

    I'm afraid I owe dgvid an apology; I had registry values (which can be up to 1 MB) confused with registry key names (which are limited to 255 characters). There's no reason* this couldn't go in the registry.

    Oops.

    *Aside from the fact that it shouldn't exist in the first place, of course. And the bit of the documentation where it says long registry values should go in files instead.

  • f. (unregistered)

    I've seen and worked with a lot worse! Some queries were stretching to just over 12,000 lines. Yes, OVER TWELVE THOUSAND! I've also fixed shitloads of bugs in these monsters and cut some of them in nearly half.

  • ClaudeSuck.de (unregistered) in reply to Jason Y
    Jason Y:
    I think it's better to go ahead and refactor as you go, making the code self-documenting, than to try documenting nasty code. You shouldn't use it prior to refactoring because programming against a well-documented mess is less productive than refactoring the mess to something clean and then programming against it. Simple, clean interfaces / contracts are more important than documentation, imo.

    You are right in your wishful thinking. However, I see queries like that quite often.

    Be sure to NEVER EVER refactor such a thing. Generally, nobody remembers old business rules and why they were there. "Documentation? Which documentation? We have one which is nearly up to date. Just "some" changes are not there. You'll have to find out which." So, they are just left in place. Refactoring impossible. If you do you will have to take the responsibility when the business people notice that the results are different from what they were before. And if you tell them at that moment that YOU made this change I don't want to be in your shoes.

    So don't change a hi-tech 16 valves 4 cylinder engine into a spinning wheel just because it makes a car run, too. You won't get very far.

    But I agree that it is difficult understand what exactly this thing is really producing. And when you imagine that the entire query is maybe only executed to retrieve a list of ID_FAX_LOGs (the rest is not used but you never know, so leave it) in order to produce a different report...

  • Socker-Conny (unregistered) in reply to by
    boog:
    Indexing a computed column isn't so bad, but setting up the indexed view in SQL Server is so tricky on any substantial database that it's fairly accurate to say the indexed view is "not supported"

    You mean like:

    create view MyView
with schemabinding
as
<query goes here>;

create clustered index MyIndex
on MyView (<candidate key for view>);

    Is that so hard? To someone who writes SQL once a year perhaps, but to someone who does this for a living?

    by:
    Thanks, that's what I actually meant but I forgot the exact details since it was 4-5 years since I came across this issue. I can recall many rediculous limitations, and ending up abandoning it altogether.

    There are some limitations, like that you can't use aggregates, which I guess has to do with some difficulty in knowing which rows should be updated when rows in the underlying tables (or views) get updated. (It doesn't seem too hard to me, but I've never written a RDBMS so I really can't say.) Or maybe that feature just didn't seem important enough.

  • ClaudeSuck.de (unregistered)

    | | v ^ TRWTF is

  • ClaudeSuck.de (unregistered) 
      ------
  |    |
  v    ^
TRWTF is

    sry

  • by (unregistered) in reply to ClaudeSuck.de
    ClaudeSuck.de:
      ------
  |    |
  v    ^
TRWTF is

    sry

    Huh?

    captcha: eros

  • (cs) in reply to f.
    f.:
    I've seen and worked with a lot worse! Some queries were stretching to just over 12,000 lines. Yes, OVER TWELVE THOUSAND! I've also fixed shitloads of bugs in these monsters and cut some of them in nearly half.

    Vegeta, what's the Analyzer say about the lines of code?

    IT'S OVER TWELVE THOUSAAAAND punches computer

  • (cs) in reply to ClaudeSuck.de
    ClaudeSuck.de:
    If you do you will have to take the responsibility when the business people notice that the results are different from what they were before. And if you tell them at that moment that YOU made this change I don't want to be in your shoes.

    Speaking from experience, it's more fun when the original report was outright wrong and performed incorrect calculations, and you're told to change the new one back to give bad results so it matches the old reports.

  • rawshark (unregistered)

    TRWTF is the database not supporting regular expressions and forcing crap like this

  • (cs) in reply to ObiWayneKenobi
    ObiWayneKenobi:
    ClaudeSuck.de:
    If you do you will have to take the responsibility when the business people notice that the results are different from what they were before. And if you tell them at that moment that YOU made this change I don't want to be in your shoes.

    Speaking from experience, it's more fun when the original report was outright wrong and performed incorrect calculations, and you're told to change the new one back to give bad results so it matches the old reports.

    Even better is when you're told that it used to work and you can see in the revision history that the bug has been there from the very beginning.

  • Abso (unregistered) in reply to pjt33
    pjt33:
    ObiWayneKenobi:
    ClaudeSuck.de:
    If you do you will have to take the responsibility when the business people notice that the results are different from what they were before. And if you tell them at that moment that YOU made this change I don't want to be in your shoes.

    Speaking from experience, it's more fun when the original report was outright wrong and performed incorrect calculations, and you're told to change the new one back to give bad results so it matches the old reports.

    Even better is when you're told that it used to work and you can see in the revision history that the bug has been there from the very beginning.

    My boss did that with a feature once. Demanded repeatedly to know why it had been taken out or, if broken accidentally, why the testers hadn't noticed that it had broken. The answer, of course, was that it had never been implemented. In fact, no one but him could even remember it being asked for or mentioned before.

  • Rob (unregistered) in reply to amischiefr
    amischiefr:
    Placing an SQL query in a config file? Maybe not the 'INDUSTRY STANDARD' (ohh beware the industry standard!), but not a wtf.

    amischiefr, I'd like you to meet Little Bobby Drop Tables, Bobby, I'd like you to meet amischiefr. I'm sure you'll be seeing a lot of each other.

  • (cs) in reply to Salami
    Salami:
    What is a better way of doing this in SQL?

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE( REPLACE(REPLACE( REPLACE(Documents.Destination,' ',''),')', ''),'(',''),'-',''),'/',''),'.',''),'*',''), ',',''),';',''),'',''),'-','')

    IMO, it's better to do this kind of thing in the user interface before it ever gets near the database!

Leave a comment on “All In The Config”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article