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've isolated the bug to a database query"
51–60 of 173 posts
Re: "I've isolated the bug to a database query"
#52A place I used to work at used an ORM which gradually constructed SQL throughout the flow of a request. One of the calls we generated was probably a couple of pages long.
Re: "I've isolated the bug to a database query"
#53One 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…
Because SQL is declarative and few(er) people can wrap their mind around that then straight procedural code.
See also Linq, you get some really crazy stuff written in Linq because people don't really understand it.
Re: "I've isolated the bug to a database query"
#54Earlier 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 must take my SQL skills for granted. It really just "clicks" for me, I guess.
Re: "I've isolated the bug to a database query"
#55I will say, Ingres was not my favorite database, but its query plan display should be used to explain how a database query works. It showed a tree of operations for each query. If you saw FSM (full sort merge) or Cartesian Product you better mean them or re-write the query.
Re: "I've isolated the bug to a database query"
#56I'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 found querying with SQL terribly difficult. Even experts find SQL hard to create and read. Do not be surprised if an analyst struggles to understand his/her own SQL. It is impossible for users to understand any but the simplest SQL.
Third, SQL practice suffers from the notion of a "correlated query" -- which has a monolithic subquery that is executed repeatedly via looping, once for each value of a candidate row picked by an outside SELECT.
The book has much more to say on the topic of SQL before going on offer relational algebra (built on top of SQL) as a n alternative.
Re: "I've isolated the bug to a database query"
#57Earlier 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'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 large datasets. For example, knowing that the only way to get a query to run against a large Teradata table without running out of spool space is to create and use temp tables only comes as a result from trying to run the query.
Re: "I've isolated the bug to a database query"
#58Sorry, 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…
Systems like that don't even have to be particularly old. I used to work on a system that started out as an Access database, and rather than doing things properly, the developers basically just translated everything into SQL statements and used ASP and COM+ on top of it.
It was a load of crap, but it still ran about 10x faster than what customers were used to, so they weren't complaining. Last I knew they were still running the same basic setup, and it's been 5 years since I worked there.
Re: "I've isolated the bug to a database query"
#59Reading the comments below, I get the impression that all the "good" DB people hang out on HN, not like those "other" incompetent nits out there who don't know what a join is. Hubris, people.
Re: "I've isolated the bug to a database query"
#60I 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.