91–100 of 173 posts
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…
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…
I've always believed those tools must exist, but sorting out the SEO crap and advertising copy that gloms up search results for them is painful. Can you name any Linux/Mac tools like that?
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…
I've always believed those tools must exist, but sorting out the SEO crap and advertising copy that gloms up search results for them is painful. Can you name any Linux/Mac tools like that?
I can't read much of the query, but at least a few lines are checking for null values. I wouldn't be surprised if 80-90% of the query is simply output formatting. Depending on the DB platform, some formatting and null-check statements are fairly verbose.
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…
So SQL is just as easy to pick up as anything else, but you're going to be horrible at it at first and need to get past the mental block of seeing something so different conceptually to "normal" programming.
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…
Can you explain each of them, when they are typically used and why they matter? I've used them before but honestly, I am not that comfortable with them and don't ever think about using them.
http://www.codinghorror.com/blog/2007/10/a-visual-explanatio...
Hope that helps!
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…
Not that I really care about "back in the day!" But it can be helpful to understand why certain things were done in a certain way. It seems like ancient history, but there you definitely will find some old code when you start working at companies that have been around for more than a few years.
This query could be condensed considerably if rewritten in htsql.
(htsql automatically generates SQL code that covers all corner cases and executes faster than hand-crafted SQL.)
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
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…