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

121–130 of 173 posts

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

#121

Post by bni »

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…

Most developers learned in school to program, i.e. writing algorithms. Most development in the "business" world is not that, it is basically managing and transforming data. Something that RDBMSes and SQL is very good at.

The result is that many developers will do selection and sorting algorithmically (taking the bugs and LOC that entails), instead of using declarative SQL.

I have worked at places were the database is just seen as something in the periphery, that you just have to deal with since Oracle is a requirement from customers. Its sick really when you think about it.

Im disappointed by many of the comments here that basically is "SQL sucks because I don't understand it".

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

#122

Post by robinhouston »

CWuestefeld wrote: post #120
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…

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

That’s expressible in Oracle syntax as:

  SELECT *
    FROM Products.Category AS c
       , Products.Product AS p
    WHERE c.CategoryId = p.CategoryId (+)
      AND p.ProductId IS NULL
so I think your point is mistaken. I do agree that the explicit JOIN syntax is clearer.

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

#124

Post by semanticist »

JBiserkov wrote: post #46
pilif wrote: post #31

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

4KB? 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

It'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)

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

#126

Post by mattmanser »

robinhouston wrote: post #122
CWuestefeld wrote: post #120

Earlier quoted context omitted.

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

That’s expressible in Oracle syntax as: SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL so I think your point is mistaken. I do agree that the explicit JOIN syntax is clearer.

I find the JOIN syntax to be far more maintainable too, the WHERE clauses become really hard to read amongst the dross of boilerplate JOIN clauses from the salient WHERE clauses. Things also get confusing as soon as you throw an OR in the WHERE clause, too many unnecessary brackets.

Also, changing schema tends to be easier too as you can often just delete the JOIN line if you've been coding consistently instead of having to pick through the WHERE clause on more complex queries.

In the end though I guess a lot of it comes down to what you're used to, though I have been exposed to both and would definitely be in the JOIN camp and help utterly destroy you evil ,s if we had to have a worldwide battle to decide the fate of SQL kind.

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

#127

Post by ceejayoz »

moe wrote: post #123
jswinghammer wrote: post #118

Earlier quoted context omitted.

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

Assuming the standard bell curve it's six pageviews a second, at peak.

And that's apparently spread across four read-only slaves, too. I'm more inclined to blame the code than the database in this case.

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

#128

Post by ceejayoz »

og1 wrote: post #102
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.

Agree with this as well. Forgot exactly what one was and googled it to make sure. The example that is used here would indicate orphaned records. http://www.w3schools.com/sql/sql_join_right.asp

There are valid use cases for orphaned records. A quote database might want to allow registered and anonymous submission, for example.

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

#130

Post by caf »

forinti wrote: post #60
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've seen this in an Access application. I've also noticed a strange character that constantly shows up: the accountant that has acquired a taste for programming. Luckily, there never seems to be more than one per institution.

I think Accenture is the corporate instantiation of that character.
Post reply on HN