61–70 of 173 posts
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.
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…
In fact, due the lack of side effects it's much easier to do than with procedural code.
When I've seen this article back in my RSS reader, it reminded me of one particular query that was generated in the application I'm maintaining. My irrational fear of sending too many queries to the database (I've outgrown that in the last 6 years) caused a single query to be generated which was 4KB in size. Which of course is much less than the one in the picture, but still very, very bad. Some so we've refactored t…
4KB? That's nothing. Search for 'media.sql' in any recent Adobe installation media. You'll find 3 MB+ SQL files, containing: -(BASE64?) encoded InstallerIcon, -(BASE64?) encoded EULAs in various languages -GUIDs like {01C3BD72-7371-4472-B179-B4DFE6DDD251} -and my personal favorite: a 25 KB XML fragment
Based on what you're describing, I now believe those developers were poached from Adobe.
I have a very simple rule for myself - "if a user of the application is concerned about a certain attribute or state an element (e.g. person, truck, plane) is in, then a report will be required showing all elements with that attribute or state."
If your database design cannot support that rule, then trouble will happen and you will have serious performance problems.
To give a simple example, suppose you are running a group of storage garages. You have a table with all your customers, a table with all your storage units, an assoc table joining customer and units with active flag + date of start, and a table with all your payments. Good enough to do transactions and figure out for a unit if they are payed up.
On the other hand, writing the report to tell who hasn't paid is going to be kind of a pain. It is a simple example, but not much different from what you find in large systems.
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…
(On the other hand, I've rarely found query planners, even for Oracle, to be as magical at optimizing as one might hope, so maybe that's why.)
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.
Just so folks know, there are tools that will decompose queries and make nice little pictures out of them. With something like this, you'd have to use it just to get started. Once you've visually decomposed it, you'd physically decompose it by splitting it inside-out. Then proceed to understand and debug inside-outwards. Not fun, but not impossible. Just a huge pain in the ass. Making it more fun would be a database…
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…
select *
from foo f, bar b
where f.baz = b.baz (+)
select *
from foo f, bar b
where f.baz (+) = b.baz
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.
Eventually we got the approval to change to MySQL for the database. When they ran the first stock update with the new version they rang us up to check it had worked because it was near instantaneous.
The moral of the story: Access is BAD! VERY BAD!