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

151–160 of 173 posts

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

#151

Post by TruthElixirX »

tom_b wrote: post #19
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…

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

"Because most undergrad education around databases is poor and antiquated. Plus, it is not SQL focused - much time is spent on talking about data modeling. Not wrong, but not often helpful in practice."

I am taking a database management class this semester. Its awful. We spent over half the semester drawing diagrams. Just last week we started working with actual SQL.

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

#152

Post by einhverfr »

astine wrote: post #79
_delirium wrote: post #65

Earlier quoted context omitted.

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

I'd agree. I'd also add that well written stored procs are often more readable than well written ad hoc queries.

The longest query I have ever written I thought was approaching unmaintainability at about 100 lines, but I can sit down and digest it without too much effort. I have however spent a week debugging a query that was three times that long. I think the time to understand starts increasing with the square of query length...... 100 lines may not take too long to understand, but 200 lines takes 4 times as long, 1000 lines takes 100 times as long, etc.....

At least my experience is that even well written SQL starts becoming a bear really fast when over 100 lines ling.

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

#153

Post by mey »

tom_b wrote: post #19
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…

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

One of our basic interview questions is doing schema modeling on a white board from business concepts we lob at them.

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

#154

Post by nithinbekal »

I was just trying to figure out a stored procedure that queries one table, loops over the rows, and within the loop queries another table using the values from the first query. Now, looping over these rows, it has a third query and a corresponding loop over those rows.

And all that for inserting the values taken from the three tables into a 4th table. This could have been done with a simple 3-table join query. Hell, it could even have been done with a single insert statement! I wonder how people fail to recognize an N+1 selects problem when it's staring them in the face.

Well, to be fair, this problem I described isn't exactly an N+1 problem is it? More like an N(M(L+1)+1)+1 selects problem. ;-) (Unless I've got my math all wrong there?)

How I hate working with PL/SQL stored procedures! :(

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

#155

Post by kahawe »

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…

I cannot help but think that the recent flavor-of-the-month rush to all that NoSQL is nothing but the average web monkies who do not understand databases, SQL and the fundamental problems RDBMS are dealing with so, yea, when I have no idea about all that I would prefer a network-accessible key-value map as well...

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

#157

Post by jtheory »

ckuehne wrote: post #77
endersshadow wrote: post #71

Earlier quoted context omitted.

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.

That's what was meant, I assume, but it's misguided. Right joins don't create a "code smell" for technical reasons, but for practical reasons. Because left joins are the standard approach, if you're using a non-standard approach to a common problem you'd better have a damned good reason (because you're just confusing maintenance programmers... and thus causing errors... with no justification, otherwise).

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

#158

Post by jtheory »

mkopinsky wrote: post #133
FuzzyDunlop wrote: post #110

Earlier quoted context omitted.

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…

Nothing builds confidence in a fellow developer like //We have to do it this way. Trust me followed by crap code...

Hi there! I made mostly random changes until it worked, so I have no idea why this contorted approach fixes the earlier failures. But I spend two weeks on this section, so don't touch it!

[this comment removed and replaced with the "Trust me" line, because maybe multi-line comments are breaking things today...]

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

#160

Post by LewisCr »

fendale wrote: post #116
miahi wrote: post #88

Earlier quoted context omitted.

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.

This code:

    Where a.client_id = c.client_id
    And a.client_id = b.client_id (+)
    And c.client_id = b.client_id (+)
Doesn't fly in our database
Post reply on HN