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-25 05:01 • by L. (unregistered)
364332 in reply to 364326
ggeens:
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.)


Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

Although I admit it's a lot of fun making huge queries to return the result using only one query ...

Re: The Query of Despair

2011-10-25 05:19 • by lucidfox
364333 in reply to 364179
apaq11:
The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...


Because Hibernate is obviously the only ORM in existence.

Look at the query closer. It uses table aliases like B and M1. It's an ORM generated query. It has to be.

Re: The Query of Despair

2011-10-25 05:28 • by geoffrey (unregistered)
well well well if it isn't SQL again rearing it's ugly little head

generic constraint language (GCL) is far superior as queries are limited to 256 characters.

Re: The Query of Despair

2011-10-25 05:42 • by Gert (unregistered)
Time for map-reduce?

Re: The Query of Despair

2011-10-25 06:13 • by Rfoxmich (unregistered)
I have this image I can't shake in my mind of someone saying:

CREATE VIEW MONSTER_QUERY_VIEW AS <insert the 6 pages of SQL here>

And then the inheritors of that view wondering why it takes so long to query that 'table'... I have a further image of someone saying..no problem...this is Oracle...we'll just turn that into a materialized view..updated every ...oh...say 10 seconds...

Re: The Query of Despair

2011-10-25 06:32 • by Watson
364337 in reply to 364315
Henk:
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?
I agree; hardcoded strings like this are bad. Obviously the thing to do is have employee names in a table along with their roles and join that table to retrieve the name they actually want.

Re: The Query of Despair

2011-10-25 06:37 • by Manadar (unregistered)
The letter is Dutch and it says:

Beste,

Het platform werkt zeer traag en we krijgen opnieuw foutmeldingen.
Kunnen jullie dit nakijken?

Following "Error encountered" and the actual query.

Translated this is:

Dear,

The platform is very sluggish and we are getting error messages again.
Could you check this out?

Re: The Query of Despair

2011-10-25 06:42 • by NH (unregistered)
364339 in reply to 364171
apaq11:
The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...

I wonder if that is something I have written...

Re: The Query of Despair

2011-10-25 07:11 • by Michael (unregistered)
364340 in reply to 364157
SELECT ( SELECT.

Nested queries start like that.

Re: The Query of Despair

2011-10-25 07:16 • by zmb (unregistered)
I know who wrote it: Chuck Norris.

Re: The Query of Despair

2011-10-25 07:43 • by JiP
There is a theory which states that the first thing you have to do when users complain about performance, is to replace the workstations and servers with far faster ones.
Another theory states that this has already happened.

Re: The Query of Despair

2011-10-25 07:46 • by JiP
But seriously,

it's probably just a badly-escaped or non-quoted smiley somewhere. Hard to pick out between all the semicolons and brackets that are supposed to be there...

;)

Re: The Query of Despair

2011-10-25 07:51 • by Not L. (unregistered)
364347 in reply to 364332
L.:
ggeens:
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.)


Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

Although I admit it's a lot of fun making huge queries to return the result using only one query ...

Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
Anyone with some knowledge of SQL will know that.

Re: The Query of Despair

2011-10-25 08:21 • by Watson
364349 in reply to 364347
Not L.:
L.:
ggeens:
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.)


Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

Although I admit it's a lot of fun making huge queries to return the result using only one query ...

Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
Anyone with some knowledge of SQL will know that.

In that case, for "temp tables" read "views".

Re: The Query of Despair

2011-10-25 08:32 • by L. (unregistered)
364350 in reply to 364347
Not L.:
L.:
ggeens:
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.)


Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

Although I admit it's a lot of fun making huge queries to return the result using only one query ...

Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
Anyone with some knowledge of SQL will know that.


You sir, live in a world where optimizers rock the hell out of everything.

Unfortunately, sometimes one has to work with MySQL (ouch) or with queries that land on the dark side of the optimizer (be it any optimizer really).

Of course the first step is to let the Optimizer take its shot at it.

But oftentimes, pre-processing (as in materialized views for example) is required to provide decent speed ;)

Re: The Query of Despair

2011-10-25 08:33 • by pjt33
364351 in reply to 364347
Not L.:
L.:
Nah . Anyone with some knowledge of SQL will go through temp tables instead of creating such a major mess if their objective is indeed to retrieve all results in one client-server loop.

And if the report is even remotely half used, any sentient being will be drawn to pre-processing quickly.

Although I admit it's a lot of fun making huge queries to return the result using only one query ...

Bollocks. Write the query and let the query planner/optimiser take care of it. Temp tables just hide information from the query engine. As with most compilers, with the right stats it will do a better job than most humans.
Anyone with some knowledge of SQL will know that.

