Comment On The Query of Despair

Jeroen's colleague had the misfortune of being assigned to debug an intermittent, unspecified error in the one of the oldest of the legacy applications. "The good news is that I've isolated it to a database query," he told Jeroen, "the bad news is that I've isolated it to a database query." [expand full text]
« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5Next »

Re: The Query of Despair

2011-10-24 14:26 • by wisran charmendrofa (unregistered)
saya sangat prihatin dengan apa yang sedang di alami.

Re: The Query of Despair

2011-10-24 14:33 • by An innocent abroad (unregistered)
364270 in reply to 364218
C-Octothorpe:
boog:
I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.
Which is exactly why he shouldn't try to RE the bitch and should just rewrite it from scratch based on specs (HA!) or expected behavior...
But... that's how I wrote it in the first place! That, and trying to match expected and observed behaviour.

Re: The Query of Despair

2011-10-24 14:37 • by Kuba
364271 in reply to 364269
wisran charmendrofa:
saya sangat prihatin dengan apa yang sedang di alami.
Thanks for your concern. I am very concerned with what is being experienced, too :) LOL.

Re: The Query of Despair

2011-10-24 14:51 • by Wonk (unregistered)
Can someone just take that query to Mordor, and toss it in the firs of Doom?

Re: The Query of Despair

2011-10-24 15:07 • by C-Octothorpe
364274 in reply to 364273
Wonk:
Can someone just take that query to Mordor, and toss it in the firs of Doom?
What good would throwing that at a bunch of evil Christmas trees do?

Re: The Query of Despair

2011-10-24 15:16 • by trtrwtf (unregistered)
364275 in reply to 364274
C-Octothorpe:
Wonk:
Can someone just take that query to Mordor, and toss it in the firs of Doom?
What good would throwing that at a bunch of evil Christmas trees do?


Perhaps he's hoping it would confound the forces of evil...
might work, I'm sure it would confound just about anyone who looked at it.

Re: The Query of Despair

2011-10-24 15:17 • by Mr A (unregistered)
364276 in reply to 364176
Steve The Cynic:
SilentRunner:
I don't get it. So much crap posted on The Daily WTF is for initiates only with no explanations for those of us with over 20 years programming experience who lack that finite bit of knowledge that makes YOUR WTF understandable.

Well, I have over 20 years' programming experience, and evidently I have the necessary piece of knowledge. In fact, it's in the article. The picture shows a hard-copy of *one* SQL query. HTF does an SQL query get complex enough to be spread over six pages?


Actually it's easy. I was landed with a piss-ridden Access arse end of a system, 15 years in the making. I traced one query and found that it relied on >200 other queries. The system had 2000 records and would regularly take 30 minutes to run a report. It happens when you give a complex IT system to a guy in marketing because 'hey - I've done this at home and it's really easy yeah!'.

Re: The Query of Despair

2011-10-24 16:12 • by Sinisa Milivojevic (unregistered)
There is a cure. Redesign entire schema and rewrite all statements.

Re: The Query of Despair

2011-10-24 16:22 • by C-Octothorpe
364284 in reply to 364281
Sinisa Milivojevic:
There is a cure. Redesign entire schema and rewrite all statements.
Thank god you didn't suggest something ridiculous...

Re: The Query of Despair

2011-10-24 16:36 • by Bill C. (unregistered)
A six page query isn't necessarily a problem if it's reasonably well-structured. You should be able to find several blocks (unioned sections or subqueries) in it that you can highlight and test separately.

If that query was well-formatted, though, it would probably be about fifteen pages.

Re: The Query of Despair

2011-10-24 16:39 • by gloin (unregistered)
I have the misfortune of working with a certain sailboat enthusiast's software and, sad to say, queries like this are per normal from that company.

Capta: abigo (related to the above-referenced company)

Re: The Query of Despair

2011-10-24 16:43 • by TheCPUWizard
364287 in reply to 364236
C-Octothorpe:
someone else:
someone:
Oh my god... it's full of stars.


My Stars.... Its full of Gods!!!
They must be crazy!


Against Stupidity...
...The Gods Themselves....
......Contend in Vain.

Re: The Query of Despair

