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

101–110 of 173 posts

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

#101

Post by InclinedPlane »

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…

The worst problem I've seen in a similar vein was a build process that involved munging a large (200+ mb) xml file. Someone had written a little JS tool for the purpose, and later the use of it had expanded so that it was being run over 150 times during the build. Since it took about 4 minutes (each time) just to read and save the xml file this meant that the build was now spending around 10 hours every day just munging the xml file. I rewrote the JS bit so that it took in a config file and could do all the processing necessary in a single pass, which only took 4 minutes.

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

#102

Post by og1 »

bhousel wrote: post #42
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…

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

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

#103

Post by flomo »

gibybo wrote: post #34
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 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'.

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

#104

Post by jarek »

dos1 wrote: post #8

While we're sharing horror stories... I once encountered a stored procedure that returned HTML in a result set. It literally created the UI of a webpage. It returned several columns of HTML that the app would place in strategic parts of the page. Well, as years went by, the app required a more innovative and web 2.0 UI. Rather than remove the HTML from the sproc, more columns were returned with more HTML, Javascript…

At least some versions of Sybase SQL Anywhere have a small HTTP server built in. Specially named/declared stored procs return HTML. I've built a smallish web app based on one, complete with Ajax touches. It was fun for one of my first programming jobs, but I don't know how maintainable it ended up. On one hand, at least your logic is close to your data... on the other hand, oh my god.

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

#105

Post by flomo »

ianterrell wrote: post #33
yuvadam wrote: post #29

Sorry, I call BS. I might have just been lucky enough to always work at professional companies and startups where this kind of stuff can never happen. But something tells me there is no reasonable way an SQL query can grow to these proportions.

All the good techniques and technologies you use now? Yeah, those were invented because the way a lot of people used to do things was terrible. I'm not old enough to have built systems like that, but I have inherited some for maintenance and debugging. That's not likely to be BS. Back in the day "do it in a stored procedure" was common advice. All the logic for untold applications and millions of lines of code lived…

There's still plenty of DBAs out there preaching "do it in a stored procedure", because in theory it lets them do things like changing normalization without affecting the application interface. (In practice, I've never seen happen that way.)

But in general, people did thing that way because there wasn't any common middleware or services interfaces. The RDBMS was the "app server".

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

#106

Post by bmj »

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…

When I interviewed for my current position (which involves database design/query writing/storage/etc), the interview was heavily SQL-focused. My would-be manager started with very easy, general questions, and then continued to plumb the depths of my knowledge 'til he hit bottom (took about 30 minutes[1]). He is of the opinion that if you can think on your feet about general database design and set theory, you're a pretty competent programmer for the work the company does.

[1] Though I apparently did well enough to get the job, I felt as if I utterly failed the interview.

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

#107

Post by jmelloy »

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 have trouble with the oracle syntax once you get to two or three tables left joined against each other, and consider it more a relic of old oracle programmers than any actual advantage one way or the other.

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

#108

Post by pbh »

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…

I agree with your point, but with an important caveat.

You are right that application developers should not be ignorant of databases, and DBAs should not be ignorant of what application developers are doing. All too often, this is the case.

However, the point when people came to think of application and database development as two separate things was the origin of databases themselves, and arguably one of the most important historical points in computer science itself. The notion that data has a life outside of any given application, and that it deserves itself to be managed, was and is incredibly powerful.

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

#109

Post by ceejayoz »

ngsayjoe wrote: post #84
jswinghammer wrote: post #43

Earlier quoted context omitted.

This is changing because MyISAM is not really a true database (in the ACID compliant sense of the term). I guess I would have assumed they were MyISAM too but they don't perform differently enough at low usage levels that you would notice it right? How big was this was application?

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

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

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

#110

Post by FuzzyDunlop »

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

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 our sites was "dying on its arse." It was hammering the slow query log, pages could take 10, maybe 20 seconds to load.

Turned out there were NO indexes at all. Maybe the odd primary key from the default CMS install (which was by no means database efficient). So we sorted that and that sorted out the immediate speed issues.

Of course, it went without saying that a poorly constructed schema must be related to a poorly constructed query or two. We were actually wrong. It was more like a query or twenty.

The submission of one form was done field by field. There was an UPDATE query constructed for each field and processed there and then, on a form with at least 12 fields. There was a comment above, "We have to do it this way. Trust me." As far as I could tell they didn't know how to dynamically build a query.

Further along, different update queries were performed by first deleting the record, and then re-inserting it with one of the fields updated.

I never quite got as far as that. There was no comment anywhere about the reasoning for such a strange approach.

I think some developers don't bother figuring it out because they think there are better things to do than optimise your code.

Post reply on HN