"I've isolated the bug to a database query" • Hacker News Board

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

#111

Post by mechanical_fish » Wed, Nov 09, 2011, 8:26 PM UTC

lyudmil wrote: post #82

tom_b wrote: post #19

Earlier quoted context omitted.

"Why do I find all the really howling bad stuff so close to the databases?" Because the database and queries against were were coded as an afterthought. Because the programmers ran all the db access tests against tables with 4 rows of data when the customers would windup with 100 million rows in production. Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much ti…

I have a slightly stupid question. How is it that we've come to a point where we think of application and database development as separate things? I think this sets the stage for a lot of the problems you're getting at. On the one hand we make-believe that all you need to know is how to sling code and as long as you can somehow get your data into the database and out, then you've done your job as a developer. I think…

How is it that we've come to a point where we think of application and database development as separate things?

My take is that the principles of database engineering only make intuitive sense at scale.

If every programmer started their career as an assistant DBA at a web host with a lot of traffic, or a data warehouse with a ton of transactions and report generation going on, or a company where lots of different groups were developing client code against the central database, every programmer would be inspired to understand databases.

As it happens, programmers start their career building little apps with one client at a time and a handful of data. And many of them finish their career building such apps, because they are very handy. And these programmers may never feel a burning need to know how databases perform with large datasets, or a huge stream of colliding transactions, or a mission-critical data integrity and safety requirement, or a variety of clients all trying to generate complex reports.

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

#112

Post by funkah » Wed, Nov 09, 2011, 8:34 PM UTC

pork wrote: post #59

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.

Maybe HNers are just better at keeping their mouths shut when they don't know anything about a topic.

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

#113

Post by niccl » Wed, Nov 09, 2011, 8:44 PM UTC

samuel wrote: post #62

bmf wrote: post #56

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

I 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, du…

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

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

#114

Post by dmk23 » Wed, Nov 09, 2011, 8:49 PM UTC

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 use the cheapest and the best tool available: EXPLAIN EXTENDED

You cannot do any better than have the DB tell you exactly what it is going to do with your query. Then you can experiment with changing / adding / removing clauses and see how it would affect the query plan produced by EXPLAIN.

For example if EXPLAIN says the query would generate a temp table you could often achieve improvement by managing the same temp tables explicitly. Many times you can get a huge performance lift by using a "group by index". You could identify and rewrite un-indexed table scans too.

I've found lots of other "unobvious" optimizations that cut down queries that ran for days or hours to minutes or seconds.

Here are a few references to get started-

1) http://dev.mysql.com/doc/refman/5.5/en/execution-plan-inform...

2) http://www.mysqlperformanceblog.com/2006/07/24/extended-expl...

3) http://www.mysqlperformanceblog.com/2010/06/15/explain-exten...

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

#115

Post by Roboprog » Wed, Nov 09, 2011, 8:51 PM UTC

scott_s wrote: post #14

Roboprog wrote: post #9

Earlier quoted context omitted.

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

You're assuming the other people want to learn. Generally, if people want to learn something, they ask questions. Telling co-workers how to do something when they don't ask can be... tricky. It depends on the environment you're in, your relationship with the people, and the people themselves.

Fair enough. You can lead an idiot to knowledge, but you can't make him think.

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

#116

Post by fendale » Wed, Nov 09, 2011, 8:57 PM UTC

miahi wrote: post #88

msluyter wrote: post #68

Earlier quoted context omitted.

I work in a very heavy database environment, and it's interesting. Nobody here ever talks about "left" vs. "right" joins. We also don't use ansi syntax (oracle), so for a typical outer join, we'll do either: select * from foo f, bar b where f.baz = b.baz (+) or select * from foo f, bar b where f.baz (+) = b.baz But we don't call one a "left" and the other a "right" join. Just bringing this up to note that the right v…

