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

141–150 of 173 posts

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

#141

Post by einhverfr »

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…

"Wow. Why do I find all the really howling bad stuff so close to databases?"

BTW, as a counterexample, I saw one developer figure out how any invoices were closed by retrieving millions of them from the db and checking in Perl. All for the want of a HAVING clause.... Needless to say while this worked ok for small amounts of data, it failed with large sets.

I've made a few SQL errors in my past that failed on large sets, but fortunately I was working against large sets, and they failed miserably, and we had to figure out why. I got good advice from an expert and fixed the problem. In many ways keeping queries separate, and focusing on readability/code standards helps a great deal I think.

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

#142

Post by harryh »

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

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

#143

Post by CWuestefeld »

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.

That’s expressible in Oracle syntax

Let's modify the query slightly, to retrieve all categories are not represented by any products from a particular manufacturer. The standard syntax would look like this:

  SELECT *
    FROM Products.Category AS c 
    LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId AND p.ManufacturerID = 123
    WHERE p.ProductId IS NULL
How will you express this in your notation? In particular, what differentiates the "p.ManufacturerID = 123" of the join criteria from the "p.ProductId IS NULL" of the filter criteria?

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

#144

Post by jaylevitt »

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…

In four weeks I've gone from not remembering the difference between inner and outer joins to isolating a bug in the Postgres optimizer... and yeah, it's all about daily experience.

I have no formal CS schooling, so thinking in sets is far from basic - I often have to stop myself from thinking in iteration. Reading Celko's "Thinking in SQL" book is in fact helpful (although why does he keep yelling at me? and what's FORTRAN?)

But it's very foreign and black-box-y to many programmers, and a tiny bit of SQL voodoo, like a tiny bit of Javascript voodoo, will get you a very long way with very poor performance.

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

#145

Post by jaylevitt »

flomo wrote: post #103
gibybo wrote: post #34

Earlier quoted context omitted.

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.

In 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'.

Yeah, I know a Rails developer who thinks four-way joins are a design smell.

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

#146

Post by einhverfr »

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

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

#148

Post by dangoldin »

CWuestefeld wrote: post #143
robinhouston wrote: post #122

Earlier quoted context omitted.

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.

That’s expressible in Oracle syntax Let's modify the query slightly, to retrieve all categories are not represented by any products from a particular manufacturer . The standard syntax would look like this: SELECT * FROM Products.Category AS c LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId AND p.ManufacturerID = 123 WHERE p.ProductId IS NULL How will you express this in your notation? In particular, w…

I'm no expert in SQL (Especially the Oracle flavor) but why would the following not work?

SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL AND p.ManufacturerID = 123

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

#149

Post by derefr »

ceejayoz wrote: post #128
og1 wrote: post #102

Earlier quoted context omitted.

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.

Or, ala Reddit, comments from deleted users.

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

#150

Post by ngsayjoe »

ceejayoz wrote: post #127
moe wrote: post #123

Earlier quoted context omitted.

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.

No, it is not the code. It is InnoDB's default buffer pool set to 8MB that cause my website to be so slow ...
Post reply on HN