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

131–140 of 173 posts

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

#131

Post by protomyth »

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

Some databases really do loose their mind when the query contains many tables. Sybase was particularly a pain and pretty much required breaking up big queries. Sybase's optimizer for version 12.5 and earlier (cannot speak after that) often required forcing indexes and plans in stored procedures.

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

#133

Post by mkopinsky »

FuzzyDunlop wrote: post #110
jswinghammer wrote: post #7

Earlier quoted context omitted.

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…

Nothing builds confidence in a fellow developer like

  //We have to do it this way. Trust me
followed by crap code...

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

#134

Post by mkopinsky »

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…

The same reason we have front-end developers who don't know how to write business logic, and back-end developers who don't know how to write a presentation layer.

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

#136

Post by Lozzer »

FuzzyDunlop wrote: post #110
jswinghammer wrote: post #7

Earlier quoted context omitted.

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…

We have a legacy database we support that creates a new dynamic stored procedure (with the same body) every time it performs a particular operation. How you can have enough knowledge to do this, but not to use a single procedure is beyond me.

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

#137

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

I have seen a few of those, but you also have to include not only stored procedures but also table layout etc. Things like "if I don't define my foreign keys, I can use a foreign key to reference rows in EITHER table a or table b!"

And then of course when you write sprocs against that mess, you get more messes.

The thing is that databases are fundamentally math engines with some extra services tacked on. This means that designing databases is ideally a mathematically sound representation of your data not specific to your application, and the database queries bridge the gap. Most developers don't understand this though and so as the application changes, you get big messes.

Now, obviously the above is an ideal which really can't be met in most cases. However the closer one is, generally the more ideal the situation, and most apps are really far from it.

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

#138

Post by einhverfr »

jcromartie wrote: post #51

People in that thread are bragging about their 10-page queries with 20 joins or 8 unions. I'm looking at a query here that is 37 printed pages, with 92 joins over 25 unions.

I think at this point I am bragging that I have never written one. Ok, I have used views of views, but.......

I have, however, had the misfortune of troubleshooting those 10 page queries. Finding a stupid typo in one of those is like looking for a needle in a haystack.....

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

#139

Post by einhverfr »

jarek wrote: post #104
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.

I once joked (as an April fools joke) about writing an HTTP server as a stored procedure on PostgreSQL (that would mean patching PostgreSQL to run as root since only root can listen on port 80).

You can read the email at http://ledger-smb.1045705.n5.nabble.com/Announcing-the-Devel...

Unfortunately people thought I was serious and I still get questions about it. Reading this thread I am starting to understand why....

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

#140

Post by andymurd »

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…

One of the biggest hurdles that typical programmers must overcome is that SQL is a declarative language, and therefore quite different to the imperative languages they are used to.
Post reply on HN