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

71–80 of 173 posts

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

#71

Post by endersshadow »

sesqu wrote: post #66
bhousel wrote: post #42

Earlier quoted context omitted.

There's no reason ever to use a right join. I consider those to be a code smell.

There's about as much reason to use a right join as there is to use a left join.

I don't think I've ever used a right join in a production query (perhaps a couple of times when I was doing some digging and it was easier), but I don't understand your statement. Left joins are insanely useful. For example, getting all the sales for a salesperson:

    select
        sp.Name,
        sum(s.Amount) as TotalSales
    from
        SalesPerson sp
        left join Sales s on
            sp.SalesPersonKey = s.SalesPersonKey
    group by
        sp.Name
    order by
        TotalSales desc
If you did a correlated subquery, it would take a ton of time to complete (`select sum(Amount) from Sales s where s.SalesPersonKey = sp.SalesPersonKey`), especially on large tables.

Left joins (and full outer joins) are plenty useful and I use them almost daily. Care to explain what you mean?

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

#72

Post by kabdib »

_delirium wrote: post #65
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…

Isn't rewriting a stored procedure into multiple queries plus Java code sort of the opposite of what most DB best-practices advocate? Now your query logic is in a mixture of SQL and Java, rather than all in SQL, and you've hidden some of the logic from the database's query planner by moving it into application code. (On the other hand, I've rarely found query planners, even for Oracle, to be as magical at optimizing…

Sure, but I took an unmaintainable stored procedure (with very few comments, by the way) and turned it into something that was small, well-documented, and that ran over two orders of magnitude faster.

If this is wrong and goes against "DB best practices" then I guess this answers my question about where the howlers come from.

[We had a project under development. At some point, and an utter surprise to the team, an Oracle consultant showed up one day and said, "I'm here to tune your database."

"Huh?"

And he did. Against a half-built system with a schema we were still designing, he "optimized" our queries and determined buffers sizes and whatnot, and then he went away.

Apparently the people in Sales were panicked that we had a database under development that we hadn't yet made plans to tune, so they had hired a guy to come in and fix things.

We tossed all of his scripts and rolled back everything he'd broken, and continued coding. Total waste of money.

Databases are a weird confluence of the power-mad, the knuckle-draggers, the money-grubbers, and a few techies who know what the heck they're doing.]

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

#73

Post by [deleted] »

matwood wrote: post #57
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've never understood the typical programmers aversion to query writing. Writing SQL is in general pretty easy (basic set theory). I'm guessing it's all about what people have experienced. I have been coding against RDBMSes since my first job while still in college. I have even read Celko for fun at one point :) IMHO, learning the hard parts of SQL only come from experience with particular RDMSes and dealing with lar…

[deleted]

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

#74

Post by endersshadow »

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…

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.

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

#75

Post by Tyrannosaurs »

matwood wrote: post #57
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've never understood the typical programmers aversion to query writing. Writing SQL is in general pretty easy (basic set theory). I'm guessing it's all about what people have experienced. I have been coding against RDBMSes since my first job while still in college. I have even read Celko for fun at one point :) IMHO, learning the hard parts of SQL only come from experience with particular RDMSes and dealing with lar…

I think it's easy once you've got your head round the model of data sets but there are plenty of experienced programmers who, due to lack of experience with SQL, still drop back to iterating through large collections of data.

It's the fastest way for them to solve a problem in terms of development effort, but it often leads to poor solutions and poor SQL.

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

#76

Post by bhousel »

sesqu wrote: post #66
bhousel wrote: post #42

Earlier quoted context omitted.

There's no reason ever to use a right join. I consider those to be a code smell.

There's about as much reason to use a right join as there is to use a left join.

Yes, but one of them means that you're thinking about the problem backwards.

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

#77

Post by ckuehne »

endersshadow wrote: post #71
sesqu wrote: post #66

Earlier quoted context omitted.

There's about as much reason to use a right join as there is to use a left join.

I don't think I've ever used a right join in a production query (perhaps a couple of times when I was doing some digging and it was easier), but I don't understand your statement. Left joins are insanely useful. For example, getting all the sales for a salesperson: select sp.Name, sum(s.Amount) as TotalSales from SalesPerson sp left join Sales s on sp.SalesPersonKey = s.SalesPersonKey group by sp.Name order by TotalS…

What he (probably) means is that the following query returns the same result as your query:

    select
        sp.Name,
        sum(s.Amount) as TotalSales
    from
        Sales s
        RIGHT join SalesPerson sp on
            sp.SalesPersonKey = s.SalesPersonKey
    group by
        sp.Name
    order by
        TotalSales desc
EDIT: Formatting.

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

#78

Post by bialecki »

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.

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

#79

Post by astine »

_delirium wrote: post #65
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…

Isn't rewriting a stored procedure into multiple queries plus Java code sort of the opposite of what most DB best-practices advocate? Now your query logic is in a mixture of SQL and Java, rather than all in SQL, and you've hidden some of the logic from the database's query planner by moving it into application code. (On the other hand, I've rarely found query planners, even for Oracle, to be as magical at optimizing…

The performance problem with using multiple queries comes mostly from the network latency and query overhead that happens every time your application needs to make a call to the database. Prepared statements minimize the later and connection pools help with the former. The primary thing to worry about when writing queries is whether the logic behind them is efficient and doesn't for the database to do stupid things or fetch the wrong information. (Like using count( * ) where exists is what you mean.) Paying attention to what your queries will make the database actually do is more important than following any guidelines.

At least that's my experience.

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

#80

Post by ohashi »

endersshadow wrote: post #28
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 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…

Can you explain each of them, when they are typically used and why they matter? I've used them before but honestly, I am not that comfortable with them and don't ever think about using them.
Post reply on HN