I also find the Oracle outer join operator (+) easier to use. I just searched the full (Oracle) sql codebase of the project I'm working on, and it seems that there is no clear winner in the "outer join" vs "(+)" battle (outer = 46%, (+) = 54%). There is no code style requirement for joins.

I think I read recently that using the Oracle (+) operator can give you the error "cannot outer join to more than one table" if you try to outer join to two or more, but the ANSI syntax will allow you to do that - I really should test this out ...

edit - I tried this out and right now cannot get that error, so not sure where I got that idea from.

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

#117

Post by jswinghammer » Wed, Nov 09, 2011, 8:57 PM UTC

FuzzyDunlop wrote: post #110

jswinghammer wrote: post #7

Earlier quoted context omitted.

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've had more than just a taste of this myself recently. I'm no database expert but I know how to build a query, normalisation, relational integrity and all that. Enough to identify bad SQL in a CRUD application. And I'll take the time to learn more about it and how to better construct a schema and query (and whether or not an RDBMS is even appropriate for the application). With this in mind my boss mentioned one of…

Is your name a wire reference? If so well done.

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

#118

Post by jswinghammer » Wed, Nov 09, 2011, 8:58 PM UTC

ceejayoz wrote: post #109

ngsayjoe wrote: post #84

Earlier quoted context omitted.

About 5 million pageviews / month. The website is quite processing intensive.

That's only two pageviews a second, on average.

We can agree that traffic rarely comes in at an average rate.

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

#119

Post by reubenyeah » Wed, Nov 09, 2011, 9:11 PM UTC

endersshadow wrote: post #74

msluyter wrote: post #68

Earlier quoted context omitted.

I work in a very heavy database environment, and it's interesting. Nobody here ever talks about "left" vs. "right" joins. We also don't use ansi syntax (oracle), so for a typical outer join, we'll do either: select * from foo f, bar b where f.baz = b.baz (+) or select * from foo f, bar b where f.baz (+) = b.baz But we don't call one a "left" and the other a "right" join. Just bringing this up to note that the right v…

I never ask for an explicit left or outer join in an interview. It's always a question, like, "Get me the total sales of every salesperson for this quarter given this schema." And then they return it, and I say, "Can I get the list of all salespeople, even if they haven't had any sales?" And that's where it falls apart, sadly.

Really? But how did they avoid learning something so basic?

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

#120

Post by CWuestefeld » Wed, Nov 09, 2011, 9:12 PM UTC

msluyter wrote: post #68

endersshadow wrote: post #28

Earlier quoted context omitted.

I work with large databases every day. I cannot tell you the amount of people I've interviewed that weren't able to solve a left join question. Let's leave right and full outer joins out of the discussion--a left join is as advanced as I dare get off the bat in an interview. I've once had a guy I worked with ask for help when he was with a new company. I told him to look at the query plan. He replied, "Yeah, but that…

I work in a very heavy database environment, and it's interesting. Nobody here ever talks about "left" vs. "right" joins. We also don't use ansi syntax (oracle), so for a typical outer join, we'll do either: select * from foo f, bar b where f.baz = b.baz (+) or select * from foo f, bar b where f.baz (+) = b.baz But we don't call one a "left" and the other a "right" join. Just bringing this up to note that the right v…

This kind of notation (a comma-separated list of tables) used to be the standard. We've moved beyond it for a very good reason: there's a very common class of queries that this notation cannot express.

It's very common to want to LEFT JOIN two tables, and then see specifically which ones on the left had no corresponding record on the right. But the old way cannot specify the JOIN criteria separately from the filter criteria, so we can't express the query.

For example, in my current database, I might do the following to see what categories are not represented by any products:

  SELECT *
    FROM Products.Category AS c 
    LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId
    WHERE p.ProductId IS NULL

By specifying the JOIN criteria in the ON, I can then use the WHERE to winnow down to the left-only rows. But if that were expressed in the old notation, it would be mistaken for JOIN criteria, and thus all categories would appear to have no products.