One company I was at did a merger with another startup, and most of the other company's engineers quit. Amongst the piles of Visual Basic we found a stored procedure that was about 5 pages long. It took about 18 hours to run; its job was to do a daily report.<p>I hate being afraid of code. I spent a day with it, got to understand it, then rewrote it as a couple of queries and some Java code, whereupon it took about five minutes to run.<p>[... then there was the guy who implemented bitwise AND and OR by precomputing some 65536 entry tables. Wow. Why do I find all the really howling bad stuff so close to databases?]
Just so folks know, there are tools that will decompose queries and make nice little pictures out of them. With something like this, you'd have to use it just to get started.<p>Once you've visually decomposed it, you'd physically decompose it by splitting it inside-out. Then proceed to understand and debug inside-outwards.<p>Not fun, but not impossible. Just a huge pain in the ass. Making it more fun would be a database with bad RI, nulls, and duplicate data all over the place. Don't get me wrong -- from looking at the image it definitely looks like aspirin will be required. :)
I'm currently reading "Mastering Relational Database Querying and Analysis" by John Carlis, which posits that SQL is inherently flawed for several reasons. To paraphrase from the text:<p>First, both the syntax and the way querying is generally presented in textbooks, lead you to think that your task when querying is to <i>display one unnamed table</i>. The author objects to each of those four words.<p>Second, many people have found querying with SQL terribly difficult. Even experts find SQL hard to create and read. Do not be surprised if an analyst struggles to understand his/her own SQL. It is impossible for users to understand any but the simplest SQL.<p>Third, SQL practice suffers from the notion of a "correlated query" -- which has a monolithic subquery that is executed repeatedly via looping, once for each value of a candidate row picked by an outside SELECT.<p>The book has much more to say on the topic of SQL before going on offer relational algebra (built on top of SQL) as a n alternative.
I once worked on a site where the original developer clearly didn't know joins existed, so if he wanted data from two related tables, he'd get all the required results from table one, then loop through them, one by one, querying table two for the corresponding record. Sometimes this went 3 or 4 tables deep, the site would take nearly a minute to load a table of products.
A lot of the problems I have seen with queries (other than DBA issues) is the conflict between application developers and report writers. A lot of databases are designed for transactions and resources are not often available to do a proper reporting database or at least summary data.<p>I have a very simple rule for myself - "if a user of the application is concerned about a certain attribute or state an element (e.g. person, truck, plane) is in, then a report will be required showing all elements with that attribute or state."<p>If your database design cannot support that rule, then trouble will happen and you will have serious performance problems.<p>To give a simple example, suppose you are running a group of storage garages. You have a table with all your customers, a table with all your storage units, an assoc table joining customer and units with active flag + date of start, and a table with all your payments. Good enough to do transactions and figure out for a unit if they are payed up.<p>On the other hand, writing the report to tell who hasn't paid is going to be kind of a pain. It is a simple example, but not much different from what you find in large systems.
When I've seen this article back in my RSS reader, it reminded me of one particular query that was generated in the application I'm maintaining. My irrational fear of sending too many queries to the database (I've outgrown that in the last 6 years) caused a single query to be generated which was 4KB in size.<p>Which of course is much less than the one in the picture, but still very, very bad.<p>Some so we've refactored the beast. Now it's 2-3 smaller queries (which are much easier to optimize for PostgreSQL and, above all, individually cacheable) which lead to a nearly 100% speedup for common cases. Also, the code is infinitely more readable which means that it's much easier to extend it.<p>I'm incredibly happy that we've seen the light and fixed it before it grew to proportions like the ones on the original article <i>shudder</i>
People in that thread are bragging about their 10-page queries with 20 joins or 8 unions.<p>I'm looking at a query here that is 37 printed pages, with 92 joins over 25 unions.
My first assignment in my first ever job working for a 'proper consultancy' was to babysit an overnight process which was a SQL Server stored procedure. Back in those days they had a 64k limit, so it was split into 3 or 4 sections. It took around 12 hours to run.<p>I'd like to say I rewrote it, but I didn't. I just left.
Reading the comments below, I get the impression that all the "good" DB people hang out on HN, not like those "other" incompetent nits out there who don't know what a join is. Hubris, people.
The original version of foursquare.com contained a lot of stuff like this (though not as epicly bad). It was a very small amount of poorly written PHP code surrounding a bunch of unreadable SQL statements. It's amazing that it worked at all.<p>Dens is a great guy, but I hope I never have to rewrite his code again.
I was just trying to figure out a stored procedure that queries one table, loops over the rows, and within the loop queries another table using the values from the first query. Now, looping over these rows, it has a third query and a corresponding loop over those rows.<p>And all that for inserting the values taken from the three tables into a 4th table. This could have been done with a simple 3-table join query. Hell, it could even have been done with a single insert statement! I wonder how people fail to recognize an N+1 selects problem when it's staring them in the face.<p>Well, to be fair, this problem I described isn't exactly an N+1 problem is it? More like an N(M(L+1)+1)+1 selects problem. ;-) (Unless I've got my math all wrong there?)<p>How I hate working with PL/SQL stored procedures! :(
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 (<a href="http://www.microgen.com/dbclarity/" rel="nofollow">http://www.microgen.com/dbclarity/</a>)? Have you been using something similar recently?<p>(disclaimer: I work for mcgn)
Reminds me of the stock update system for one of our major clients at my first job. The predecessor of myself and my colleague had thought it a brilliant idea to build a clothing ecommerce site, with a complete list of all stock going back to the year dot with ASP and Access (that's Classic ASP, not .NET). Towards the end the stock update would take pretty much an entire afternoon to run.<p>Eventually we got the approval to change to MySQL for the database. When they ran the first stock update with the new version they rang us up to check it had worked because it was near instantaneous.<p>The moral of the story: Access is BAD! VERY BAD!
I've seen and had to debug longer stored procedures for sure but never a single query. I can't see the last page so maybe this is a stored procedure. It's hard to tell.
I worked for a company where there were queries somewhat like this, however they were obscured because they would create views on the fly. So a query would look deceptively simple only to realize (not exaggerating here) there were four levels of views underneath it. Bugs were a pain, but the worst was trying to optimize those queries. Just untangling what the actual query was made life really difficult.
I've seen SQL that looked like this but didn't wind up being very complicated. I've also seen seemingly simple queries that were actually very tricky!<p>I can't read much of the query, but at least a few lines are checking for null values. I wouldn't be surprised if 80-90% of the query is simply output formatting. Depending on the DB platform, some formatting and null-check statements are fairly verbose.
I will say, Ingres was not my favorite database, but its query plan display should be used to explain how a database query works. It showed a tree of operations for each query. If you saw FSM (full sort merge) or Cartesian Product you better mean them or re-write the query.
I'm pleased that most of the commenters recognize that SQL has a need and is it's own language, for good and bad. I really expected to find a troll popping out "NoSQL rulez" type comments, and the level of understanding of how and where SQL can help is very encouraging.
Soooo... what is it supposed to do? Looks like a million sub-selects and joins! Already have a headache just with looking at it. I'd probably right it all over again from scratch.
A place I used to work at used an ORM which gradually constructed SQL throughout the flow of a request. One of the calls we generated was probably a couple of pages long.
htsql (www.htsql.org) is a business reporting language -- one line in htsql can generate 5 or 6 lines of SQL.<p>This query could be condensed considerably if rewritten in htsql.<p>(htsql automatically generates SQL code that covers all corner cases and executes faster than hand-crafted SQL.)
While we're sharing horror stories...<p>I once encountered a stored procedure that returned HTML in a result set. It literally created the UI of a webpage. It returned several columns of HTML that the app would place in strategic parts of the page. Well, as years went by, the app required a more innovative and web 2.0 UI. Rather than remove the HTML from the sproc, more columns were returned with more HTML, Javascript and the like. One time I had to fix some Javascript that rendered on the page. When I finally found where the errant JS was coming from, I realized I had to file a database change ticket to fix the UI :)
See, this kind of crap is what happens when you have the programmers sit on the bench and let the "engineers" take charge. Why do you hate society, you Luddites?!
Sorry, I call BS.<p>I might have just been lucky enough to always work at professional companies and startups where this kind of stuff can never happen.<p>But something tells me there is no reasonable way an SQL query can grow to these proportions.