You seem to assume that the aim is to write a query which wastes as little CPU time as possible. The rest of us are trying to waste as little maintenance programmer time as possible.

Re: The Query of Despair

2011-10-25 09:27 • by ņăĝęŠĥ (unregistered)
These queries are very common in ORM based architecture.

Re: The Query of Despair

2011-10-25 10:26 • by Ol' Bob (unregistered)
Yeah - so..? Six pages at about 68 lines per page = 408 lines. There's a query in an app that I have to maintain occasionally that's over 1800(!!!!) lines of SQL. Granted, though, they're not as dense as the lines in this query. :-)

Re: The Query of Despair

2011-10-25 12:18 • by Bertie Fraser (unregistered)
I have seen and dealt with, one of this length. My first change sent it over the maximum length for a SPROC. Then it occurred to me to redo it in parts, and it ended up as 4 or 5 separate procedures. I can't be sure -- that was 5 years ago.
The problem could be bigger than just a stored procedure. In my case the orginal author had incrementally created a rules engine of sorts, implementing too much business logic in the SPROC.
First one should understand what this one is trying to do, then break it out into smaller, more focused pieces. On the way, check to see that the tables are normalized sufficiently for the application.
You might even need a DB Architect for this one.

Re: The Query of Despair

2011-10-25 12:21 • by Bertie Fraser (unregistered)
364461 in reply to 364212
The answer too all computer questions and problems in the 1980's was "42"

The Queering of Despair

2011-10-25 12:36 • by trtrwtf (unregistered)
Why did you have to delete my man-on-man rape story?! Do you know how long that took to write?!

I was feeling inspired, too... *kicks pebble*

Re: The Query of Despair

2011-10-25 12:59 • by Peter (unregistered)
364479 in reply to 364318
pjt33:
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 don't know about Ben's case, but in the one that I did, it replaced an Access routine that used to do what you're describing, although that report took about 7-8 hours to run. As crazy as that place was, the place I went to next has had 2 entries show up in this site (one of them), and at least one case that hit the US Supreme Court.

Re: The Query of Despair

2011-10-25 15:02 • by tom (unregistered)
364513 in reply to 364200
Ross:
Nuke the site from orbit. It's the only way to be sure.

Thanks for the laugh :)

Re: The Query of Despair

2011-10-25 15:21 • by Matt Westwood
364526 in reply to 364461
Bertie Fraser:
The answer too all computer questions and problems in the 1980's was "42"


Still is bloody 42 among those with no imagination.

Re: The Query of Despair

2011-10-25 15:52 • by Don L (unregistered)
It looks like DigiNotar's security plan.
Since no managers understood it, they specifically told their employees to ignore security....

Re: The Queering of Despair

2011-10-25 16:37 • by Ur zuney shadow (unregistered)
364551 in reply to 364467
Zunesis disguised as trtrwtf:
Why did you have to delete my man-on-man rape story?! Do you know how long that took to write?!

I was feeling inspired, too... *kicks pebble*


You have Pebbles around and your best idea is kicking her?

Do you suffer from acratia?

CAPTCHA: ideo - CAPTCHA also has a male idea!

Re: The Query of Despair

2011-10-25 18:33 • by Jeff Grigg (unregistered)
I want the source!

Re: The Query of Despair

2011-10-25 18:59 • by Xythar (unregistered)
Only six pages? Pfft, amateurs.

Re: The Query of Despair

2011-10-25 19:28 • by RobIII
According to Chrome this query is 56 pages large.

I wish I had time to translate my 3-part blog about this some day; for now you'll have to do with Google-translate:

Part I
Part II
Part III

These blogentries are part of a larger concept, that's why they are actually part 5, 6 and 7.

Re: The Query of Despair

2011-10-25 20:52 • by Anonymouse (unregistered)
The real WTF: Pushpins in a whiteboard?!

Re: The Query of Despair

2011-10-25 21:54 • by Fernando (unregistered)
Almost as large as the 10K+ SQL text I found once (I know it was more then 10K because it overflowed a buffer that size) in a NYC city department.

Re: The Query of Despair

2011-10-25 23:58 • by skreidle (unregistered)
364588 in reply to 364159
While I agree with that statement when it comes to specific programming languages that are occasionally posted here, I don't know much any DB programming--but I can see that that is a hellspawn of a single database query. :)

Re: The Query of Despair

2011-10-26 05:26 • by SEMI-HYBRID code
"The good news is that i've isolated the problem to a single database query. The bad news is that the application doesn't consist of anything else."

Re: The Query of Despair

2011-10-26 05:28 • by sxpert (unregistered)
364603 in reply to 364159
the mere SIZE of the bloody SQL request is enough, I'd say... 5 pages goddamit !

