News for hackers. Stuff that matters.
Re:SQL Databases Are An Overapplied Solution (And What To Us... by viraptor on Wednesday February 17, 2010 @08:10PM (#1133141)
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.filter(type='stock_widget', part_id=453)['amount']
    -vs-
    SELECT amount FROM storage WHERE id = 'widget 453'
Most popular:

    for r in db.filter(type='item', date=xxx): histogram[r['part_id']] += 1
    histogram.sort_value()[0].key()
What I wanted to show is - you're writing the same amount of code for both cases. In some databases (like Tyrant) you can also run the script server-side and just report the result if you prefer. Also depending on the database, you don't need to read the whole record every time - you can just request a list of fields in most of them.

Have some fun with a NoSQL database before rejecting it for reasons like the ones you mentioned... It's also not always about processing speed - I could use either solution, but coding for TT is just simpler than for any SQL in most of what I do (see how my db.filter examples give you the solution in the current language, but queries are just... queries that you have to run and retrieve results (I'm ignoring SQL-LINQ now)).

Re:SQL Databases Are An Overapplied Solution (And What To Us... by wvenable on Wednesday February 17, 2010 @10:41PM (#1133334)
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 for API, you can hide the SQL pretty well behind an abstraction as well. But there is an advantage to using SQL itself -- the database engine analyzes your query and produces the most optimal way to get the answer you want. You're choosing to write poorly optimized code instead.

Re:SQL Databases Are An Overapplied Solution (And What To Us... by smcq on Thursday February 18, 2010 @04:28AM (#1133784)
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 advantage, not a theoretical advantage.

Re:SQL Databases Are An Overapplied Solution (And What To Us... by wvenable on Thursday February 18, 2010 @10:24AM (#1134302)
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 Us... by viraptor on Thursday February 18, 2010 @08:49AM (#1134093)
> 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 Us... by wvenable on Thursday February 18, 2010 @10:21AM (#1134298)
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 Us... by viraptor on Thursday February 18, 2010 @12:42PM (#1134729)
The only thing you lose by adding indexes is insert speed. Indexes in most document stores don't change anything apart from shifting the get/put time balance. They don't define anything and can be often created / dropped / recreated without affecting the whole application.

As mentioned before - I could put the "most popular" script on the server and request only the result if you wanted. TT can do that for sure and I remember reading about another solution that may (redis I think? I may be wrong here).

About the normalised or not data - you have to design your data storage to your solution and needs. If you made something hard to query, maybe it's time to redesign it, or add another denormalised storage, or keep aggregate copies, or use some other solutions... Same applies to rdbmses really.

You know how to run your queries on your DB efficiently (or you should start learning now). Sure - exploratory searching for patterns, custom reports, etc. - that's where SQL is great. If you need it, either you should have additional "archives" in some better data store (sql-capable column-based store?), or simply use rdbms for everything. Over-simplification is as bad as over-generalising ;) just use what you need, instead of what is popular.

Re:SQL Databases Are An Overapplied Solution (And What To Us... by wvenable on Thursday February 18, 2010 @01:27PM (#1134846)
> About the normalised or not data - you have to design your data storage to your solution and needs.

I understand your point. But once you have normalized data, don't you lose most/all the advantages of a NoSQL solution? RDBMS's are designed to handle the searching, joining, and filtering of normalized data -- NoSQL solutions are not.

My original comment about "lack of imagination" applies -- If you're designed your data to handle as many scenarios as possible (which obviously the author of the article did not) then you will end up with something that is fully normalized.

If you need performance, whether it be in an RDBMS or not, you'll need to sacrifice some flexibility. But it seems like with NoSQL you're prematurely optimizing -- picking a solution with a very specific use-case when you're most likely not going to need any of the supposed advantages.

I'm not suggesting that NoSQL doesn't have it's use, but most of these articles advocate using it by default and in situations where it's clearly not appropriate. The author of this article, using it for e-commerce orders, seems like a very inappropriate use to me.

Re:SQL Databases Are An Overapplied Solution (And What To Us... by wvenable on Thursday February 18, 2010 @11:18AM (#1134470)
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 Us... by jawngee on Thursday February 18, 2010 @02:24AM (#1133640)
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 access.

Re:SQL Databases Are An Overapplied Solution (And What To Us... by viraptor on Thursday February 18, 2010 @09:30AM (#1134167)
"... 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 Us... by smcq on Thursday February 18, 2010 @04:34AM (#1133795)
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.
Re:SQL Databases Are An Overapplied Solution (And What To Us... by wvenable on Thursday February 18, 2010 @10:30AM (#1134322)
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 Us... by smcq on Thursday February 18, 2010 @11:27AM (#1134511)
Look, I'm not going to argue with you because you have no idea what you're talking about. Go away.