Flat files are a perfectly viable option in some circumstances. Not everything requires data uniformity or the ability to run complex ad-hoc queries, nor does everything need information to be controlled by a separate process running on a different machine. Not every system integrates multiple applications through a shared data-store. The NoSQL crowd isn't arguing that SQL is bad, just overused. There are a great many situations where something like flat files or Berkeley DB is more than sufficient, and yet people still use relational technology. In my experience it's generally because that's all they know. In their mind, if one needs to store data one uses SQL. They don't select the right tool for the job because they honestly don't know there are other tools.
One of the reasons is because RDBMSs offer a lot of tools, like atomicity, durability, backup/restore, centralization, point-in-time-recovery, etc. Many application developers need these things without actually needing the abstraction of a relational system.
Design an efficient table relating a tree structure. Then design queries to answer questions such as: * Find the nodes in the subtree under B. * Find all ancesters of G * Find the nearest common ancestor of D and H
Trees is a wellknown problem of SQL, but the fact is that SQL can't handle most datastructures and complex relations, only very simple one dimensional ones.
>> Trees is a wellknown problem of SQL, but the fact is that SQL can't handle most datastructures and complex relations, only very simple one dimensional ones.
Sorry, that's not true. Have you tried analytical functions? You would be amazed how complex scenarios can be handled easily with them. And they are part of ANSI SQL standards. And db providers (Oracle etc) have taken the concept and improved a lot on it.
I think the anti-sql 'movement' has more to do with new (internet era) languages and their developers than so called 'lack' of features. In my limited experience, I have observed people coming from C (and such) background have no problem with sql, while java developers (and this is probably true for most developers working on web-based applications) are the worst kind when it comes to understanding even basics of sql. All they want is their objects.
I strongly believe that a competent programmer designing/developing system which includes data and data-storage should at least know normalization, indexes, and what does it mean by 3NF. Programming language is one thing, database is another, and knowledge of both is required to build a decent system.
And to expand on that a little, I think each part of the MVC idiom has it's own domain-specific language because those languages are well-suited to those applications. An imperative language with an emphasis on objects (e.g., Java) just doesn't do the same thing that a declarative set-theoretical language (SQL) does. Well, it can, but doing so is a royal pain in the ass. That same imperative language is also total overkill for defining a layout. HTML does that job beautifully and simply.
There are certainly common CS themes running between all three. We have three languages not because people haven't thought about those things, but because they make our lives easier.
Whenever I hear people bitching about 'doing away' with SQL, I always wonder what they think is wrong with it. SQL certainly has some limitations, don't get me wrong, but it is a great language for the vast, vast majority of cases. If your application is so specialized that SQL isn't appropriate, well, bravo, but that does not mean that the relational database concept is flawed. Personally, I think if people spent a few moments doing some formal analysis before they built their databases (imagine that, thinking before doing?!), they would find that SQL is a beautiful thing. If your implementation of SQL doesn't cut the mustard, maybe you just need a better query optimizer?
I think the main problem that the web 2.0 dynamic language crowd has with RDBMs is that:
1. Relational data is strongly typed. You cannot easily add new fields to a table or store arbitrary types in a column and expect acceptable performance.
2. Migrating large amounts of relational data to a new structure takes a very very long time. Constant refactoring of data models is to be avoided. You have to get it right the first time or at least very early in the development cycle to avoid major headaches...
3. Databases are hard to mock in a testing context. Automated tests can be significantly slowed down with even a test database..
4. Error in database architecture are very difficult to correct due to 1 and 2, especially when used with a dynamically typed language..
5. It's difficult to maintain the data integrity that RDBMSs take for granted in highly scalable distributed systems and have acceptable performance.
The only real show stopper and a real reason to replace RDBMSs is #5. All the others can be worked around by just deeper study of data modeling techniques. Data modelling is not something most developers can figure out intuitively. There is a lot of theory to be learned to do it right and it can very easily be done badly leading to severe performance problems and an unmaintainable application.
With regards to # 5: I went to a presentation at Javaone where some Ebay engineers explained that they do not use transactions in any of their database operations. They just leave junk rows around in the db if a transaction half completes and as long as they aren't reachable they don't consider it a big deal. They have to very carefully organize the order in which they manipulate data to avoid data corruption,but that lets them get around # 5,
Design an efficient table relating a tree structure.
Huh? Tree structures are best handled by relational databases, as it is far faster then recursion. Give row a unique ID and a parent ID, and in addition, a left hand and right hand number, the root node having a left-hand value of 1 and a right hand value of (number rows * 2), the first child node has a left-hand value of one more than the parent's, the right-hand value is one less then the left-hand of a younger sibling.
Then design queries to answer questions such as: * Find the nodes in the subtree under B.
SELECT * FROM rows WHERE left > [left hand value of B] AND right < [right hand value of B]
* Find all ancesters of G
SELECT * FROM rows WHERE left < [left hand value of G] AND right > [right hand value of G]
* Find the nearest common ancestor of D and H
SELECT * FROM rows WHERE left < [lowest left hand value from D,H] AND right > [highest right hand value from D,H] ORDER BY right LIMIT 1
Trees is a wellknown problem of SQL, but the fact is that SQL can't handle most datastructures and complex relations, only very simple one dimensional ones.
Are you saying trees are easy or hard? And for more complex systems, that is what JOINs are for. SQL is by far the most powerful way and often the fastest way to manipulate data that I know of. The only time I can recall that I had to use a non-SQL solution that was faster then the SQL solution was a matrix operation.
For anyone wondering, parent is talking about a preorder tree traversal algorithm:
Link 1 [mysql.com]
Link 2 [sitepoint.com]
And parent it right. I was doing an adjacency list in MySQL for a while, because I thought that preorder trees were just a little too complicated, but they are *way* easier and more intuitive.
the fact is that SQL can't handle most datastructures and complex relations, only very simple one dimensional ones
You are kidding, right? Just today I cooked up a 7-table query including 2 subselects, and a left outer join to a meta table consisting of 2 inner joined tables. Total of some 11 tables comprising a highly complex data set. Don't know what you mean by "very simple one dimensional ones" but 11 tables each joined in either a one-to-many or many-to-many mapping provides at least 11 dimensions. (more if you self-join tabls, often needed) And this isn't particularly hard for me - often I have joins combining 12 or more very large tables with unrestrained combinations somewhere in the billions to trillions of possibilities that all somehow seem to parse just a few seconds thanks to a few well-placed indexes and a well-structured query.
My guess would be that because SQL is a Structured Query Language it is best used for handling structured data. If you have serial, unstructured data you have to invent your own format for it to use inside the database, and then the query language isn't helping you.
Right, I went into a little detail on another post. When I said, "genetic," I mean genes - DNA. There are four main Nucleic Acid types in DNA: Adenosine, Cytosine, Guanine, and Thymidine. Abbreviated ACGT. So you could store a gene sequence as ACGCCTGCAATC. But in other populations, Asian for example, the same gene is more commonly found as ACTCCTGCAATC. (The third nucleotide is different) Exact string matches won't find matches between different population groups. So they create wild-card letters that represent either G or T -> K. So ACKCCTGCAATC would match either the both of sequences commonly found in western and eastern populations. Data of this nature has no business being in a relational database. For that matter, it doesn't belong in these pseudo databases either.
It seems like a huge chunk of all programming is like being Gerardus Mercator [wikipedia.org].
You've got a bunch of information with one shape, but you really need it in another shape.
The code is about dealing with the embarrassment of it all.
If you've got tabular stuff, and you so very often do, the relational model is fantastic.
If you're dealing with some kind of graph, you're hating life.
People coming in complaining that their aircraft makes a poor submarine are initially amusing, but become tedious.
Genetic sequences are long strings alphabetic characters. One of the most common representations is the FASTA [wikipedia.org] which deals with the most common type of nucleotide polymorphisms. You can't use exact string searching to find a match which makes BLOBS and CLOBS useless. That said, the meta-data of genetic data is reasonably structured and does load into relational databases fairly well.
Actually, this isn't true either. See this article [dbmsmag.com] for pointers to some of the failings of SQL in dealing with financial data, particularly time series (e.g. sales figures, share prices, etc.). Here's another take on the problem [nyu.edu], which essentially is that SQL doesn't recognise that there can be relationships between the rows of a table (e.g., "this happened after this").
A basic premise of the relational model is that there is no relationship between rows. So it isn't surprising that SQL can't see any. Maybe you need to organize that data differently? You can solve a lot of problems in SQL using triggers, temporary tables, and the built-in aggregate and sorting functions.
1) 1996 called, they want their arguments back. For example, most RDBMS have ranking functions now. 2) Even in 1996, he doesn't know SQL worth shit
SELECT (prev.sales+now.sales+next.sales)/3 three_day_average FROM sales prev,
sales now,
sales next WHERE prev.day_number = now.day_number-1 AND next.day_number = now.day_number+1
Easy as pie making most of the calculations he wants. Maybe he should ask someone knowledgable in SQL?
So what you are saying is that you do not know enough of SQL to understand that query; therefore you are not qualified to comment on the practicality and viability of using SQL for complex structures.
See, I don't think there is ever a good time or place for SQL.
SELECT text FROM mild_introductory_statements WHERE id=random();
Anyone who says so has never had to use it.
SELECT text FROM statements_indicating_superior_experience WHERE id=random();
I like to compare it with JavaScript.
SELECT text FROM unrelated_tool WHERE id=random();
It's a language that is difficult to refactor, maintain, and while it's a standard, the standard is so vague that it's useless.
SELECT text FROM seemingly_valid_yet_unsubstantiated_objections WHERE id=random();
Like JavaScript, people are trying to build other languages on top of it to hide its shortcomings -- for javascript you have tools like GWT, and for SQL you have HQL, Linq, etc.
SELECT text FROM wrongheaded_causal_analysis WHERE id=same_one_as_two_queries_ago();
Not to say that there is anything wrong with relational databases, we just lack a good tool to interface with them.
SELECT text FROM reasonable_sounding_parthian_shot_to_obscure_trolling WHERE id=random();
I call BS. You are in your mom's basement with one eye watching the door. While you construct intricate combinations of self-joins. Until your fingers cramp up. Because you are too scared to even query another table let alone join with it.
by Anonymous Coward
on Thursday July 02, @05:03PM (#28565151)
Seems to be a silly thing to be against. Relational databases and the stuctured query language may not be perfect, but I bet these people could die in their 90's and people will still be using relational dbs and sql.
If you want to tout open or cheap dbs and more lightweight types of storage/db servers, then they might have some points, but being against sql is just plain dumb.
SQL isn't the only way possible to query relational databases. It's nice and does a really good job for even mildly complex queries and I would not want to ditch it just yet, but seriously... who hasn't had a business need for multiple levels of aggregates (eg averages of sums across multiple groupings, say "average across all customers' total balances") As it is, you end up splitting the logic between the database and the application, or creating a view of the first level of aggregation, then querying against that and hoping that the performance doesn't suck total ass.
I agree, there are problems SQL doesn't solve well. But I think it's unlikely that other, better solutions to those problems will also be superior to SQL where it *does* perform well. As such, "no SQL" is probably not the right plan any more than "SQL only".
That is one view. It's nice and all, but incomplete. The issue is performance.
Any time you're dealing with a large quantity of data, it's always easiest to process or filter where it's located. Transmitting it, processing it, and transmitting back changes adds an unreasonable amount of overhead. Hence, SQL is a "Query" language. In other words, you have the RDBMS do reasonable data processing and filtering of records for you. Your application should only need to specify the operations performed, and should only process data if your computation is particularly unusual. This makes feasible computations that would otherwise be entirely unreasonable. (note that an application working on the same machine generally has the same issue as one working on a separate system. SQL servers present the application with a stream of data - pipe, socket, etc)
My opinion: SQL is horrendous. It's a pain to use, and many basic data transforms cannot be described in that language (at least without some huge, awful, convoluted command == maintenance nightmare).
I've seen strong reactions from various camps with regard to concern over saying no to SQL. I'm not sure why people freak out over it. First, you have to strike out toward new things if you want to progress the world. Second, SQL hasn't caused people to stop using spreadsheets or Access databases. Third, there are groups that get together to dispute that the earth is round; insisting that it is flat. Or that gray aliens are visiting earth regularly and probing our anuses.
Bring on the next fascinating data technology. SQL will continue to have a major place for many years to come, no matter what happens.
I've seen strong reactions from various camps with regard to concern over saying no to SQL.. Third, there are groups that get together to dispute that the earth is round; insisting that it is flat.
Corporations represented in this group included the likes of Google, Last.fm, Amazon, and Facebook. Hardly the same caliber of people who claim the earth is still flat. I'm inclined to listen to engineers from these companies if they say that an SQL database does not scale well for vast amounts of data.
And yet where the other corporations; the oil companies, the banks, large merchant conglomerates. In IT we seem to have this sort of myopic view that if it isn't an IT company of some kind, it doesn't exist. Google, as compared to the huge companies that use tools like Oracle, is a bit player. I know that's hard for all of us who have sucked at the teat of silicon valley for so long have a hard time dealing with, but a significant amount of data that has nothing to do with social networking and finding pr0n goes on and does use tools like SQL.
And so you're saying this is all the fault of the relational database, and would all be solved by using some sort of object based database? That's the topic at hand here, not developers dealing with legacy systems patched together.
The whole thing is just reactionary mumbo-jumbo. There are kinds of data that relational databases are fantastic for, and kinds of data they're not, and sometimes none of it is exactly perfect. SQL is actually a pretty damned good, single-purpose language. It's not hard to learn, and once you learn it, the differences between RDBMS implementations becomes a little like Javascript, just something you have to put up with, not that a lot of people actually have to worry all that much about writing fully-por
The problem is the performance of transactions and persistence and distribution of data techniques, not whether we are using a logic-like STRUCTURED QUERY LANGUAGE to ask for data matching certain conditions.
The latter is still, and will continue to be, very useful.
It's just that now that we can assume local clusters and WANs worth of co-operating data stores, there are probably better, more performant ways of implementing persistence, replication, distribution of data than traditional RDBMS implementations.
The two concerns: The logical model of how we QUERY for data (or combine it in bulk), which is the core of SQL, and how we persist it and retrieve it quickly, now have more options for being separated.
It's just that now that we can assume local clusters and WANs worth of co-operating data stores, there
are probably better, more performant ways of implementing persistence, replication, distribution of data
than traditional RDBMS implementations.
You can also assume magical fairy dust and free energy, but that doesn't make it so. You can ask if there are better ways, but you can't assume it, and in the end you will find there is no magic.
Clusters and replication are NOT NEW. Not even remotely new. There is, in fact, nothing new architecturally at all that would indicate some new capability that hasn't already been repeatedly analyzed and tried. That doesn't mean you can't tweak something for a situation, or that you need a giant Oracle database for everything, but "the web" and "cheap hardware" change the equation by precisely nothing.
What has changed the equation is cheap, unimportant data, which covers the majority of the web. "Real" applications, where data integrity is important (like say, your bank account), and immediate accuracy guaranteed, require the main thing you use a database for: data integrity. Your facebook page, your google search, that blog entry, or some video on youtube: these don't matter. If it's a little slow, or doesn't update immediately, or you get an error, no one is losing money. No one cares.
In essence, if a reliable database isn't important for your app, your app isn't really handling important data. This may be fine; in the mainstream, there's a lot of noncritical stuff. But this doesn't make databases unimportant.
It's pretty easy to say "yes" to alternatives without saying "no" to SQL.
Just because a crowbar can pull out a stubborn nail better doesn't mean they should replace all the hammers. Then what would we put nails in with? Different tools for different jobs.
by Anonymous Coward
on Thursday July 02, @05:22PM (#28565371)
If I was to read the article, I bet somewhere someone would be wittering on about Key Value Datastores.
The brainchild of a generation brought up on high level collections, they learn one (in this case Map) and apply it to everything.
Sadly SQL, and RDBMS, works for most people. It maps object data well (oh whaaaa, i have to do foreign keys - GROW SOME FUCKING BALLS YOU LAZY GRADUATE!) and it is well understood. And with abstractions like LINQ to query them, even the lazy dumb Windows.NET programmer doesn't have to strain their brain to learn SQL.
And when you have terabytes of specific unique data, you clearly should go away to work out how best to store it. Even a RDBMS/SQL solution is too generic for all problems.
For me using an RDMS as the persistence layer for an object-oriented application has ALWAYS felt like a bit of a kludge. Like we're using it just because it's what we have, rather than the best tool for the job.
I'm not seeing anything that offers a real advantage over using advanced features like one finds in postgres combined with memcached. Some of my program likes to think of its data as a structured object while other parts like seeing that data as rows in a table (they even link up to other tables through foreign keys!).
Saying no to SQL and relational databases is just fine if you've got something better to replace it with. However I know of no such thing. The reason they're popular is that they are so powerful for data storage. If something better came along you wouldn't even need to say no to SQL. You'd just say yes to the newer better rival.
SQL is not a database, it is a standard interface to a feature set commonly associated with relational models. Before everyone standardized on SQL, there were other relational query languages. The "No" part of "NoSQL" refers to the fact that some basic elements of relational implementations cannot be usefully expressed using a much simpler distributed hash table model.
All the "NoSQL" does is eliminate all the parts of traditional relational databases that do no scale -- discarding the bottleneck rather than fixing it. These are things like joins and external indexing. Unfortunately, discarding those things means you discard a lot of very important functionality as a practical matter, notably the ability to do fast, complex analytics. Adopting the NoSQL architecture runs contrary to the trend toward more real-time, contextual analytical processing. There are a great many analytical applications that are not amenable to batch-mode pattern-matching, and the NoSQL model is a lot less applicable than I think some people want to acknowledge. In its domain, it is a great tool but it has many, many prohibitive limits. We are essentially trading power for scale.
That said, do not take this as an endorsement of traditional SQL relational databases either, as they have a number of serious limitations themselves. As just mentioned, a number of the core analytical operations those models support are based on algorithms that scale poorly. The SQL language itself has mediocre support for many abstract data types (e.g. spatial) and data models (e.g. graph), which in part reflects the inadequacies of the assumed underlying database algorithms (e.g. B-trees) that are implicit in SQL. The inability to efficiently do event-driven/real-time applications is also more a reflection of the access methods used in databases than any intrinsic weakness in SQL; SQL may be clunky for that purpose, but that is not the real limiter.
A truly revolutionary deviation from SQL would usefully implement a superset of the features SQL supports, not take them away. Of course, we would need access methods more capable than hash tables and B-trees to useful implement those features, which is a lot more work than discarding features that scale poorly. NoSQL is a stopgap technical measure for that small subset of applications where the serious tradeoffs are acceptable.
Note that most of these solutions come from the interwebs, social networks, etc. And it isn't so much anti-sql as it is anti-relational database (sql != rdb).
The basic premise is that we need different solutions that: can scale very high for very narrowly scoped reads & writes, don't need to perform ranged queries / reporting/etc, and don't need ACID compliance. And that may be the case. Sites like slashdot, facebook, reddit, digg, etc don't need the data quality that ebay needs.
On the other hand, ebay achieves scalability AND data quality with relational databases. And when I've worked with architectures that scale massively and avoid the relational trap for better solutions - they inevitably later regret the lack of data quality and complete inability to actually get trends and analysis of their data. It *always* goes like this:
Me: So, is this thing (msg type, etc) increasing?
Developer: No idea.
Me: Ok, so lets find out.
Developer: How?
Me: I don't know - typical approach - lets query the database.
Developer: It'll take four+ hours to write & test that query and then days to run. And when it's done we might find that we wrote the query wrong.
Me: What?!?
Developer: We had to do it this way, you can't report on 10TB databases anyhow
Me: What?!? Are you on crack? there are dozens of *100TB* relational databases out there that people are reporting on
Developer: well, we probably don't need to know what that trend is anyhow
Me: I'm outta here
First: my mantra: Data belongs to the organization, not the application... if the app fails and data is accessible then we all go on - if the data fails or is locked away - what was the point of the app again?
In a SQL database then data is understood by the organisation, DBAs and data architects. If left to app developers taking an app-centric approach to data... I get nervous quickly.
So long as the data is just as definable and accessible as current SQL databases then all good - give me an app with some odd-ball storage then it is bye-bye.
Quit Whining (Score:5, Funny)
Re:Quit Whining (Score:4, Insightful)
The horrible lag I get when using address completion in Firefox 3 makes me wish more people thought that way!
Parent
Re:Quit Whining (Score:4, Insightful)
Flat files are a perfectly viable option in some circumstances. Not everything requires data uniformity or the ability to run complex ad-hoc queries, nor does everything need information to be controlled by a separate process running on a different machine. Not every system integrates multiple applications through a shared data-store. The NoSQL crowd isn't arguing that SQL is bad, just overused. There are a great many situations where something like flat files or Berkeley DB is more than sufficient, and yet people still use relational technology. In my experience it's generally because that's all they know. In their mind, if one needs to store data one uses SQL. They don't select the right tool for the job because they honestly don't know there are other tools.
Parent
Re:Quit Whining (Score:5, Insightful)
One of the reasons is because RDBMSs offer a lot of tools, like atomicity, durability, backup/restore, centralization, point-in-time-recovery, etc. Many application developers need these things without actually needing the abstraction of a relational system.
Parent
Re:Quit Whining (Score:4, Funny)
Have you looked near Redwood Regional Park? On the side of a hill?
Parent
A time and place for everything (Score:4, Insightful)
SQL is great for financial data. SQL is terrible for genetic data.
Re:A time and place for everything (Score:4, Insightful)
It would be interesting to hear why this is.
Parent
Re:A time and place for everything (Score:4, Insightful)
Design an efficient table relating a tree structure. Then design queries to answer questions such as:
* Find the nodes in the subtree under B.
* Find all ancesters of G
* Find the nearest common ancestor of D and H
Trees is a wellknown problem of SQL, but the fact is that SQL can't handle most datastructures and complex relations, only very simple one dimensional ones.
Parent
Re:A time and place for everything (Score:5, Interesting)
Sorry, that's not true. Have you tried analytical functions? You would be amazed how complex scenarios can be handled easily with them. And they are part of ANSI SQL standards. And db providers (Oracle etc) have taken the concept and improved a lot on it.
I think the anti-sql 'movement' has more to do with new (internet era) languages and their developers than so called 'lack' of features. In my limited experience, I have observed people coming from C (and such) background have no problem with sql, while java developers (and this is probably true for most developers working on web-based applications) are the worst kind when it comes to understanding even basics of sql. All they want is their objects.
I strongly believe that a competent programmer designing/developing system which includes data and data-storage should at least know normalization, indexes, and what does it mean by 3NF. Programming language is one thing, database is another, and knowledge of both is required to build a decent system.
Parent
Re:A time and place for everything (Score:4, Insightful)
There are certainly common CS themes running between all three. We have three languages not because people haven't thought about those things, but because they make our lives easier.
Whenever I hear people bitching about 'doing away' with SQL, I always wonder what they think is wrong with it. SQL certainly has some limitations, don't get me wrong, but it is a great language for the vast, vast majority of cases. If your application is so specialized that SQL isn't appropriate, well, bravo, but that does not mean that the relational database concept is flawed. Personally, I think if people spent a few moments doing some formal analysis before they built their databases (imagine that, thinking before doing?!), they would find that SQL is a beautiful thing. If your implementation of SQL doesn't cut the mustard, maybe you just need a better query optimizer?
Parent
Re:A time and place for everything (Score:5, Insightful)
The only real show stopper and a real reason to replace RDBMSs is #5. All the others can be worked around by just deeper study of data modeling techniques. Data modelling is not something most developers can figure out intuitively. There is a lot of theory to be learned to do it right and it can very easily be done badly leading to severe performance problems and an unmaintainable application. ,but that lets them get around # 5,
With regards to # 5: I went to a presentation at Javaone where some Ebay engineers explained that they do not use transactions in any of their database operations. They just leave junk rows around in the db if a transaction half completes and as long as they aren't reachable they don't consider it a big deal. They have to very carefully organize the order in which they manipulate data to avoid data corruption
Parent
Re:A time and place for everything (Score:5, Informative)
Design an efficient table relating a tree structure.
Huh? Tree structures are best handled by relational databases, as it is far faster then recursion. Give row a unique ID and a parent ID, and in addition, a left hand and right hand number, the root node having a left-hand value of 1 and a right hand value of (number rows * 2), the first child node has a left-hand value of one more than the parent's, the right-hand value is one less then the left-hand of a younger sibling.
Then design queries to answer questions such as:
* Find the nodes in the subtree under B.
SELECT * FROM rows WHERE left > [left hand value of B] AND right < [right hand value of B]
* Find all ancesters of G
SELECT * FROM rows WHERE left < [left hand value of G] AND right > [right hand value of G]
* Find the nearest common ancestor of D and H
SELECT * FROM rows WHERE left < [lowest left hand value from D,H] AND right > [highest right hand value from D,H] ORDER BY right LIMIT 1
Trees is a wellknown problem of SQL, but the fact is that SQL can't handle most datastructures and complex relations, only very simple one dimensional ones.
Are you saying trees are easy or hard? And for more complex systems, that is what JOINs are for. SQL is by far the most powerful way and often the fastest way to manipulate data that I know of. The only time I can recall that I had to use a non-SQL solution that was faster then the SQL solution was a matrix operation.
Parent
Re:A time and place for everything (Score:4, Informative)
Link 1 [mysql.com]
Link 2 [sitepoint.com]
And parent it right. I was doing an adjacency list in MySQL for a while, because I thought that preorder trees were just a little too complicated, but they are *way* easier and more intuitive.
Parent
Re:A time and place for everything (Score:4, Insightful)
Design an efficient table relating a tree structure. Then design queries to answer questions such as:...
I don't know, but I recall reading that Postgres 8.4 is now out and includes support for recursive queries. [slashdot.org] (trees) Not sure about the reputation of the blog in question, but you may have heard of it?
the fact is that SQL can't handle most datastructures and complex relations, only very simple one dimensional ones
You are kidding, right? Just today I cooked up a 7-table query including 2 subselects, and a left outer join to a meta table consisting of 2 inner joined tables. Total of some 11 tables comprising a highly complex data set. Don't know what you mean by "very simple one dimensional ones" but 11 tables each joined in either a one-to-many or many-to-many mapping provides at least 11 dimensions. (more if you self-join tabls, often needed) And this isn't particularly hard for me - often I have joins combining 12 or more very large tables with unrestrained combinations somewhere in the billions to trillions of possibilities that all somehow seem to parse just a few seconds thanks to a few well-placed indexes and a well-structured query.
Methinks you don't really understand SQL?
Parent
Re: (Score:3, Insightful)
It would be interesting to hear why this is.
My guess would be that because SQL is a Structured Query Language it is best used for handling structured data. If you have serial, unstructured data you have to invent your own format for it to use inside the database, and then the query language isn't helping you.
Re:A time and place for everything (Score:4, Insightful)
Parent
Re:A time and place for everything (Score:4, Interesting)
You've got a bunch of information with one shape, but you really need it in another shape.
The code is about dealing with the embarrassment of it all.
If you've got tabular stuff, and you so very often do, the relational model is fantastic.
If you're dealing with some kind of graph, you're hating life.
People coming in complaining that their aircraft makes a poor submarine are initially amusing, but become tedious.
Parent
Re: (Score:3, Interesting)
Re:A time and place for everything (Score:5, Informative)
SQL is great for financial data.
Actually, this isn't true either. See this article [dbmsmag.com] for pointers to some of the failings of SQL in dealing with financial data, particularly time series (e.g. sales figures, share prices, etc.). Here's another take on the problem [nyu.edu], which essentially is that SQL doesn't recognise that there can be relationships between the rows of a table (e.g., "this happened after this").
Parent
Re:A time and place for everything (Score:4, Informative)
Parent
Re:A time and place for everything (Score:5, Informative)
1) 1996 called, they want their arguments back. For example, most RDBMS have ranking functions now.
2) Even in 1996, he doesn't know SQL worth shit
SELECT (prev.sales+now.sales+next.sales)/3 three_day_average
FROM sales prev,
sales now,
sales next
WHERE prev.day_number = now.day_number-1
AND next.day_number = now.day_number+1
Easy as pie making most of the calculations he wants. Maybe he should ask someone knowledgable in SQL?
Parent
Re:A time and place for everything (Score:4, Insightful)
So what you are saying is that you do not know enough of SQL to understand that query; therefore you are not qualified to comment on the practicality and viability of using SQL for complex structures.
I suspect the same applies to Perl.
-dZ.
Parent
The RDBMS responds to the troll (Score:5, Funny)
See, I don't think there is ever a good time or place for SQL.
SELECT text FROM mild_introductory_statements WHERE id=random();
Anyone who says so has never had to use it.
SELECT text FROM statements_indicating_superior_experience WHERE id=random();
I like to compare it with JavaScript.
SELECT text FROM unrelated_tool WHERE id=random();
It's a language that is difficult to refactor, maintain, and while it's a standard, the standard is so vague that it's useless.
SELECT text FROM seemingly_valid_yet_unsubstantiated_objections WHERE id=random();
Like JavaScript, people are trying to build other languages on top of it to hide its shortcomings -- for javascript you have tools like GWT, and for SQL you have HQL, Linq, etc.
SELECT text FROM wrongheaded_causal_analysis WHERE id=same_one_as_two_queries_ago();
Not to say that there is anything wrong with relational databases, we just lack a good tool to interface with them.
SELECT text FROM reasonable_sounding_parthian_shot_to_obscure_trolling WHERE id=random();
Parent
This is what happens (Score:4, Funny)
When you get a lot of morbidly obese nerds with no life to program for you.
Meanwhile SQL users get laid.
Re:This is what happens (Score:5, Funny)
Parent
Re:This is what happens (Score:4, Funny)
Parent
Don't Like Traditional Relational Databases? (Score:3, Funny)
Tilting at windmills (Score:5, Insightful)
Seems to be a silly thing to be against. Relational databases and the stuctured query language may not be perfect, but I bet these people could die in their 90's and people will still be using relational dbs and sql.
If you want to tout open or cheap dbs and more lightweight types of storage/db servers, then they might have some points, but being against sql is just plain dumb.
Re:Tilting at windmills (Score:5, Insightful)
SQL isn't the only way possible to query relational databases. It's nice and does a really good job for even mildly complex queries and I would not want to ditch it just yet, but seriously... who hasn't had a business need for multiple levels of aggregates (eg averages of sums across multiple groupings, say "average across all customers' total balances") As it is, you end up splitting the logic between the database and the application, or creating a view of the first level of aggregation, then querying against that and hoping that the performance doesn't suck total ass.
Parent
Re: (Score:3, Insightful)
I agree, there are problems SQL doesn't solve well. But I think it's unlikely that other, better solutions to those problems will also be superior to SQL where it *does* perform well. As such, "no SQL" is probably not the right plan any more than "SQL only".
Re:Tilting at windmills (Score:4, Informative)
Parent
RDBMS and application logic (Score:5, Insightful)
That is one view. It's nice and all, but incomplete. The issue is performance.
Any time you're dealing with a large quantity of data, it's always easiest to process or filter where it's located. Transmitting it, processing it, and transmitting back changes adds an unreasonable amount of overhead. Hence, SQL is a "Query" language. In other words, you have the RDBMS do reasonable data processing and filtering of records for you. Your application should only need to specify the operations performed, and should only process data if your computation is particularly unusual. This makes feasible computations that would otherwise be entirely unreasonable. (note that an application working on the same machine generally has the same issue as one working on a separate system. SQL servers present the application with a stream of data - pipe, socket, etc)
My opinion: SQL is horrendous. It's a pain to use, and many basic data transforms cannot be described in that language (at least without some huge, awful, convoluted command == maintenance nightmare).
Parent
Re:Relational good, SQL not so (Score:4, Funny)
While you're here, can you fill in the following form.
I would like my pony to be:
[ ] female
[ ] male, entire
[ ] male, gelded
with coat colour:
[ ] white
[ ] black
[ ] brown
[ ] piebald
[ ] other, please specify _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Parent
Flat Earth (Score:3, Insightful)
I've seen strong reactions from various camps with regard to concern over saying no to SQL. I'm not sure why people freak out over it. First, you have to strike out toward new things if you want to progress the world. Second, SQL hasn't caused people to stop using spreadsheets or Access databases. Third, there are groups that get together to dispute that the earth is round; insisting that it is flat. Or that gray aliens are visiting earth regularly and probing our anuses.
Bring on the next fascinating data technology. SQL will continue to have a major place for many years to come, no matter what happens.
Re:Flat Earth (Score:4, Interesting)
I've seen strong reactions from various camps with regard to concern over saying no to SQL.. Third, there are groups that get together to dispute that the earth is round; insisting that it is flat.
Corporations represented in this group included the likes of Google, Last.fm, Amazon, and Facebook. Hardly the same caliber of people who claim the earth is still flat. I'm inclined to listen to engineers from these companies if they say that an SQL database does not scale well for vast amounts of data.
Parent
Re:Flat Earth (Score:5, Insightful)
And yet where the other corporations; the oil companies, the banks, large merchant conglomerates. In IT we seem to have this sort of myopic view that if it isn't an IT company of some kind, it doesn't exist. Google, as compared to the huge companies that use tools like Oracle, is a bit player. I know that's hard for all of us who have sucked at the teat of silicon valley for so long have a hard time dealing with, but a significant amount of data that has nothing to do with social networking and finding pr0n goes on and does use tools like SQL.
Parent
Re:Flat Earth (Score:4, Informative)
And so you're saying this is all the fault of the relational database, and would all be solved by using some sort of object based database? That's the topic at hand here, not developers dealing with legacy systems patched together.
Parent
Re: (Score:3, Interesting)
The whole thing is just reactionary mumbo-jumbo. There are kinds of data that relational databases are fantastic for, and kinds of data they're not, and sometimes none of it is exactly perfect. SQL is actually a pretty damned good, single-purpose language. It's not hard to learn, and once you learn it, the differences between RDBMS implementations becomes a little like Javascript, just something you have to put up with, not that a lot of people actually have to worry all that much about writing fully-por
Re: (Score:3, Funny)
You're not going to get many page hits with an attitude like that...
The problem is performance not SQL (Score:4, Interesting)
The problem is the performance of transactions and persistence and distribution of data techniques, not
whether we are using a logic-like STRUCTURED QUERY LANGUAGE to ask for data matching certain conditions.
The latter is still, and will continue to be, very useful.
It's just that now that we can assume local clusters and WANs worth of co-operating data stores, there
are probably better, more performant ways of implementing persistence, replication, distribution of data
than traditional RDBMS implementations.
The two concerns: The logical model of how we QUERY for data (or combine it in bulk), which is the core of SQL,
and how we persist it and retrieve it quickly, now have more options for being separated.
Re:The problem is performance not SQL (Score:5, Insightful)
It's just that now that we can assume local clusters and WANs worth of co-operating data stores, there are probably better, more performant ways of implementing persistence, replication, distribution of data than traditional RDBMS implementations.
You can also assume magical fairy dust and free energy, but that doesn't make it so. You can ask if there are better ways, but you can't assume it, and in the end you will find there is no magic.
Clusters and replication are NOT NEW. Not even remotely new. There is, in fact, nothing new architecturally at all that would indicate some new capability that hasn't already been repeatedly analyzed and tried. That doesn't mean you can't tweak something for a situation, or that you need a giant Oracle database for everything, but "the web" and "cheap hardware" change the equation by precisely nothing.
What has changed the equation is cheap, unimportant data, which covers the majority of the web. "Real" applications, where data integrity is important (like say, your bank account), and immediate accuracy guaranteed, require the main thing you use a database for: data integrity. Your facebook page, your google search, that blog entry, or some video on youtube: these don't matter. If it's a little slow, or doesn't update immediately, or you get an error, no one is losing money. No one cares.
In essence, if a reliable database isn't important for your app, your app isn't really handling important data. This may be fine; in the mainstream, there's a lot of noncritical stuff. But this doesn't make databases unimportant.
Parent
Not mutually exclusive (Score:3, Insightful)
Just because a crowbar can pull out a stubborn nail better doesn't mean they should replace all the hammers. Then what would we put nails in with? Different tools for different jobs.
Yeah, so why are they better? (Score:5, Insightful)
If I was to read the article, I bet somewhere someone would be wittering on about Key Value Datastores.
The brainchild of a generation brought up on high level collections, they learn one (in this case Map) and apply it to everything.
Sadly SQL, and RDBMS, works for most people. It maps object data well (oh whaaaa, i have to do foreign keys - GROW SOME FUCKING BALLS YOU LAZY GRADUATE!) and it is well understood. And with abstractions like LINQ to query them, even the lazy dumb Windows .NET programmer doesn't have to strain their brain to learn SQL.
And when you have terabytes of specific unique data, you clearly should go away to work out how best to store it. Even a RDBMS/SQL solution is too generic for all problems.
Re:Yeah, so why are they better? (Score:4, Insightful)
Saying RDMS's map object data well is a bit of a stretch, they map relational data well and that's it.
http://www.codinghorror.com/blog/archives/000621.html [codinghorror.com] for some good background on the problems.
For me using an RDMS as the persistence layer for an object-oriented application has ALWAYS felt like a bit of a kludge. Like we're using it just because it's what we have, rather than the best tool for the job.
Parent
What's the benefit exactly? (Score:4, Insightful)
I'm not seeing anything that offers a real advantage over using advanced features like one finds in postgres combined with memcached. Some of my program likes to think of its data as a structured object while other parts like seeing that data as rows in a table (they even link up to other tables through foreign keys!).
How about saying yes to the alternative (Score:5, Insightful)
Saying no to SQL and relational databases is just fine if you've got something better to replace it with. However I know of no such thing. The reason they're popular is that they are so powerful for data storage. If something better came along you wouldn't even need to say no to SQL. You'd just say yes to the newer better rival.
SQL is not a database (Score:5, Insightful)
SQL is not a database, it is a standard interface to a feature set commonly associated with relational models. Before everyone standardized on SQL, there were other relational query languages. The "No" part of "NoSQL" refers to the fact that some basic elements of relational implementations cannot be usefully expressed using a much simpler distributed hash table model.
All the "NoSQL" does is eliminate all the parts of traditional relational databases that do no scale -- discarding the bottleneck rather than fixing it. These are things like joins and external indexing. Unfortunately, discarding those things means you discard a lot of very important functionality as a practical matter, notably the ability to do fast, complex analytics. Adopting the NoSQL architecture runs contrary to the trend toward more real-time, contextual analytical processing. There are a great many analytical applications that are not amenable to batch-mode pattern-matching, and the NoSQL model is a lot less applicable than I think some people want to acknowledge. In its domain, it is a great tool but it has many, many prohibitive limits. We are essentially trading power for scale.
That said, do not take this as an endorsement of traditional SQL relational databases either, as they have a number of serious limitations themselves. As just mentioned, a number of the core analytical operations those models support are based on algorithms that scale poorly. The SQL language itself has mediocre support for many abstract data types (e.g. spatial) and data models (e.g. graph), which in part reflects the inadequacies of the assumed underlying database algorithms (e.g. B-trees) that are implicit in SQL. The inability to efficiently do event-driven/real-time applications is also more a reflection of the access methods used in databases than any intrinsic weakness in SQL; SQL may be clunky for that purpose, but that is not the real limiter.
A truly revolutionary deviation from SQL would usefully implement a superset of the features SQL supports, not take them away. Of course, we would need access methods more capable than hash tables and B-trees to useful implement those features, which is a lot more work than discarding features that scale poorly. NoSQL is a stopgap technical measure for that small subset of applications where the serious tradeoffs are acceptable.
Pros & Cons of non-relational solutions (Score:5, Interesting)
Note that most of these solutions come from the interwebs, social networks, etc. And it isn't so much anti-sql as it is anti-relational database (sql != rdb).
The basic premise is that we need different solutions that: can scale very high for very narrowly scoped reads & writes, don't need to perform ranged queries / reporting /etc, and don't need ACID compliance. And that may be the case. Sites like slashdot, facebook, reddit, digg, etc don't need the data quality that ebay needs.
On the other hand, ebay achieves scalability AND data quality with relational databases. And when I've worked with architectures that scale massively and avoid the relational trap for better solutions - they inevitably later regret the lack of data quality and complete inability to actually get trends and analysis of their data. It *always* goes like this:
Me: So, is this thing (msg type, etc) increasing?
Developer: No idea.
Me: Ok, so lets find out.
Developer: How?
Me: I don't know - typical approach - lets query the database.
Developer: It'll take four+ hours to write & test that query and then days to run. And when it's done we might find that we wrote the query wrong.
Me: What?!?
Developer: We had to do it this way, you can't report on 10TB databases anyhow
Me: What?!? Are you on crack? there are dozens of *100TB* relational databases out there that people are reporting on
Developer: well, we probably don't need to know what that trend is anyhow
Me: I'm outta here
Data out-lives applications (Score:5, Insightful)
First: my mantra: Data belongs to the organization, not the application... if the app fails and data is accessible then we all go on - if the data fails or is locked away - what was the point of the app again?
In a SQL database then data is understood by the organisation, DBAs and data architects. If left to app developers taking an app-centric approach to data... I get nervous quickly.
So long as the data is just as definable and accessible as current SQL databases then all good - give me an app with some odd-ball storage then it is bye-bye.
I don't understand (Score:5, Funny)
So a bunch of Excel users got together for dinner in San Francisco - why is this news?