Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

"I've isolated the bug to a database query"

thedailywtf.com

Post reply on HN
Print view View original

31–40 of 173 posts

Re: "I've isolated the bug to a database query"

#31

Post by pilif »

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.

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

Re: "I've isolated the bug to a database query"

#32

Post by marshray »

yuvadam wrote: post #29

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

Automated query builders.

Re: "I've isolated the bug to a database query"

#33

Post by ianterrell »

yuvadam wrote: post #29

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

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

Re: "I've isolated the bug to a database query"

#34

Post by gibybo »

MattBearman wrote: post #11

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.

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

Re: "I've isolated the bug to a database query"

#35

Post by DanielBMarkham »

offbyone wrote: post #26
DanielBMarkham wrote: post #16

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

I'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?

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

Re: "I've isolated the bug to a database query"

#36

Post by timtadh »

yuvadam wrote: post #29

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

I worked at a company that had a ton of business logic in stored procedures. Yes, queries can get ridiculously long. All of their queries were written by hand.

Re: "I've isolated the bug to a database query"

#37

Post by MattBearman »

gibybo wrote: post #34
MattBearman wrote: post #11

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.

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

I 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

Re: "I've isolated the bug to a database query"

#38

Post by mweibel »

stevejalim wrote: post #24
ironchef wrote: post #5

Earlier quoted context omitted.

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

This is an old link, but you just reminded me of: http://howfuckedismydatabase.com

hahaha great :D

Re: "I've isolated the bug to a database query"

#39

Post by ngsayjoe »

jswinghammer wrote: post #7
kabdib wrote: post #6

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

The & 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 t…

I tried caching the hell out of Rails, kept adding indexes to my SQL tables, created 4 MySQL replicas, added more RAM to my linodes, tuned many default parameters in my.cnf, but the website was still slow to a crawl.

This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.

Re: "I've isolated the bug to a database query"

#40

Post by tom_b »

yuvadam wrote: post #29

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

I was once handed a database with four SQL procedures in it. Each procedure was 6+ pages of run-on PL/SQL. The picture in the original article is frighteningly close to what I had in hand at that point. Mostly nested cursors and imperative style programming munged into the proc.

I've seen other cases, none quite as bad as that one. It tends to be the result of giving a developer who hasn't had any formal training or experience with SQL a requirements doc without any supervision. If all you have ever done is normal imperative coding, you will not even know you are doing it wrong.

You can also find some weird SQL created by report generator tools.

Post reply on HN