Re: The Query of Despair

2011-10-26 05:28 • by SEMI-HYBRID code
364604 in reply to 364574
RobIII:
According to Chrome this query is 56 pages large.


that was kind of disappointing, i hoped that at least half of it would be the WHERE part, would be much more fun

Re: The Query of Despair

2011-10-26 06:47 • by H. Walter (unregistered)
I think the real WTF is the fact that the query was printed out on paper.

As stated in the text it is coming from a legacy system. This mostly implies having a little complexer data model than the usual modern Web 2.0 CRUD model on 4 tables (item, item_comment, item_attachment and user).

Re: The Query of Despair

2011-10-26 10:31 • by L. (unregistered)
364673 in reply to 364574
RobIII:
According to Chrome this query is 56 pages large.
.


I think it's time to consider our last line of defense .. let chuck norris roundhouse kick the guy who wrote that datamodel ...

Re: The Query of Despair

2011-10-26 10:34 • by L. (unregistered)
364676 in reply to 364612
H. Walter:
I think the real WTF is the fact that the query was printed out on paper.

As stated in the text it is coming from a legacy system. This mostly implies having a little complexer data model than the usual modern Web 2.0 CRUD model on 4 tables (item, item_comment, item_attachment and user).


lol .?

Obviously the dm sucks as most datamodels used in about anything ever written . but saying older is better ?? man there's a lot of software that was written without even knowing about SQL, FK, triggers and all ...

The older the applications, the more chances of major wtf, no relational integrity, major information duplication and overall corrupted corruptable shit quality data ...

Web 2.0 my s, databases aren't meant as toys for webdevs anyway and people using the kind of DM you talk about are definitely not to be left close to a database.

Re: The Query of Despair

2011-10-26 11:28 • by Kwit (unregistered)
Looks like typical SQL query generated by SharePoint. Lenght of this query depends on amount of data in document library. Microsoft Support say that this is perfectly normal and recommends to remove some data from library.

Re: The Query of Despair

2011-10-26 11:31 • by Dave C. (unregistered)
My personal record is 600 lines. Since I know my limitations, I wrote a perl script to help. Otherwise, I'd probably still be debugging it.

Re: The Query of Despair

2011-10-26 11:57 • by Gregory (unregistered)
Probably this query is the result of some JOINS and subqueries between a few number of tables each one containing dozens of columns.

Captcha: inhibeo

Re: The Query of Despair

2011-10-27 09:47 • by davey (unregistered)
364810 in reply to 364163
Because then it would read

SELECT 1=1

and that's no fun at all.

Re: The Query of Despair

2011-10-29 08:53 • by Kenneth (unregistered)
Well, does if perform well ;0)

Re: The Query of Despair

2011-10-31 06:43 • by abdullah (unregistered)
I remember one time at a social function chatting with a snooty DBA. He showed contempt when I told him I prefer to do 'JOINs by code'. I know it's a bit lazy, and I do use simple joins these days, but I know why I do it.

I should print out this article and keep it handy next time I need to defend my position.

Re: The Query of Despair

2011-10-31 10:35 • by Rootbeer
I had to squint at the image to determine whether it was the same six-page SQL query that was taped to a whiteboard in a former workplace of mine.

No, it's not, and it's even worse. At least that one consisted mainly of a matrix of WHERE condition permutations, OR'ed together, such that the entire query only had to be evaulated in the most pessimal case.

Re: The Query of Despair

2011-10-31 14:38 • by DG (unregistered)
365167 in reply to 364193
D:
There is no hope. The best you can do is put the beast down. Burn the document, wipe the drives, torch the building... Oh, and flush the site with holy water just to be on the safe side.


You should nuke the site from orbit, its the only way to be sure.

Re: The Query of Despair

2011-11-01 18:00 • by scav (unregistered)
I don't often say this, but Holy Cunting Fuck.

Re: The Query of Despair

2011-11-01 21:11 • by Jonathan Wilson (unregistered)
After seeing that monstrosity, I wonder if there needs to be a new entry in Guinness World Records for the "worlds longest SQL query"

Although I bet there are probably longer monstrosities generated by "frameworks" like hibernate (the abuse of such frameworks seems like a WTF in and of itself IMO)

Re: The Query of Despair

2011-11-02 07:19 • by lulzSQL (unregistered)
365404 in reply to 364199
Yes. And I have had to do that already. Massive views or fact tables for analytics. You have it.

Re: The Query of Despair

2011-11-09 09:22 • by Pablo (unregistered)
The wine is finished will you testing...
"Spanish expression"

Se te va el vino en cata

« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5Next »

Add Comment