12 ms·
"I've isolated the bug to a database query"
- hackermom 15y agoSee, 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?!
- acangiano 15y agoI'd love to see an EXPLAIN on that. ;)
- ironchef 15y ago* 1. row * id: 1 select_type: SIMPLE table: lots_of_em type: not_good possible_keys: none key: none key_len: n/a ref: NULL rows: googol filtered: 0 Extra: You're screwed, Do not pass go.
- stevejalim 15y agoThis is an old link, but you just reminded me of: http://howfuckedismydatabase.com http://howfuckedismydatabase.com
- mweibel 15y agohahaha great :D
- MattBearman 15y agoThat's awesome, especially this - http://browsertoolkit.com/fault-tolerance.png http://browsertoolkit.com/fault-tolerance.png
- jswinghammer 15y agoI'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.
- kabdib 15y agoOne 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. 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. [... 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?]
- jswinghammer 15y agoThe & and | operators weren't good enough for him? That stuff ends up in databases because on your average team the knowledge of databases drops dramatically when you get past selects and maybe left joins. I worked with a few developers who wrote and lived with queries that were taking 30 seconds each to run on their local machines because they couldn't imagine how to fix them and just waited for me to get assigned to the project in a week or two so I would fix it. They just kept raising the timeout values until the pages rendered. The problem was simple in that they were accessing a table with 30 million rows without using any index at all so changing it to use the clustered index made the query return almost instantly. I've seen similar things where people come up with crazy ways of doing your most basic database tasks.
- Roboprog 15y agoSo show your coworkers how to run the query-plan dumper for your DB brand. The curtain is torn away, and the underlying ISAM is revealed for all to see to allow the needed "hints", indices or restructuring to be understood. Don't lord it over them, explain.
- jswinghammer 15y agoThey were disinterested. Under normal conditions you are correct and most people would be interested but they were not.
- 15y ago
- dos1 15y agoWhile we're sharing horror stories... 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 :)
- spydum 15y agoSounds like Oracle's Portal product?
- thwarted 15y agoI had a boss one time who, rather than write a CGI, would write SQL statements against oracle to format the results as HTML. Things like select '<table>' from dual; select '<tr><td>'||column1||'</td><td>'||column2||'</td></tr>' from dual;
- jarek 15y agoAt least some versions of Sybase SQL Anywhere have a small HTTP server built in. Specially named/declared stored procs return HTML. I've built a smallish web app based on one, complete with Ajax touches. It was fun for one of my first programming jobs, but I don't know how maintainable it ended up. On one hand, at least your logic is close to your data... on the other hand, oh my god.
- MattBearman 15y agoI 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.
- puredanger 15y agoMmmm... nested loop joins. Little more code and he would have his own database. :)
- gibybo 15y agoI see this pretty often with people who haven't spent much time with relational databases. I suppose it's understandable since thinking in sets with SQL is a different paradigm than they are used to, but I even see tons of PHP/MySQL tutorials online that use this method when they should be using a join.
- MattBearman 15y agoI think would have been understandable if he'd grabbed all the required ids from table one and done some kind of where in() with those ids on table two. But the way he did it, if table one returned 100 rows, then he'd be making 101 queries to the database. Don't think I could ever understand that :S
- flomo 15y agoIn the old days, MySQL didn't support foreign key indices, so the LAMP developer community had this mentality that "Joins are slow" or even "Joins are evil" and actively encouraged people not to use them. So its not really surprising this kind of thing is still out there - PHP tutorials are somewhat infamous for promoting 'worst practices'.
- jaylevitt 15y agoYeah, I know a Rails developer who thinks four-way joins are a design smell.
- DanielBMarkham 15y agoJust 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. Once you've visually decomposed it, you'd physically decompose it by splitting it inside-out. Then proceed to understand and debug inside-outwards. 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. :)
- offbyone 15y agoI've always believed those tools must exist, but sorting out the SEO crap and advertising copy that gloms up search results for them is painful. Can you name any Linux/Mac tools like that?
- DanielBMarkham 15y agoNot linux, but I have a windows example. Being an El Cheapo, what I do from a windows box is fire up MS Access, link into the database (and this works even if the database is on a *nix box somewhere), then drag the text of the query into the graphical query builder tool. It's taken some pretty complex stuff apart for me in the past. It's been a while since I last deep-dove in a complex database, so I don't have any other examples handy. Sorry. Maybe somebody else can pick up the thread. I remember reviewing a bunch of them several years ago -- these tools have been around for a long time.
- myth_drannon 15y agoon PostgreSQL it's part of pgAdmin you don't need a separate tool
- shrike 15y agoI haven't found anything for Mac/Linux to do it but on Windows Visio can actually do a good job of visualizing a schema and SP structure.
- waqf 15y ago
- topbanana 15y agoMy 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. I'd like to say I rewrote it, but I didn't. I just left.
- LarryMade 15y agoPeople can write queries that large without formatting? or was that the result of some query generation application?
- snorkel 15y agoIt's got to be from a point-and-click Query-O-Matic tool.
- josephcooney 15y agoOr an ORM.
- jtheory 15y agoI'll wager it's generated within the code, based on report parameters or something similar. That does look like rough going... though in my experience, a nasty-looking mess like that may not be so bad if you simply format it -- with indenting to indicate subquery levels, and maybe some color (like light grey for formatting/null check fluff and bold for SQL keywords). Often a massive query like that indicates business logic and even display details built into the query -- are there lots of WHEN clauses? Big chunks of the query just managing formatting the output? If SQL is your "hammer" to fix every problem, you can take a simple query -- say, fetching a user's full name and address from a single table -- and make it really damned long just by cramming everything into the query... is there an address2? A middle name? Did the user capitalize their names (you could fix that in SQL...)? Etc.. I have written some very long SQL before -- a few years ago I flattened many many pages of buggy PHP into a dozen carefully-designed (but a bit complex) materialized views that were the base for the final reporting queries (which could be pretty straightforward; the data involved was already neat & tidy). If you blew up each view involved and removed all formatting, the reporting queries would be pretty tough to digest; that's the point, though -- all of the complexity was cut up & compartmentalized into well-named bite-sized pieces, so other developers were still able to maintain and extend it after I left.
- bni 15y agoIm sure 12 pages of Java code doing this procedurally instead is much more maintainable.
- emehrkay 15y agoThis is kinda impressive.
- yuvadam 15y agoSorry, I call BS. I might have just been lucky enough to always work at professional companies and startups where this kind of stuff can never happen. But something tells me there is no reasonable way an SQL query can grow to these proportions.
- ysangkok 15y agoWhy not? It's probably computer-generated. You can actually see the first page, and if you look at it, it uses nested SELECT statements. No one said it was smart, but it certainly is reasonable.
- marshray 15y agoAutomated query builders.
- ianterrell 15y agoAll the good techniques and technologies you use now? Yeah, those were invented because the way a lot of people used to do things was terrible. I'm not old enough to have built systems like that, but I have inherited some for maintenance and debugging. That's not likely to be BS. Back in the day "do it in a stored procedure" was common advice. All the logic for untold applications and millions of lines of code lived at the database level. Count yourself lucky perhaps to have entered the market when you did, but don't discount that you're standing on tall, sometimes ugly, shoulders.
- tallanvor 15y agoSystems like that don't even have to be particularly old. I used to work on a system that started out as an Access database, and rather than doing things properly, the developers basically just translated everything into SQL statements and used ASP and COM+ on top of it. It was a load of crap, but it still ran about 10x faster than what customers were used to, so they weren't complaining. Last I knew they were still running the same basic setup, and it's been 5 years since I worked there.
- jakejake 15y agoIt's a good point. At one time the database was the application. Stored procedures and such were part of the application UI (as it were). At one time features were being added to database systems you could build command-line interfaces for the end-users. These days the database is mostly just used as a storage engine. Not that I really care about "back in the day!" But it can be helpful to understand why certain things were done in a certain way. It seems like ancient history, but there you definitely will find some old code when you start working at companies that have been around for more than a few years.
- pilif 15y agoWhen 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. Which of course is much less than the one in the picture, but still very, very bad. 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. 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 shudder
- JBiserkov 15y ago4KB? That's nothing. Search for 'media.sql' in any recent Adobe installation media. You'll find 3 MB+ SQL files, containing: -(BASE64?) encoded InstallerIcon, -(BASE64?) encoded EULAs in various languages -GUIDs like {01C3BD72-7371-4472-B179-B4DFE6DDD251} -and my personal favorite: a 25 KB XML fragment
- gecko 15y agoThere was a source control system whose code I had the pleasure of reading several months ago whose favorite way to store data was as a SQLite3 database, with a single table, with a single column, with a single row, containing JSON. Words failed me. Based on what you're describing, I now believe those developers were poached from Adobe.
- flomo 15y agoI can see the value of sticking that kind of stuff into a SQLite database rather than some obscure structured resource format.
- semanticist 15y agoIt's common to use SQLite for data storage in Mac OS X, but 3MB SQLite databases aren't the same thing as a 4KB database query, which is the horror the original comment was admitting to. :o)
- mgl 15y agoReally 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/ http://www.microgen.com/dbclarity/)? Have you been using something similar recently? (disclaimer: I work for mcgn)
- mwexler 15y agoI'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.
- jcromartie 15y agoPeople in that thread are bragging about their 10-page queries with 20 joins or 8 unions. I'm looking at a query here that is 37 printed pages, with 92 joins over 25 unions.
- ceejayoz 15y agoAnd thus Skynet was born.
- einhverfr 15y agoI think at this point I am bragging that I have never written one. Ok, I have used views of views, but....... I have, however, had the misfortune of troubleshooting those 10 page queries. Finding a stupid typo in one of those is like looking for a needle in a haystack.....
- philjackson 15y agoA 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.
- protomyth 15y agoI 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.
- bmf 15y agoI'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: First, both the syntax and the way querying is generally presented in textbooks, lead you to think that your task when querying is to display one unnamed table. The author objects to each of those four words. 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. 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. 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.
- samuel 15y agoI disagree with the readability part. I do heavy use of "WITH" to name my intermediate steps and comment the tricky parts (as I would do with any other programming language) and my colleagues find them pretty readable(or that's what I'm told). That's how I "reverse engineer" such monster queries, refactoring them in intermediate relations with names. Pretty often the same subselect is used more than once. In fact, due the lack of side effects it's much easier to do than with procedural code.
- niccl 15y agoDoh! I must be one of the SQL bunnies everyone else is superior to... I didn't know about the WITH statement. Thanks for enlightening me.
- deleted 15y ago[deleted]
- einhverfr 15y agoI disagree with those criticisms of SQL, but I think it is inherently flawed from another perspective. Obviously how textbooks portray SQL has nothing to do with whether it is inherently flawed. The real issues with SQL come down to the fact that it is an imperfect representation of relational math, and then also the dreaded ambiguity regarding NULLs. The problem with NULLs is that NULL is used to refer to two very distinct conditions. In one case (outer joins) they are used to refer to a NULL set. In another, unknown values. These really should be distinct values.
- pork 15y agoReading 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.
- protomyth 15y agoA 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. 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." If your database design cannot support that rule, then trouble will happen and you will have serious performance problems. 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. 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.
- salvadors 15y agoIs your point here that the data being stored is insufficient (e.g. you'd want an end date, not just an active flag; this doesn't cope at all with prices changing over time, bulk discounts, or different customers paying different rates; there's no concept of invoices, or whether payment is due based on calendar months or based on opening date; etc) or that you're ignoring all that sort of stuff just to keep the example simple (so assume everyone pays a fixed rate per unit, due weekly; someone can't close their account until they're paid up; etc) but that you'd still want a more complex schema so as to be able to more easily generate a "Who owes us money?" report? If it's the former, then sure: you need to be able to model all these things properly. If it's the latter, then I'm not so sure. The SQL to create that sort of report is going to be non-trivial, but it shouldn't be overly complex for someone who knows what they're doing, and if you have the correct indexes it shouldn't take very long to run either. If you want to start doing all sorts of fancy data warehouse slicing and dicing, you're usually better extracting daily (or more/less frequent depending on needs) dumps of your transactional database into a different structure more suitable for reporting, than in restructuring your 'live' database and having to deal with all the resulting denormalisation issues, etc.
- OiNutter 15y agoReminds 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. 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. The moral of the story: Access is BAD! VERY BAD!
- bialecki 15y agoI 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.
- jakejake 15y agoI'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! 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.
- atsaloli 15y agohtsql (www.htsql.org) is a business reporting language -- one line in htsql can generate 5 or 6 lines of SQL. This query could be condensed considerably if rewritten in htsql. (htsql automatically generates SQL code that covers all corner cases and executes faster than hand-crafted SQL.)
- deleted 15y ago[deleted]
- kleiba 15y agoPlease forgive me, this is OT: can anyone here recommend a good online resource for learning SQL "the hard way"?
- harryh 15y agoThe 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. Dens is a great guy, but I hope I never have to rewrite his code again.
- nithinbekal 15y agoI 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. 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. 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?) How I hate working with PL/SQL stored procedures! :(
- jpadilla_ 15y agoSoooo... 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.