I disagree with the comment "SQL databases don't scale". If you've got a decent database architect, scalability (not capacity), stops being an issue.
SQL Databases Are An Overapplied Solution (And What To Use Instead)
51–60 of 67 posts
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#52Earlier quoted context omitted.
Did you use any NoSQL solution seriously? Let's see some examples (imaginary python-like interface, so I don't have to be language/backend-specific). Like you say: Start writing code. Do you want to know how much you made in sales today? sum(amount in db.filter(type='order', date=xxx)) -vs- SELECT SUM(amount) FROM order WHERE date=? How many of widget #453 are still in stock? (IRL it's never that simple, but...) db.f…
The question is what is this doing: sum(amount in db.filter(type='order', date=xxx)) If you're iterating every order in the database to apply your filter or to calculate the most popular items then you wasting a huge amount of processing power and RAM for something an RDBMS can do very efficiently. It's not an advantage that your incrementing values in your programming language of choice either, it's a disaster. As f…
> If you're iterating every order in the database to apply your filter
I'll stop you there - filters can use database indexes. Exactly the same way SQL databases do it. Database driver chooses how it fetches those results. Getting the records based on some criteria and indexed attributes does not iterate through every record.
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#53Earlier quoted context omitted.
I'm not sure what you mean by, "it just pushes the problem down into the filesystem". Could you explain? On the upside, SQLite will take disk space, memory, and CPU proportional to actual usage, and compared to (say) Ruby or Python, it's a drop in the bucket. As to the discontinuous transition between it and a bigger system, sure. It's a trade-off. So is worrying about scalability plans during early prototyping, thou…
If you're building a multitenant distributed system like GAE, the SQLite blob needs to be stored in a distributed system too, even if you don't need to handle concurrent access.
Got it. SQLite also explicitly notes that its locking does not work properly with many broken implementations of NFS (http://sqlite.org/faq.html#q5), so that's not an option.
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#54Earlier quoted context omitted.
Did you use any NoSQL solution seriously? Let's see some examples (imaginary python-like interface, so I don't have to be language/backend-specific). Like you say: Start writing code. Do you want to know how much you made in sales today? sum(amount in db.filter(type='order', date=xxx)) -vs- SELECT SUM(amount) FROM order WHERE date=? How many of widget #453 are still in stock? (IRL it's never that simple, but...) db.f…
Ok, so write me a query that shows me which orders were placed for a specific widget within a date range for a customer belonging to a particular company where the payment is still being processed. You say it yourself: (IRL it's never that simple, but...) What's the but? And I'm still not certain how different applications access a NoSQL store without clobbering each other, where RDBMS systems enforce regulated acces…
"... but I'll show a trivial example, because methods of orders processing and querying could take a whole blog post."
The key to such query is using the correct data representation, and the query itself is no different to the previous one: condition and condition and [...] You can solve the date + payment being processed by first getting a list of orders satisfying those conditions and then checking which of them have the widget you need. That's still only 2 queries (if the db can handle multi-key get). It's not slow/complicated/whatever and it's more or less what your typical RDBMS will do. How often do you write complicated new queries IRL?
Stopping document stores from clobbering each other? Reader/writer locks if you're working on a local store. Or setting up an arbitrating db access manager if you're working on a remote one. Exactly the same as SQL servers (SQLite and any RDBMS respectively)
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#55Earlier quoted context omitted.
Which boils down to "relational databases are easier for non programmers to use", but I'm a programmer, I don't care. OODB's allow me to write applications much faster, and bugs affect both kinds of databases equally. Data corruption happens just as much in relational databases. I've used both, various relational db's for more than 10 years. I'm well aware of their strengths and weaknesses. Given the choice, I'll tak…
From your profile i can see that your a smalltalk fan. Well, Alan Kay was onced asked where he thought the future of programming languages lies and he replied something along the lines of knowledge representation/logic programming. guess what - relational databases are based on the first order logic, which is the most common method for representing 'knowledge', look at prolog or opencyc. When you write sql you're act…
The future may very well lie there, the present most certainly does not. Above all else I'm pragmatic and no matter how pretty SQL might be for getting the data you want, even if I ignore that it isn't actually relational, it simply doesn't fit well with the rest of the environment necessary to put a working application in front of a user today.
While I admire Alan Kay greatly, he's always been a bit of a dreamer. Dreams don't pay my bills, but object databases most certainly make me more productive, make my apps better, and my job much more enjoyable; SQL however, despite the fact that I know it very well, is always an annoyance that makes any project take long and require much more code than would otherwise be necessary.
As for Greenspun, everyone, no matter how famous or who taught them, is wrong about many things. Look at this website, we're having this discussion on a very popular site which uses a home-brew object database precisely because for many such sites a relational database is simply unnecessary and massive overkill and would create as many problems as it would solve. Relational database have their place, but they are a massively overused and over-applied solution. No blog, small website, or small biz appliction needs a relational database, they'd all be much simpler and work just as well with an OODB and quite frankly be faster and cheaper to develop.
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#56Earlier quoted context omitted.
The question is what is this doing: sum(amount in db.filter(type='order', date=xxx)) If you're iterating every order in the database to apply your filter or to calculate the most popular items then you wasting a huge amount of processing power and RAM for something an RDBMS can do very efficiently. It's not an advantage that your incrementing values in your programming language of choice either, it's a disaster. As f…
> If you're iterating every order in the database to apply your filter I'll stop you there - filters can use database indexes. Exactly the same way SQL databases do it. Database driver chooses how it fetches those results. Getting the records based on some criteria and indexed attributes does not iterate through every record.
Yes, I did assume that was the case. However, once you've started adding indexes aren't you already losing some of the advantage of NoSQL. An index is a well defined set of columns.
In your "Most popular" example, you're still iterating records locally (and sorting locally) to calculate the results rather than having it done on the server. You've also presupposed that you have normalized data -- which in authors example, you do not.
My point about having the RDBMS optimize your query still stands. Your code and your SQL statements are not the same -- the SQL is a description of the result but your code is the actual process. And you've over-simplified the process; in reality it would be much more complicated.
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#57Earlier quoted context omitted.
The question is what is this doing: sum(amount in db.filter(type='order', date=xxx)) If you're iterating every order in the database to apply your filter or to calculate the most popular items then you wasting a huge amount of processing power and RAM for something an RDBMS can do very efficiently. It's not an advantage that your incrementing values in your programming language of choice either, it's a disaster. As f…
You don't seem to understand how stored views work. This computation cost is felt at insertion time and is not theoretically worse than the exact same computation cost at insertion time in a rdbms to build almost identical index structures. What current implementations of rdbms's gain you is the ability to write completely ad-hoc queries and get reasonable performance most of the time. This is an implementation advan…
I understand that you can have indexes -- which, in my opinion, seems to defeat the purpose of NoSQL in the first place. You take your unstructured data store and structure it.
Also, in the authors example the items are properties of the order. How exactly would you index on those?
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#58Earlier quoted context omitted.
Ok, so write me a query that shows me which orders were placed for a specific widget within a date range for a customer belonging to a particular company where the payment is still being processed. You say it yourself: (IRL it's never that simple, but...) What's the but? And I'm still not certain how different applications access a NoSQL store without clobbering each other, where RDBMS systems enforce regulated acces…
Do the same thing you would do in SQL, run the efficient query (you're almost guaranteed have a view for date limited for a specific customer) then filter it based on product. This is exactly what your RDBMS does, and I don't see how you're gaining anything except a different language.
2 words: Query Optimizer
http://en.wikipedia.org/wiki/Query_optimizer
If you think you're doing exactly that an RDBMS does, you're kidding yourself. Even if you do, you're still wasting effort re-coding something that already exists.
The fact is, running the query on the order and filtering on the product may not be the most efficient way of getting the data. It might be faster query on the product and then filter based on the order. RDBMS's make those sorts of decisions all the time.
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#59There's definitely a lack of imagination when it comes to proponents of NoSQL solutions, and this article shows no exception. While we do want to store, for example, an entire e-commerce order in a single operation we also don't necessarily want to retrieve it that way. That type of storage makes otherwise simple operations considerably more difficult. Do you want to know how much you made in sales today? How many of…
Did you use any NoSQL solution seriously? Let's see some examples (imaginary python-like interface, so I don't have to be language/backend-specific). Like you say: Start writing code. Do you want to know how much you made in sales today? sum(amount in db.filter(type='order', date=xxx)) -vs- SELECT SUM(amount) FROM order WHERE date=? How many of widget #453 are still in stock? (IRL it's never that simple, but...) db.f…
I realized that most of your examples here have a flaw. They assume you've got a normalized data store. But in the authors example, that isn't the case. The items in the order are properties of the order.
So, for example, your most popular query is wrong. You have to iterate on the orders and then iterate on the items. I doubt you can use an index for that. And you have to fetch the orders when you really only wanted the items.
I was going for the same sort of thing with the number of items still in stock. A better example might be asking how much of a particular widget has been purchased.
Re: SQL Databases Are An Overapplied Solution (And What To Use Instead)
#60Earlier quoted context omitted.
Do the same thing you would do in SQL, run the efficient query (you're almost guaranteed have a view for date limited for a specific customer) then filter it based on product. This is exactly what your RDBMS does, and I don't see how you're gaining anything except a different language.
2 words: Query Optimizer http://en.wikipedia.org/wiki/Query_optimizer If you think you're doing exactly that an RDBMS does, you're kidding yourself. Even if you do, you're still wasting effort re-coding something that already exists. The fact is, running the query on the order and filtering on the product may not be the most efficient way of getting the data. It might be faster query on the product and then filter ba…
Look, I'm not going to argue with you because you have no idea what you're talking about. Go away.