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-11-09 09:49 • by programer001 (unregistered)
select * from death

Re: The Query of Despair

2011-11-09 09:55 • by brokentone (unregistered)
366189 in reply to 364165
Yes, placing the pages inside the same barn would be really helpful!

Re: The Query of Despair

2011-11-09 09:59 • by Just guessing... (unregistered)
Something to do with fraud detection, maybe? We have a query about two pages long that chews recursively through member accounts and activities doing multiple deep joins and counts to find webs of collusion / fraudulent activity, and assign scores to each potential network.

Re: The Query of Despair

2011-11-09 10:01 • by Look upon me and despair (unregistered)
366194 in reply to 364178
C-Octothorpe:
frits:
C-Octothorpe:
frits:
Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
What, nausea?
I think the colloquial term is "douche chills".
I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
Wallow in it: youtube.com/watch?v=CE8XKeN0zk4 - Kickboxer Jean Claude Van Damme Dance [HD]

Re: The Query of Despair

2011-11-09 10:52 • by Aleksey Tsalolikhin (unregistered)
Use htsql to collapse the query. htsql is a 5th generation language, an abstraction on top of SQL. http://htsql.org

Re: The Query of Despair

2011-11-09 11:09 • by jcromartie (unregistered)
366224 in reply to 364190
Pfft... right here in front of me sits a query with 25 unions in it. It is ~1800 lines long, making it 37 printed pages in Calibri 10.

Beat that.

Re: The Query of Despair

2011-11-09 11:12 • by jcromartie (unregistered)
366226 in reply to 364299
I'm looking at my monster query here: 92 joins over 25 unions.

Re: The Query of Despair

2011-11-09 11:19 • by mgl (unregistered)
Really nasty piece of SQL code, definitely not for human-based processing. What do you think about tools that may decipher and visualize such complex queries in a more structured way, like DBClarity (http://www.microgen.com/dbclarity/)? Have you been using something similar recently?

Re: The Query of Despair

2011-11-09 11:54 • by Bah (unregistered)
Edit with TABs and LFs... half work done.

Re: The Query of Despair

2011-11-09 12:26 • by Shet (unregistered)
366252 in reply to 364158
you're probably right in that no human wrote that

Re: The Query of Despair

2011-11-09 17:06 • by artuc (unregistered)
What was he coding: the world?

Re: The Query of Despair

2011-11-09 17:29 • by Eddy (unregistered)
This must be a generated Query from MS Access.

Re: The Query of Despair

2011-11-09 21:12 • by Larry Sheldon (unregistered)
I knew a COBOL programmer who wrote a major application program that used DMS1100 and which had one period after the period at the end of the PROCEDURE DIVISION. header.

No, unfortunately I don't have a copy of the code-list.

Re: The Query of Despair

2011-11-09 21:45 • by The His (unregistered)
the his

Re: The Query of Despair

2011-11-10 01:13 • by BarofFoo (unregistered)
366322 in reply to 364255
6 pages with lots of formatting and whitespace is ok.

6 pages with no line breaks, formatting or whitespace is blurgh.

Re: The Query of Despair

2011-11-10 04:50 • by AJ (unregistered)
Probably took longer to run than debug

Re: The Query of Despair

2011-11-10 09:37 • by Abraham Crego (unregistered)
Try with DISTINCT or change all for a "HELLO WORLD" (as many Microsoft good solutions)

Re: The Query of Despair

2011-11-10 14:09 • by select name from users where (unregistered)
366439 in reply to 364209
Oh. Dear. FAIL.

You wrote a query to extract a ton of data, most of which you weren't interested in, and sent it over the wire (slow), in order to run your own unproven search algorithm against it.

You have in one moment;

1. Removed the ability of the solution to scale. The amount of data sent across the wire will determine the fastest speed of the query - and as that base set of 'relevant data' likely grows as a multiple of the desired result set, that query is likely slowing down.

2. Removed the ability of the solution to scale. If the data exceeds your available RAM allocation, you're now writing to disk anyway. A disk on a web app platform that is probably already busy enough. Even slower.

3. Removed the ability of the solution to scale. That 'relevant data' set is being sent over the wire every time the query is run. A web app you say? I hope you won't be surprised when your user base grow and your network grinds to a halt. Really slow.

4. Removed the ability of the solution to scale. A good SQL engine optimises it's query execution plan according to the underlying data. It will use indexes when it makes sense, cache results when it makes sense, rewrite sub-queries into joins when it makes sense. As the underlying dataset changes, so does the optimisation. You're now at the mercy of your algorithm, your memory allocation system, your network bandwidth.

I suggest you read the "Art of SQL" and get a clue. There were obviously some things wrong with the system before you touched it (eg schema flaws, or sql engine configurations, either of which could prevent optimisation), but you didn't fix them. You applied a band aid that will fail.

Some people are best not allowed to do anything with a relational database.

Re: The Query of Despair

2011-11-11 09:23 • by daniel (unregistered)
366506 in reply to 364176
All the fields from every joined table are written out in the SELECTs. Temporarily simplifying that to check out the WHERE joins or qualifiers would be useful in debugging.

Re: The Query of Despair

2011-11-12 11:21 • by Kavius (unregistered)
366585 in reply to 366439
select name from users where:
You wrote a query to extract a ton of data, most of which you weren't interested in, and sent it over the wire (slow), in order to run your own unproven search algorithm against it.


I couldn't agree more. I was debugging an app once (it was taking 8 hours to pull up a weeks payroll, and 50% of the time it failed) and isolated the problem down to a query. The query looked something like this printout.

When I requested time to study the query, I was told the query could not be the problem because the guy that wrote it had been a genius. As I studied the query, I realized the problem was that his "hand crafted" query, had been pulling all the records from the database, running them through a Java filter, and writing them back to the database. I rewrote the query in my spare time (with a where clause to only look at the current pay period), it was a fraction of the size, formatted to be readable: it took less than 2 seconds to run. I quit the next day with the statement "filter early, filter often".

When I found out the original author was working for my prior employer, I phoned them and recommended immediate termination.

Re: The Query of Despair

2011-11-24 12:08 • by Dwain A Wuerfel (unregistered)
If he is able to find the line of code causing the error I would have to say he has a unique skill that is very marketable.

Re: The Query of Despair

2011-12-05 15:32 • by whar (unregistered)
368657 in reply to 364173
Liz, is that you?

Re: The Query of Despair

2011-12-22 13:57 • by JRaymond (unregistered)
Last place I worked had a 76 page long stored procedure.... added one entry to the database. Not computer generated, but rather, shoddy offshore contractor generated.

Re: The Query of Despair

2012-02-03 05:49 • by Josiah (unregistered)
That is amazing. I am sure we will never know how that came to be.

Re: The Query of Despair

2012-07-05 10:27 • by Chris s. (unregistered)
384364 in reply to 364159
That's an SQL query, which should be declarative - in other words describe what you want to get out of the system. They are usually short. This query seems to go over 10 foolscap pages. In short, it's a nightmare - so bad that I wonder how the query engine even *parsed* it, let alone ran it...

Re: The Query of Despair

2012-12-15 19:59 • by cy (unregistered)
Well at least the pages are numbered. I rarely use abbreviations but for this I make the exception. OMG

Re: The Query of Despair

2013-07-08 08:21 • by Cynep (unregistered)
It is fun way to demonstate to outsiders how tough your work is.

As employer who debug such queries almost every day I can say only "Deal with it!"
« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5Next »

Add Comment