Why Programmers Don't Like Relational Databases
typicalprogrammer.com
typicalprogrammer.com
I've built a career moving data out of flat files into RDBMS in order to get systems working properly (and quickly). I couldn't believe the hoops people jumped through just because of their dislike (or ignorance) of relational databases.
This data base model is perfect for many applications, and can easily be mixed with other data storage technologies (if you know what you're doing.)
Don't like the capabilities of your RDMBS? Then add what you need to you app. Indexing, transactions, stored procedures, audit, security, whatever. The code doesn't care where it's at. It still runs.
Not pretty enough for you? Then become a mathematician or fashion designer. Otherwise, shut up and code.
Not practical whatsoever.
Or do you mean that using "like" is slow? i would think that it would not be as long as that column is indexed.
My ideal database would just act like a big hash table. Where I can store an object and retrieve it at will, without having to learn any other language. (sql in this case), or use complicated ORMs, (Hibernate, or Django models). etc..
So, for some reason or the other, there hasn't been much inovation in this field. Maybe b/c the big players in databases like it as it is, and there is no incentive to inovate.
Just b/c you drank your koolaid a while ago, doesn't mean everybody else has. In the programmer's poing of view, right now databases are a necessary evil, but they could be much better.
- Give me a list of good (lifetime sales > $10,000) customers on the west coast (CA, OR, WA) who bought any product on the defective list during busy season (10/06, 11/06, 12/06) and haven't placed an order since our last email blast. We'll find out why.
- Give me a list of phone numbers (using our Caller ID) of people who called in the last 4 days, whose call wasn't answered and had never called us before. We'll call them back.
- Give me a list of our slowest moving (10%) products that haven't been ordered in the last 4 months that are sitting in prime space (Location beginning with 1,2, or 3) in the warehouse. We'll move them to pallets to make room for new stuff.
- Give me a list of all products returned from Territory 7 in the last 3 months with a Problem Code related to fit or size. We want to make sure their description is correct on the website.
I could go on (and on and on)...
Do any of these with your "big hash table" in less than 1 second and earn a seat at the table with the big boys.
Just because something is old doesn't mean it's obsolete. Maybe because it works so well.
And just because someone understands this doesn't mean they "drank kool-aid".
Nothing wrong with a little debate. But please come up with more creative methaphors.
(The reply that I would have written if you didn't respond first would probably have emphasized the amount of code required rather than performance.)
I could have made any number of technical arguments. We'd be pissing at each other all day.
Instead, I cited a few things that concern those who pay the bills. As hackers engaging in good technical debate here, it's too easy to lose sight of those whom we serve.
allowed_forums = [] // empty set
for each group in user.groups
allowed_forums |= group.forums // union
Now let's compare it with your SQL solution. You see, mine so simple, it's not even an interview question :)
2. Suppose you have one million users. Then you're sucking up an awful lot of RAM just for this tiny piece of functionality.
3. Your pseudocode makes a lot of convenient assumptions about what functionality is already provided in standard libraries and in the accessor methods for user.groups and group.forums. Let's see it written in a real language.
4. You say you don't care about how data is stored and accessed, but given the right set of reasonable assumptions, I can conclude that the running time of your algorithm is O(n^3). The beauty of relational databases is that you usually really don't have to care. The query optimizer is pretty good at figuring it out for you.
EDIT: nostrademons, I saw that peer reply before you deleted it. That was just spookily similar :-)
allowed_forums = []
for each forum in forums
if forum.groups <= user.groups // set comparison, like in Pascal
allowed_forums |= forum // include an item
3. My pseudocode is an assumption that it is possible to build some language/environment that would replace databases, only in theory so far.
4. Ok, maybe I'm going to use the "beauty of databases" in the implementation for my language. The goal is to eliminate SQL (and useless interview questions at the same time :)
2. There's a word for such a system. It's called a database :-).
3/4. Ok, sounds good. But like many others in this discussion, you're confounding SQL with the relational model. Your pseudocode above goes slightly outside the relational model because it's written in imperative style, but can be adapted to it easily enough just by replacing your for loop with set-builder notation and maybe a unary union operator. Sure, SQL has warts. It was originally designed as a language extension for COBOL for chrissakes. But the language you're inventing is just a prettier syntax for basically the same semantics.
No offense, drfranke, but this reminded me of a conversation I had long-long ago, with a group of physicists. I was saying Fortran was going to die soon and their only argument was "see, you can do it in Fortran! you are just replacing one syntax with another!". Actually damn Fortran died a bit later than I expected, but those're just technical details :)
This is not true. Let's get back and compare the intellectual effort required to solve your task in SQL and in my imaginary language. The difference in complexity is exactly the amount of effort by which you help the SQL query optimizer to do its work.
Tables and SQL are old concepts which haven't evolved thru time. If SQL was so great, why in the heck there is so many ORM frameworks out there? Is it b/c people are stupid, or maybe b/c there is actually a need to just have a Object Oriented Database.
That does not mean that databases are useless. Your [(customer.name, customer.phone) for customer in customers for sale in sales if sale.customer_id = customer.customer_id and sale.product_id in defective_product_list and sale.date.month in (10, 11, 12) and customer.lifetime_sales > 10000 and customer.state in ('CA', 'WA', 'OR')] isn't going to help much if you have thousands of customers and millions of sales.
Remember, this is a discussion of relational databases, not SQL. 2 different issues.
That was early 2005, though. It may be better now.
Uh, isn't that an argument FOR the popularity of relational DBs? There would be no need for ORM tools if there weren't lots of R databases that programmers need to interface with.
To turn your rhetorical question around "Why haven't OO databases become popular if that's what so many people really need?"
XML, while not necessarily 'object-oriented' is hierarchical, not relational. It's becoming very popular.
My anecdotal experience is that a lot of programmers bristle when they get into a technical area that actually has a mathematical basis or a demonstrably right way to solve a problem. Programmers are used to problems that have lots of possible solutions, none of them provably right or wrong. I've seen that attitude in the realms of databases, computer graphics, compilers, and algorithms.
A few years ago I worked with a programmer who actually set out to prove to me that bubble sort was more efficient than whatever Oracle was doing to sort rows. He was retrieving a lot of rows, unsorted, then sorting them with his own code. Even pulling out Knuth and trying to explain big-O notation to him had no effect -- that was just so much academic bullshit and opinion that didn't apply, according to him. Of course Oracle was able to sort the rows orders of magnitude faster than his PL/SQL bubble sort, and the <i>coup do grace</i> was delivered by a DBA who took the inefficient cursors out of the solution.
That kind of thing is played out every day between programmers, and when they run into something like relational theory their first instinct is all too often to dismiss it as old, inapplicable, ugly (see the other comments in this forum), rigid, etc. Those are all code for "I don't understand it and I can't be bothered to learn it." When a DBA who is charged with maintaining data integrity interferes with whatever bad idea the programmer has he or she is subjected to <i>ad hominem</i> attacks. Some DBAs no doubt deserve contempt but in my experience a lot of programmers can and should be learning from DBAs rather than sneering at them.
In the comments in this forum you'll see the "Relational databases can't work with tree-structured data" canard hauled out, as if this exact problem hadn't been addressed numerous times in the database literature (Joe Celko, Fabian Pascal, Chris Date, etc.). That's ignorance, and the older I get the more I suspect it's more willful than I'd like to think.
What I think is "stupid" is not your article, but the fact that some programmers don't like relational databases (for whatever reason). I'll try to pick my words and make my point more carefully in the future.
I understand much of the theory behind these technologies, but, as a practitioner, I'm not in a position to base my actions solely upon theory. I HAVE to deliver. I wish I had a nickel for every benchmark I've conducted over the years. You may have noticed that I tend to avoid theoretical debate here; I'm more interested in what works.
And relational databases work very well, very often. Whether you like them or not.
Up until recently, I generally heard programmers complain constantly about the limitations of SQL and RDBMS's, and I heard a lot about object databases. The idea was that programmers would define objects, and the database would provide persistence for the objects. It was (and still is) a nice idea.
Rails took a completely different approach, embracing the RDBMS as the model itself. Rather than creating objects and pushing the design out to the RDBMS, you define the relational structure and allow Rails to extract an object model. If you are doing basic CRUD operations, you may not have to think about SQL again, even in a reasonably complicated app, and the syntax is very clean.
I just love it. And Ruby is like a beautiful flo... oh yeah, didn't want to get negged, better shut up now.
How about actually having the database start evolving just like programing languages have?
What's stopping you from solving this problem? Sounds like a great entrepreneurial opportunity. Maybe you could submit a YC application.
Any new system, (OOD) will take years before reaches the maturity level to be used in commercial solutions. Kinda hard for a cash strapped startup.
Also, there have been attempts to build OODs but they have failed, mainly b/c they way ahead of their time.
What I would like to see a distributed Object Oriented Database system, that can scale at will.
Maybe you could take a similar approach?
What I hate (and I get the feeling you hate it too) is object-relational mapping. This is truly, exceptionally tedious stuff. Hibernate was no fun, but I find Rails to be far, far less of a hassle.
And there's plenty to object to in the various implementations of relational algebra (ie., the different RDBMs's). But maybe the programming language side is exactly where the innovation should be occuring.
One problem I can think of is that I'm forced to mix two languages in one story (a program, that is). It's like writing an essay in English over-peppered with a lot of Latin phrases and sayings. In web development it gets even worse: as if my favorite programming language X plus SQL wasn't enough, I mix up HTML, CSS and JavaScript - that's at least 5 languages in total!
Another problem is that from a programmer's perspective databases are, put simply, arrays possibly with pointers. The only reason we can't declare these structures as arrays/lists with pointers is that data can grow infinitely big and my favorite language X can't handle that. In other words, using databases is a classical example of premature optimization that comes at a price of overcomplicated code. Yes, databases are plain arrays and even the best relational advocate can't prove the opposite (or I'd be glad to hear arguments actually).
Upd: persistence and a possibility to share data between processes is another reason we use databases, but I'm sure there are more elegant ways of doing this.
Another thing database give you is ACID storage. Sure, you could reproduce some of that using thread-level locking or some other locking mechanism, but then what you have isn't a simple array any more.
I can do that with hash arrays. The primary key is my primary hash, other keys are just extra hash maps pointing to the main array.
I understand, perhaps none of the existing languages can fully replace the DB functionality, but I suspect nobody is even thinking about that because - hey, there are plenty of nice DBMSs around, why bother reinventing them?
but then what you have isn't a simple array any more
True, but again, it's a matter of having an infrastructure that handles sharing for you. Windows Registry, however awkward and Microsoft'ish, does just that, as as example.
A.k.a an in-memory table with hash indexes. MySQL has them. ;-)
You're still missing out on a lot of functionality (even besides transactions/persistence/concurrency). For example, what if you want all records where the creation date is between a certain range of days? Or how about the maximum value of a certain column? How about joins?
A conventional database can retrieve these from the indexes, but your multiple-hashes system would require a full table scan.
And I never said arrays should be in memory entirely, even though I declare them as arrays in my language.
Take a look at the following SQLAlchemy code:
select([users.c.first_name + ' ' + users.c.last_name,
users.c.email, games.c.title],
from_obj=[users.join(games,
users.c.user_id == games.c.creator_id],
and_(games.c.creation_date == datetime.datetime.now() - datetime.timedelta(3),
or_(users.c.email.like('%@aol.com'),
users.c.email.like('%@hotmail.com'))))
SELECT users.first_name + ' ' + users.last_name,
users.email, games.title
FROM users
INNER JOIN games ON (users.user_id = games.game_id)
WHERE games.creation_date >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND (users.email LIKE '%@aol.com' OR users.email LIKE '%@hotmail.com')
I'm reminded of the two competing camps for large-scale systems design. The UNIX philosophy is "small pieces loosely joined": each component does one thing well, and the system just provides a common metaphor and architecture for them to work together (eg. pipes and line-oriented text). The Windows philosophy is "big chunks tightly coupled": everything just works out of the box, but if you need to do something the designers didn't think of, you're out of luck. Kinda like "I'll do it myself; show me how" vs. "Do it for me."
Nearly every computing system can be placed somewhere along this continuum. In operating systems, we've got MacOS (pre-X) and Windows on one side and UNIX on the other. In languages, we've got Scheme and Smalltalk on the UNIX side and Java and .NET on the other (ironically, Scheme and Smalltalk have a "small pieces loosely coupled" architecture internally, but are big monolithic chunks when it comes to the outside world). For IDEs, Emacs is all the way over on the UNIX side, vim not far behind, Eclipse is in the middle, and IntelliJ and Visual Studio all the way on the Windows side. For JavaScript libraries, YUI/Mootools/Prototype are all on the Windows side and JQuery is on the UNIX side, though it's moving closer to the Windows philosophy. Among web frameworks, Rails and Django are on the Windows side with Pylons on the UNIX side. The web itself is based on the UNIX philosophy, but many of the largest websites follow the Windows philosophy.
I'm not so certain that people fit rigidly into one category though. Up until about 3-4 years ago, I was firmly in the Windows camp: I did Java programming only, I did it on a Windows machine, I used JBuilder and Eclipse to do it. Then I started branching out, mostly because I started learning about all this stuff that Java wouldn't let me get at. I think many people may follow a similar path: "do it for me" until you want to do something that the designers didn't think of, and then you have no choice to move to a "do it yourself; we'll show you how" environment.
You mean simply using normal CS data structures and keeping application data in memory? That approach certainly works - PG uses it for this site, and I believe it's used for Mailinator and Bloglines.
But you should think of the services that a database gives you and consider whether you're going to use them. That includes:
1.) Indexing by arbitrary column
2.) Subranges & slices, again by arbitrary column
3.) Joins
4.) Sorting
5.) Quick summarizing, over any combination of columns
6.) Transactions
7.) Persistence
8.) Concurrency control
9.) Access from multiple languages
10.) Distribution
If you aren't going to use any of these, then by all means Keep It Simple. If you're only going to use one or two - say you need persistence, indexing, and slicing - you can probably roll something up with your language's normal libraries. But if it looks like you'll need a good chunk of them, you'll drive yourself nuts trying to implement them yourself. Even maintaining multiple indexes via your hashtable approach can get very complicated when you have multiple requests inserting, deleting, and changing rows.
No, I leave that to optimization. Could be in memory, on disk or wherever else my compiler and the run-time system decide them to be. When I see performance isn't good I may start fine-tuning my run-time system.
Indexing by arbitrary column
That's a matter of optimization. Thinking in indexes is clearly premature optimization that can be done automatically.
Subranges & slices, again by arbitrary column
That's map/reduce with some clever infrastructure that can optimize things for me.
Joins
Pointers.
Sorting
Sorting is sorting :)
Quick summarizing, over any combination of columns
Map/reduce.
Transactions, Persistence and Concurrency control
Possible without the DBMS.
Access from multiple languages
Honestly, I give up here. If we are going to have a programming language that itself handles large data structures, then other languages are probably out.
Distribution
What you mean by distribution?
Ability to spread your data across multiple physical machines.
I think it's pretty clear that if you know exactly how your data is going to be used, then performance is better if you hard code everything and store it in a way that is optimised for retrieval. However, if data is used for multiple purposes, then hard-coding access paths is horrible in terms of performance and maintainance.
I think it's generally less about readability and more about flexibility and error-checking (when it's not about being stubborn). If you want to generate SQL queries on the fly, it may be faster and less error-prone to use python objects rather than strings.
Do you mean embedded? that I agree with. Embedded sucks but there are lots of techniques (abstraction) you can use to avoid them. Unavoidable is writing web based applications without using several languages. Though I can think of some lisp apps that do not (news). Do you lisp?
Your analogy with writing an essay is off target. If writing a program is like writing an essay in English, the SQL/database part of the program are like mathematical formulas in the essay. You could use English instead of mathematical notation but it would be far uglier, less precise, and more prone to error and misunderstanding. In a typical web application (what I've been doing for over ten years) the database part is usually the only part that can be proved correct.
I can't help you overcome your misconceptions about relational theory or relational databases, or do anything about your opinion that relational theory is ugly. But you are just wrong arguing that relational databases are glorified arrays full of pointers and examples of premature optimization.
The "best relational advocate" is probably Chris Date, and if you take the time to read his books you'll at least discover proof that you are wrong.
But you are just wrong arguing that relational databases are glorified arrays full of pointers and examples of premature optimization.
I think the relational concepts are just a subset of what you can do with various kinds of collections in imperative languages - arrays, sets, maps. For example, I can have an array of records describing users, each of which incorporates a set of groups a user belongs to. In DB terms that's 3 tables (users, groups and membership) with one-to-many relations between them. Either way, we are describing the same concept of users and their membership in various groups. So why bother having another language?
My conclusion is that OOP is conceptually built around single "objects" where RDBMSs are built around sets of "objects". The difference in nominal units is some of the basis of the impedence mismatch.
Think about what it takes to do something interesting with a collection of objects in c++/java/whatever, such as pick out the subset that have a cost>n: it is a loop over the entire set. Ruby makes it prettier and cleaner, but you're still just looping. Unless you create a custom collection class that internally stores a hash organized by costs. But the creation of the new class is extra overhead because the nominal unit is the single object.
The counterexample is just as instructive. To retrieve the newest 10 entries in a blog uses the LIMIT syntax in SQL, which is painfully nonstandard: http://troels.arvin.dk/db/rdbms/#select-limit
Things that are simple in one space are hard in the other.
In my own work I've resolved some of this by attempting to avoid abusing the "standard" ORM tools that are out there that allow designing queries in your language of choice (Rails for Ruby, Hibernate for Java). Instead, I attempt to figure out exactly what I need out of query: how to structure the SQL to give me back the exact set of data I care about. No subsequent looping in Ruby/Java allowed.
That forces a line in the system between the DB and the application code where each is able to be use its nominal units in as native a way as possible.
And, for me at least, cursors are definitely in the range of "advanced-ish" SQL.
My point isn't that it can't be done -- empirically, many blogs do it -- but that it points to part of the impedance mismatch.
Sure it is: see Section 6.10 of Part 2 of SQL:2003.
> I thought it couldn't be relied on for being monotonically increasing with inserts. It will also fail if you need to use a date column that may not be ordered by time.
I think you are confused: ROW_NUMBER() is a window function, and as such is applied to an arbitrary query expression:
SELECT x, y, row_number() over (order by z) from t
I don't see how it is an "impedance mismatch": transforming sets of data is the fundamental goal of SQL, and this seems to fall squarely within that domain.