Comment On The Query of Despair

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

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.

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.

Time for map-reduce?

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

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.

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?

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

SELECT ( SELECT.

Nested queries start like that.

I know who wrote it: Chuck Norris.

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.

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

;)

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.

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

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

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.

These queries are very common in ORM based architecture.

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

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.

The answer too all computer questions and problems in the 1980's was "42"

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*

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.

Ross:

Nuke the site from orbit. It's the only way to be sure.

Thanks for the laugh :)

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.

It looks like DigiNotar's security plan.
Since no managers understood it, they specifically told their employees to ignore security....

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!

I want the source!

Only six pages? Pfft, amateurs.

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.

The real WTF: Pushpins in a whiteboard?!

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.

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

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

the mere SIZE of the bloody SQL request is enough, I'd say... 5 pages goddamit !

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

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

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

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.

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.

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.

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

Because then it would read

SELECT 1=1

and that's no fun at all.

Well, does if perform well ;0)

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.

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.

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.

I don't often say this, but Holy Cunting Fuck.

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)

Yes. And I have had to do that already. Massive views or fact tables for analytics. You have it.

The wine is finished will you testing...
"Spanish expression"

Se te va el vino en cata