12

There has been a lot of talk recently about NoSQL.

The #1 reason why I hear people use NoSQL is because they start to de-normalize their DBMS data so much so, to increase performance, that they end up with just one table with all of their data within that single table.

With Materialized Views however, you can keep your data normalized, yet have it stored as a single table view for the same reasons why you'd use NoSQL.

As such, why would someone use NoSQL over Materialized Views?

JustinT
  • 2,481
  • 3
  • 18
  • 11
  • I thought the real rationale behind NoSql was the fact that SQL does not scale to > petabytes of data. The lack of normalization, and of advanced sql features in general (such as (coff) guaranteed consistency), is a result of, and more a side effect of, the distributed architecture, not an actual design goal. – Chris Becke Apr 09 '10 at 14:10

3 Answers3

6

One reason is that materialized views will perform poorly in an OLTP situation where there is a heavy amount of INSERTs vs. SELECTs.

Everytime data is inserted the materialized views indexes must be updated, which not only slows down inserts but selects as well. The primary reason for using NoSQL is performance. By being basically a hash-key store, you get insanely fast reads/writes, at the cost of less control over constraints, which typically must be done at the application layer.

So, while materialized views may help reads, they do nothing to speed up writes.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • That is incorrect. Oracle states that Materialized Views greatly increases performance. http://www.oracle.com/technology/products/oracle9i/daily/jul05.html – JustinT Apr 09 '10 at 13:49
  • 2
    @JustinT: Of course their marketing will say that. The reality is that in some cases (read-heavy situations) they are great. In write-heavy situations, they are not great, which is the point I was making by specifying OLTP, above. – D'Arcy Rittich Apr 09 '10 at 13:53
  • Greatly Increases performance compared to what? "Oracle9i significantly improves the functionality of materialized views"..compared to how slow they were before. Not compared to NoSQL style architectures. – NebuSoft Apr 09 '10 at 13:53
  • your original post simply said "One reason is that materialized views will perform poorly in an OLTP-type situation." And nothing about write specific performance. If the application is read-heavy, your statement would be incorrect. – JustinT Apr 09 '10 at 13:54
  • 2
    @Justin - OLTP to me implies heavy writes. I will update the post to be explicit. – D'Arcy Rittich Apr 09 '10 at 13:57
4

NoSQL is not about getting better performance out of your SQL database. It is about considering options other than the default SQL storage when there is no particular reason for the data to be in SQL at all.

If you have an established SQL Database with a well designed schema and your only new requirement is improved performance, adding indexes and views is definitely the right approach.

If you need to save a user profile object that you know will only ever need to be accessed by its key, SQL may not be the best option - you gain nothing from a system with all sorts of query functionality you won't use, but being able to leave out the ORM layer while improving the performance of the queries you will be using is quite valuable.

Tom Clarkson
  • 16,074
  • 2
  • 43
  • 51
  • 2
    Data cannot be 'in SQL'. Did you mean 'in relations'? There is a difference to departing from the SQL language and departing from the relational model, although 'NoSQL' compounds the two. Note also that indexes and views will help little in a heavy insert/update environment. – beldaz Sep 17 '12 at 04:15
0

Another reason is the dynamic nature of NoSQL. Each view you create will need created before-hand and a "guess" as to how an application might use it.

With NoSQL you can change as the data changes; dynamically varying your data to suit the application.

CKuharski
  • 314
  • 3
  • 14