2011-10-24 16:48 • by Bad Eyes (unregistered)
364288 in reply to 364157
The poop of DOOM:
Fr1st two words: SELECT ( SELECT. That's already very promising.

Also, not fr1st!
Something like....
SELECT(SELECT COUNT(NOmc_id) FROM (SELECT O_NEWCARAVNAS_NC LEFT JOIN

Re: The Query of Despair

2011-10-24 16:57 • by Jorg (unregistered)
Die Email sieht verdachtig nach germanischen ... Vielleicht (wie die Deutschen tun) es muss nur ein paar sehr lange Wörter (oder Tabellennamen)

That is, when German's haven't got a word to describe something, they run a description together into one word, so they tend to have long words for some things. Perhaps all the tablenames were such long words....

Re: The Query of Despair

2011-10-24 16:57 • by Herby (unregistered)
Blasting this from space is not enough. One needs to call in a tactical nuclear strike with a remote drone, which ought to obliterate enough to get it off of the face of the earth.

Of course, TRWTF is probably SQL in the first place. This company should be relegated to using RPG-II for the rest of time.

Re: The Query of Despair

2011-10-24 17:01 • by DBA (unregistered)
364291 in reply to 364210
The poop of DOOM:
boog:
On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.

To apply for Representative Line, it should be a line, not six pages.
Easy query - trivial...Just a one-liner (albeit a longish oneliner)

Re: The Query of Despair

2011-10-24 17:05 • by Grant Fritchey (unregistered)
I want to see the execution plan. Please.

Re: The Query of Despair

2011-10-24 17:07 • by Some one (unregistered)
364293 in reply to 364256
This is not my real name:
Why are the worst WTFs on this site always in Dutch...
Because there's noone from India on this site to submit.

Re: The Query of Despair

2011-10-24 17:08 • by boog
364294 in reply to 364292
Grant Fritchey:
I want to see the execution plan. Please.
1. Find the original programmer.
2. Execute him.

End of plan.

Re: The Query of Despair

2011-10-24 17:16 • by da Doctah
The reason this doesn't happen more often isn't because it's a WTF. It's because whoever's name is on the code only gets credit for writing one line of code.

Re: The Query of Despair

2011-10-24 17:16 • by Grant Fritchey (unregistered)
And imagine how long it takes to compile. I saw an 86 table join that took 3.5 minutes to compile (ran like a clock afterwards though). This monster could beat that easily I'm sure.

Re: The Query of Despair

2011-10-24 17:48 • by mudimba (unregistered)
It could be worse . . . at least it is not a regex

Re: The Query of Despair

2011-10-24 17:49 • by Ben (unregistered)
I wrote a query like that once. It was for a report that the business requested. It was the same query unioned about 12 times (one for each of the cases they had) where each query had about 30 joins to get the requested data. It also took about 30 minutes to run, but did do a job that 4 people used to take a week to do. I printed it out once and it took 8 pages (nicely formatted though).

I was so proud of it when I finished I ended up stripping all newlines inverted the text colour so it was white on black and saved it into an image which I used as my wallpaper for a while.

Re: The Query of Despair

2011-10-24 18:19 • by airdrik (unregistered)
364300 in reply to 364289
Jorg:
Die Email sieht verdachtig nach germanischen ... Vielleicht (wie die Deutschen tun) es muss nur ein paar sehr lange Wörter (oder Tabellennamen)

That is, when German's haven't got a word to describe something, they run a description together into one word, so they tend to have long words for some things. Perhaps all the tablenames were such long words....

So to counter that the German's have been working on simplifying their language in a process with the fittingly concise and descriptive name of vereinfachungsbestrebung!

So to fix this query they merely need to do some simplifylongquerybyrestructuringandremovingredundancy, or some similar.

Re: The Query of Despair

2011-10-24 18:40 • by Obscure Asimov Reference (unregistered)
364302 in reply to 364287
Aye, Robot.

Re: The Query of Despair

2011-10-24 21:31 • by Nagesh (unregistered)
364304 in reply to 364289
Jorg:
Die Email sieht verdachtig nach germanischen ...

Looks Dutch to me. Matches with the name "Jeroen", too.

Re: The Query of Despair

2011-10-24 21:52 • by Well.. (unregistered)
Sound like business as usual to me, nothing to see here folks move along...

sino: a conjunction of sin and no, count me out.

Re: The Query of Despair

2011-10-24 22:00 • by scott (unregistered)
364306 in reply to 364157
"SELECT ( SELECT" Nothing necessarily wrong with that part.

Re: The Query of Despair

2011-10-24 22:02 • by scott (unregistered)
364307 in reply to 364292
Grant Fritchey:
I want to see the execution plan. Please.


Not like 98% of the developers that read this site ever give a crap about execution plans...

Re: The Query of Despair

2011-10-25 00:11 • by Salami
A query like that has to grow over time. It starts out as a complicated query, then someone spots a bug in a certain case or wants to add a field. Instead of fixing the query and risking breaking something else, it gets wrapped in parentheses as a subquery, because that is "safer". Repeat 10 or 12 times and you have a 6 page query.

Re: The Query of Despair

2011-10-25 00:27 • by Cheong (unregistered)
There IS a reason for programmers doing maintenance project have a temptation to rewrite everything from scratch.

Re: The Query of Despair

2011-10-25 00:43 • by My Name Is Here (unregistered)
364311 in reply to 364209
My Name Is Missing:
I once helped out on a project with a SQL query that long that took 70 seconds to complete on average (not good for a web app!). I fixed it by extracting the relevant data from the database and doing the search in RAM, reducing the query time to sub second. Some things are best not done with a relational database.
I suspect that it was a problem with the management of the database (collecting stats, not indexed properly etc.) or skillset of the programmer (shit SQL).
Even with a shitty DB design, if you can move the data off the disk, across the network and do it faster in memory on a separate computer something is obviously wrong isn't it?

Re: The Query of Despair

2011-10-25 00:58 • by Spontaneous (unregistered)
This is nothing. Teradata database and tools handle queries many times longer than this.

Re: The Query of Despair

2011-10-25 01:13 • by Digger (unregistered)
Complex programs should be complex. They cannot be composed out of simple 'Hello World' snippets.
Work harder, and someday you will grow to that level.

Re: The Query of Despair

2011-10-25 02:16 • by Henk (unregistered)
364315 in reply to 364289
Actually, it's Dutch - probably Flemish, given the way some names are spelled.

Which brings me to another point: what happens when Monique Dewandelaer leaves the company?

Re: The Query of Despair

2011-10-25 02:23 • by Marcus Mönnig (unregistered)
I can beat this. I have a 50 pages printout of a SQL statement that was still running after 48 hours. (Hibernate...)

Re: The Query of Despair

2011-10-25 02:26 • by pjt33
364318 in reply to 364299
Ben:
I wrote a query like that once. It was for a report that the business requested. It was the same query unioned about 12 times (one for each of the cases they had) where each query had about 30 joins to get the requested data.

Was it not possible to write it as a loop through the cases aggregating into a temporary table and then select from that?

I have investigating a marginally less nasty legacy query on my list of things to do for one project. It provides the data for the dashboard page of the admin side of a website. One query to count the total number of users, users active in a given window, countries represented, sales in different categories, this, that, and the other. Unfortunately there is no documentation, and the client can't remember the precise details of half of the values it gets back ("Does this one include test users or not?") so it's probably going to be a case of gathering requirements and redoing it largely from scratch as a dozen or so queries stitched together in application code, using the subqueries of the existing SP as a guide.

Re: The Query of Despair

2011-10-25 02:27 • by Shinobu (unregistered)
364319 in reply to 364210
The poop of DOOM:
To apply for Representative Line, it should be a line, not six pages.
And thus you casually opened up the possibility that this is a Representative Six Pages.
Jorg:
Die Email sieht verdachtig nach germanischen
Nope. As has already been noted, it's Dutch.

Re: The Query of Despair

2011-10-25 02:29 • by pjt33
364320 in reply to 364311
My Name Is Here:
My Name Is Missing:
I once helped out on a project with a SQL query that long that took 70 seconds to complete on average (not good for a web app!). I fixed it by extracting the relevant data from the database and doing the search in RAM, reducing the query time to sub second. Some things are best not done with a relational database.
I suspect that it was a problem with the management of the database (collecting stats, not indexed properly etc.) or skillset of the programmer (shit SQL).
Even with a shitty DB design, if you can move the data off the disk, across the network and do it faster in memory on a separate computer something is obviously wrong isn't it?

I don't know about Oracle, but MS Sql Server is fairly limited in its string manipulation. I wouldn't be surprised if the missing item from the programmer's skillset was integrating .Net functions into Sql Server.

Re: The Query of Despair

2011-10-25 02:31 • by Bobs Lawn Service (unregistered)
This is what happens when SQL turns fully sentient and tries to crush the souls of us puny humans. I also think that you can fix something like that. It's just a matter of beautifying the code so that it is human readable and just take it one logical step at a time. Then you take two weeks of leave and a few visits to a shrink to recover.

Re: The Query of Despair

2011-10-25 02:36 • by Watson
364322 in reply to 364239
The "Z-Guy":
Daniil S.:
trtrwtf:
As a wise man once said, "Just remember: what has been seen, cannot be unseen."
I wish I had a dime for each time I had that experience
Then you could feel like a whore, too.
The sort that turns ten cents for a trick.

Re: The Query of Despair

2011-10-25 03:07 • by +9 (unregistered)
364323 in reply to 364155
Spivonious:
...

That's insane.

captcha: populus - boring game


Only mad scientist can repair something made by another mad scientist...

Re: The Query of Despair

2011-10-25 03:25 • by Severity One
Well, only yesterday I had to deal with a ten-way join in Oracle, with four of the joined 'tables' actually being 'with' clauses, one of which had another four-way join.

And no, it can't be made any simpler. I blame the supplier.

Re: The Query of Despair

2011-10-25 03:26 • by Jigar (unregistered)
364325 in reply to 364160
Yes Most probably it is Hibernate

Re: The Query of Despair

2011-10-25 03:49 • by ggeens
364326 in reply to 364176
Steve The Cynic:
HTF does an SQL query get complex enough to be spread over six pages?


You need a SQL programmer to achieve that.

Regular application programmers might add an outer join or a subquery. (Some adventurous programmers might add both.) If it gets more complicated, they'll add some application code to link several queries.

There are two ways to end up with such a query:

- A query generator.

- A database developer. They insist of getting all data in a single query, no matter how complicated it gets.

(BTW, I'm pretty sure I know P. Vandamme, and I worked on that project. It's in Dutch - Belgium actually.)

Re: The Query of Despair

2011-10-25 04:06 • by Sir Robin The Not-So-Brave (unregistered)
364327 in reply to 364258
frits:
This is not my real name:
Why are the worst WTFs on this site always in Dutch...
What do you expect from a country full of dikes that little boys have to save from bursting, by plugging them with their thumbs? ;)

It's from Belgium. The surname Vandamme is a lot more common in Belgium than in the Netherlands (Vandamme a toponym, it comes from the town of Damme in West-Flanders, near Bruges).
If you look carefully at the query, you will find the name of a sales person, Monique Dewandeleer. She lives in Machelen according to Facebook. Unfortunately she doesn't seem to have a LinkedIn account, so I can't find out where she works. Probably some car leasing company in or near Brussels. Should be easy to find out with a bit more research.

Re: The Query of Despair

2011-10-25 04:22 • by Oneway (unregistered)
364328 in reply to 364161
Confusing feelings of arousal?

Re: The Query of Despair

2011-10-25 04:29 • by just me (unregistered)
364329 in reply to 364247
GrammerSnarker:
Let Alex have a the life, we humans can parse it.

That may be OK for you, but what about us spam bots?

Re: The Query of Despair

2011-10-25 04:42 • by Fadzlan (unregistered)
364330 in reply to 364285
Considering that the query that long probably be deeply nested, even if you manage to beautify it, it would be severely indented that it wouldn't make a difference in readability anyway.

Oh yeah, I don't think it will take 15 pages. My opinion is that it probably takes the whole wall. Hah!

Re: The Query of Despair

2011-10-25 04:48 • by Philosopher (unregistered)
364331 in reply to 364264
Nagesh:
none of you peepul have working experence with Kristal reports. That's one softwear that will write complex querees and mess with your mind all time.


Oh, come on. If you're just going to misspell words to achieve the illusion that English is not your first language then I suggest not applying that to the name of a software product that after an install shows up on on your start menu as 'Crystal Reports' and would therefore be right in front of your nose.
« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5Next »

Add Comment