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 time is spent on talking about data modeling. Not wrong, but not often helpful in practice.
Because DBA's are (rightly) focused on making sure your db is properly backed up, replicated, and fault-tolerant. They may not even understand SQL queries beyond the basic one table select.
Because the users will force data into the systems that the developers never anticipated.
Because db jobs are segregated by product for development. If you've done SQL coding on Oracle for the last 10 years, good luck applying for a job doing SQL coding on MS SQL Server.
Because SQL hacking doesn't get much respect. I've been in job interviews where deep into the process they ask me a question with data in two tables that requires an outer join. And answering correctly differentiates you significantly from other applicants. I guess it is the "fizz buzz" of SQL these days . . .
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 that's bullshit. No one would use your application without the data, so the database is as much of your responsibility as any other part of the system. You should know how to use it well and should take responsibility for its quality.
On the other hand, there are things that belong in application code, rather than, for example, in a stored procedure. Most things you could do with a stored procedure probably belong in application code. The app is where the business logic is, the database is where the data is. As a DBA you aren't doing your job right if you feel OK about implementing business rules in SQL. It's much poorer at expressing intent than an OO language would be, it's less readable, and doubles the number of places you need to look for domain logic.
The WTF's go both ways.
My take is that the principles of database engineering only make intuitive sense at scale.
If every programmer started their career as an assistant DBA at a web host with a lot of traffic, or a data warehouse with a ton of transactions and report generation going on, or a company where lots of different groups were developing client code against the central database, every programmer would be inspired to understand databases.
As it happens, programmers start their career building little apps with one client at a time and a handful of data. And many of them finish their career building such apps, because they are very handy. And these programmers may never feel a burning need to know how databases perform with large datasets, or a huge stream of colliding transactions, or a mission-critical data integrity and safety requirement, or a variety of clients all trying to generate complex reports.
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.
Sigh... This again. Out here in the real world, we have databases with a hundred apps connected, with tens of thousands of concurrent users, and these apps are written in a dozen or more languages, there's COBOL, VBA, C++, Java, Python... There's green screens, desktop apps, websites, embedded devices...
The ONLY way to have consistent, enforced business logic, from ANY application in ANY language (even people executing SQL directly) is to do it in stored procedures. Doubles the number of places? Your way is 100x the number of places. Little websites with one application running on one database are not really representative of serious database use.
Ho ho ho. Website experience doesn't translate to real database work.
As to the rest of your response, I don't mean to be rude but you're simply rambling. Obviously with a legacy system you work with what you've got, but the point is what you would choose if you had a brand new system and needed to make the call today. Choice of language depends on system requirements as we all know.
I really feel uncomfortable about all this posturing. I'm all for discussion, but if you honestly consider me a moron for saying what I have, you shouldn't have any interest in responding. Participating in a discussion merely to dismiss (and disparage) the other party is pointless and boring. Down-vote and move on.
It must be nice to only work on greenfield projects, but as I keep saying, it is not experience that transfers to large scale development efforts, which span decades and continents. If you spread this kind of misinformation, you create more problems than you solve, when the "business logic" is in a hundred places and no-one knows what bit of the system does what. Sure SQL isn't the perfect language, but it is the de facto lingua franca. And there is no "the app" for the business logic to live in. There are a hundred apps, each of which is a piece of "the app".
And if we're pointing fingers, it was you who asserted that a DBA who sees the big picture (funny how every developer thinks theirs is the only app in prod, when they are really in a cast of thousands) doesn't know how to do their job. Show a little humility and you will learn.
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 doesn't tell you much."
Relational databases are oft-misunderstood and underestimated time and time again. Where are the Codds of yesteryear?
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.
It's the fastest way for them to solve a problem in terms of development effort, but it often leads to poor solutions and poor SQL.
The result is that many developers will do selection and sorting algorithmically (taking the bugs and LOC that entails), instead of using declarative SQL.
I have worked at places were the database is just seen as something in the periphery, that you just have to deal with since Oracle is a requirement from customers. Its sick really when you think about it.
Im disappointed by many of the comments here that basically is "SQL sucks because I don't understand it".
I have no formal CS schooling, so thinking in sets is far from basic - I often have to stop myself from thinking in iteration. Reading Celko's "Thinking in SQL" book is in fact helpful (although why does he keep yelling at me? and what's FORTRAN?)
But it's very foreign and black-box-y to many programmers, and a tiny bit of SQL voodoo, like a tiny bit of Javascript voodoo, will get you a very long way with very poor performance.
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.
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 vs. left outer join distinction might be unclear to even relatively experienced engineers coming from such an environment.It's very common to want to LEFT JOIN two tables, and then see specifically which ones on the left had no corresponding record on the right. But the old way cannot specify the JOIN criteria separately from the filter criteria, so we can't express the query.
For example, in my current database, I might do the following to see what categories are not represented by any products:
SELECT *
FROM Products.Category AS c
LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId
WHERE p.ProductId IS NULL
By specifying the JOIN criteria in the ON, I can then use the WHERE to winnow down to the left-only rows. But if that were expressed in the old notation, it would be mistaken for JOIN criteria, and thus all categories would appear to have no products. SELECT *
FROM Products.Category AS c
, Products.Product AS p
WHERE c.CategoryId = p.CategoryId (+)
AND p.ProductId IS NULL
so I think your point is mistaken. I do agree that the explicit JOIN syntax is clearer.Also, changing schema tends to be easier too as you can often just delete the JOIN line if you've been coding consistently instead of having to pick through the WHERE clause on more complex queries.
In the end though I guess a lot of it comes down to what you're used to, though I have been exposed to both and would definitely be in the JOIN camp and help utterly destroy you evil ,s if we had to have a worldwide battle to decide the fate of SQL kind.
Let's modify the query slightly, to retrieve all categories are not represented by any products from a particular manufacturer. The standard syntax would look like this:
SELECT *
FROM Products.Category AS c
LEFT JOIN Products.Product AS p ON c.CategoryId = p.CategoryId AND p.ManufacturerID = 123
WHERE p.ProductId IS NULL
How will you express this in your notation? In particular, what differentiates the "p.ManufacturerID = 123" of the join criteria from the "p.ProductId IS NULL" of the filter criteria?SELECT * FROM Products.Category AS c , Products.Product AS p WHERE c.CategoryId = p.CategoryId (+) AND p.ProductId IS NULL AND p.ManufacturerID = 123
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.
edit - I tried this out and right now cannot get that error, so not sure where I got that idea from.
Where a.client_id = c.client_id
And a.client_id = b.client_id (+)
And c.client_id = b.client_id (+)
Doesn't fly in our database select
sp.Name,
sum(s.Amount) as TotalSales
from
SalesPerson sp
left join Sales s on
sp.SalesPersonKey = s.SalesPersonKey
group by
sp.Name
order by
TotalSales desc
If you did a correlated subquery, it would take a ton of time to complete (`select sum(Amount) from Sales s where s.SalesPersonKey = sp.SalesPersonKey`), especially on large tables.Left joins (and full outer joins) are plenty useful and I use them almost daily. Care to explain what you mean?
select
sp.Name,
sum(s.Amount) as TotalSales
from
Sales s
RIGHT join SalesPerson sp on
sp.SalesPersonKey = s.SalesPersonKey
group by
sp.Name
order by
TotalSales desc
EDIT: Formatting.[1] Though I apparently did well enough to get the job, I felt as if I utterly failed the interview.
http://www.codinghorror.com/blog/2007/10/a-visual-explanatio...
Hope that helps!
I am taking a database management class this semester. Its awful. We spent over half the semester drawing diagrams. Just last week we started working with actual SQL.
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 to the project in a week or two so I would fix it. They just kept raising the timeout values until the pages rendered. The problem was simple in that they were accessing a table with 30 million rows without using any index at all so changing it to use the clustered index made the query return almost instantly.
I've seen similar things where people come up with crazy ways of doing your most basic database tasks.
Don't lord it over them, explain.
Other devs on my team had real problems with it - they were stuck and found it really difficult to "think in sets." They kept asking me about how to use cursors to go through data . . . heck, they may even have been right from a clarity and maintenance perspective.
But the object-oriented part of my brain has apparently been excised by spending so much time fishing (SQL). So now I have too many fish questions in that direction.
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.
//We have to do it this way. Trust me
followed by crap code...[this comment removed and replaced with the "Trust me" line, because maybe multi-line comments are breaking things today...]
This parameter saved it all "innodb_buffer_pool_size = 768M" ... how am I supposed to know that Rails creates InnoDB by default, i thought MySQL default was always MyISAM.
In real RDBMS systems, tables can be clustered (index organized in Oracle-speak) or not, nested or not, in different tablespaces, with many different parameters that do not change the semantic of DML/queries but change the performance significantly.
(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.)
If this is wrong and goes against "DB best practices" then I guess this answers my question about where the howlers come from.
[We had a project under development. At some point, and an utter surprise to the team, an Oracle consultant showed up one day and said, "I'm here to tune your database."
"Huh?"
And he did. Against a half-built system with a schema we were still designing, he "optimized" our queries and determined buffers sizes and whatnot, and then he went away.
Apparently the people in Sales were panicked that we had a database under development that we hadn't yet made plans to tune, so they had hired a guy to come in and fix things.
We tossed all of his scripts and rolled back everything he'd broken, and continued coding. Total waste of money.
Databases are a weird confluence of the power-mad, the knuckle-draggers, the money-grubbers, and a few techies who know what the heck they're doing.]
At least that's my experience.
The longest query I have ever written I thought was approaching unmaintainability at about 100 lines, but I can sit down and digest it without too much effort. I have however spent a week debugging a query that was three times that long. I think the time to understand starts increasing with the square of query length...... 100 lines may not take too long to understand, but 200 lines takes 4 times as long, 1000 lines takes 100 times as long, etc.....
At least my experience is that even well written SQL starts becoming a bear really fast when over 100 lines ling.
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.
See also Linq, you get some really crazy stuff written in Linq because people don't really understand it.
BTW, as a counterexample, I saw one developer figure out how any invoices were closed by retrieving millions of them from the db and checking in Perl. All for the want of a HAVING clause.... Needless to say while this worked ok for small amounts of data, it failed with large sets.
I've made a few SQL errors in my past that failed on large sets, but fortunately I was working against large sets, and they failed miserably, and we had to figure out why. I got good advice from an expert and fixed the problem. In many ways keeping queries separate, and focusing on readability/code standards helps a great deal I think.
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 five minutes to run.
[... then there was the guy who implemented bitwise AND and OR by precomputing some 65536 entry tables. Wow. Why do I find all the really howling bad stuff so close to databases?]