Edit: This question is about how to deal with many issues that arose from a overall system design, which made parts of the system deviate from common standards. For example, managing everything in the business model with own program code, even down to relational integrity. This gives the database and persistence layer a smell of bad design, by using it as a place to "dump something in and get it out again somehow", rather than a structured storage. I asked the question, because NoSQL document storages seem to me like an option to move an already schema-less (or very loose schema) database to something without schema by default. Also, I must note that the whole system is not a bad one at all, despite some of the flaws described here. Also, some of the problems, such as versioning, have solutions on the way or already implemented.

Think of a software system you look at, based on classic, relational databases (SQL Server, Oracle), NHibernate as object-relational mapper (ORM), a business logic model layer on top and a huge number of modules (a few 100), mainly .NET based services and a few web services (with clients, max. ~100 per system/customer, company network, non-public). Operation style is mainly OLTP, with write/CUD access being an importand part of the workload. Productive databases are usually some 10GB, but always well below 100GB in size (thus no "big data"). It does it's work well, but to me, the database and ORM implementation smell of several anti-patterns (for relational databases). Maybe these implementations can be better with another kind of database – document oriented ("NoSQL"), or in-memory database.

  1. Many relational database and supporting ORM features omitted: Tables are strongly denormalized, foreign key relations missing or impossible, e.g. due to metadata tables referencing different main tables, with columns like IdInTable INT, OwnerTable INT. NHibernate has mostly no object relations mapped (and has generally problems with table structures it is not made for). Instead, these are implemented in business logic (sometimes leading to orphaned child objects or inefficient database access, see below).

  2. Denormalization down below the basics: Increasing use of non-1st NF data: nclob/nvarchar(max) columns with XML, comma separated lists, or composite numeric value columns (e.g. 123, 10123, 40123 for task type 123, but different module configs identified by 0,1,4 * 10000). The first two containing database relevant, logical "Foreign Keys" and data model relevant values, such as <UserType>AdminUser</UserType> (to be checked with LIKE '%...%'). This is mainly due to many quick-to-release, short lived and customized values which shouldn't go into the main schema or are easier to implement through XML values.

  3. Non-2nd NF data, including table contents being copied by triggers, followup stored procedures or applications into other tables. E.g., a table column value copied to a "vertical" metadata table, this again copied to a "horizontal" or "pivoted" representation of the metadata (each metadata type a column), because some applications can only use the metadata or horizontal metadata. Frequent requests to use "rubbish bin structures" (dump collected data from various sources into one nclob/nvarchar(max) "rubbish bin" column and let an application search through it, instead of many different sources).

  4. The "One-Object-Disease" in business logic model and applications: Iterating and immediate load/save of single objects: The business layer uses mainly Load/Save() methods for individual objects and few bulk/set based operations. A common job is to get object IDs by SQL or it's NHibernate representations, then iterate over all retrieved Ids and fetch the objects one by one in the style of foreach (oneId in Ids) { myObjects.Add( BizModel.GetMyObjectById(oneId) ); }. This with all metadata, dependent objects collections etc., a classic SELECT N+1 situation. Furthermore, most of NHibernate's caching, persistence ignorance and combined operations have been forcilby disabled: Loading one object explicitly calls SELECT FROM MyObject WHERE Id=:id to prevent using the cache or deferred execution, but get a fresh object from the current DB row. MyObject.Save() is implemented to enforce immediate Insert/Update: NHibernate session.Save(...) followed by immediate .Flush(). The whole thing uses NHibernate micro-sessions: loaded objects are immediately taken out of session context and saved within a new session (preventing those "weird", undesired changes of unsaved objects in DB). Persistence ignorance and object relations through NHibernate seem undesirable, to keep control of the state of every single object. NHibernate is really considered a mapper (one row to one object) rather than a complex tool for relational database access. There's also debate about using a "fast" micro-ORM instead of NHibernate, which will materialize SELECT N+1 queries lightning fast into objects, but of course do nothing against N+1 itself.

  5. An important requirement is to get everyting working with everything, because it is too much to release all modules for every single change: a new module must work with an old database version, where certain columns and tables don't exit, and an old module must still work with a new DB version, with columns etc. added. This leads to new columns having default values if not nullable, and old tables/columns, abandoned for a long time, to be still in the data model, because removal might crash old modules. Another consequence is reluctance to add new tables/columns, which you can hardly get rid of, once released. Instead, XML (in text columns) and similar, denormalized stuff, or property values in the global metadata table, are preferred.

  6. Many modules receive tasks just for single objects, which would be no problem within a possible, set based approach, since a set/bulk data access method could also handle a single object/row, if needed so. On the other hand, there are the web servers, maintenance and background services, which handle many objects at once, need the business logic and run very inefficient in the current single-object way (the webservice using native SQL, or, newly, a Lucene based search engine to search the IDs of desired objects, but retrieving the full model objects one by one).

Imagine, you've tried to change this. In the beginning, you didn't know NHibernate and how it works, but then you came up with ideas how to adapt data access to it's real abilities, and avoid unneccessary database operations: map relations in NHibernate, keep sessions and transactions open for several object operations, do set/bulk operations, normalize the DB the way you've learned years ago, add foreign keys, views, maybe Materialized Views to it. But you keep being rejected, with arguments like: "nobody is going to pay for it", "the database can handle it, no matter how 'bad' the application is", and simply "it works". Disk space, memory, CPU power and network resources are cheap; refactoring data access would be much more expensive. Likely, standing by the code programmer's object oriented approach, rather than the DB programmer's set based approach, is preferred (including it's enforcement against the ORM implementation). What does it matter if the system could be 10 or even a 100 times faster, if it works sufficiently in the current way? Don't care about SELECT N+1 anyway, today's databases can handle it! That would only be gold plating! It might become different when databases grow into terabytes, but that's not for now.

So, maybe, there is a solution in the "NoSQL" or "NewSQL" area. It might be possible to fetch objects from and store them in a database in a fast and efficient way. Even with many queries in a single object, rather than set approach, as long as it is a local DB without long distance latency. It looks like the current system uses the relational database just as an extended, persistent main memory, and all those "stone age relicts" of IT, like creating and maintaining tables and indices manually, or mapping objects to relational tables, just add a huge overhead.

My idea is:

A "NoSQL" document database is a good thing, because:

  • the document mostly contains the whole object graph with dependent items, metadata and everything belonging to it, so it doesn't require additional DB queries, thus avoiding or greatly reducing the SELECT N+1 problem.
  • within a document, there's implicit "relational integrity" through dependent objects being contained by their parents (nested in XML or JSON representation).
  • over multiple different documents, there are no relations in database, these are solely maintained by the business logic (as it is done now frequently, but wrong for a classic relational DB design).
  • it has usually no fixed schema, so it is much easier to work with with changing data structures. Objects can ignore properties added later, or fill missing values from older version data with defaults.
  • Subsequent data with external/variable/no schema can be integrated without break (as opposed to storing XML in relational text columns).
  • many document DB have auto indexing or search engines integrated.

A minimum of automated data integrity, especially multi-object transactions, is still required.

An in-memory relational database, or any focused on fast access without the need to access slow hard drives with every (write) operation, would help with speed, but still basically rely on a hard relational schema, which has yet been largely omitted and seems to be undesirable to the stakeholders.

Can anybody with experience tell me if my assumptions are correct?

  • 5
    The title could well be "Db designer's nightmare. Way to escape?" – ypercubeᵀᴹ Sep 21 '14 at 10:02
  • 2
    "This relational database is poorly designed" is not a good argument for changing technologies. As the manager making the decision, I'd ask for a list of the current issues, how they are affecting the company, suggestions on how to correct them, and how much it will cost to do so. Once we agree that the issues are insurmountable, we could have a discussion about the pros and cons of rewriting in RDB versus NoSQL. – Tony Ennis Sep 21 '14 at 16:02

Welcome to database hell!

NoSQL is often championed as a solution to these types of applications. However, your problem here is clearly that the programmers have no idea what they are doing. Furthermore, it looks like your management is afraid of change, or unwilling to take the hard decisions which you correctly list and which are likely to fix the root cause. It is highly uncertain that replacing the database with another database helps here. Don't blame the tool, blame the craftsman.

While it may be true that every object in a NoSQL CAN contain the entire object graph, you are still stuck with how to keep all the data up to date with proper integrity. Incidentally, this is exactly what 3NF was made to solve, which it solves beautifully in all properly designed systems out there. Once you maintain redundant data (no matter if in SQL or NoSQL), you run into all sorts of problem with locking and latching to keep things in sync.

The dynamic schema nature of NoSQL is only an advantage if the schema changes very frequently. From your description of the system, it does not sound like this is the case, because nobody dares to touch the code. In addition, schema changes in a relational database is not THAT hard. The NoSQL championed advantages of a dynamic schema are largely a solution looking for a problem that just doesn't exist for a DBA who knows what he is doing.

Even if you could harvest some theoretical benefit of NoSQL, you still have to think about how to migrate from where you are without running into all the same arguments you are currently facing. The cost of comparing the two approaches would likely be prohibitive in an environment that is not open to radical change.

I fully understand why you are grappling for solutions here. However, there are plenty of great jobs out in IT. Life is too short to deal with this type of system and organisation. Unless you need a lesson in patience and in the endurance of pain, just move on.

| improve this answer | |
  • 4
    +1 Especially for the comments about moving on. I'd add that on NoSQL someone will also have the headaches and expenses of trying to back up, restore, scale, tune, shard, cluster, and fix what is likely to be a far less stable technology with much less instrumentation compared to a decades-old RDBMS. These things obviously aren't always trivial on SQL Server or Oracle, but are often even worse on emerging platforms in my experience. – James L Sep 21 '14 at 12:14
  • What I see is that the system had certain flaws for some time, but is about to go downhill now, mainly due to some client applications now demanding all required data in the "horizontal" metadata table (which is already copied and thus bad design). So many data tables will have their data represented in and copied to the "vertical" global metadata table (which is supposed to be for customized properties), and from there again copied to it's "horizontal" representation. Sometimes by trigger, sometimes by applications reacting on entity change events, themselves again changing the entities. – Erik Hart Sep 21 '14 at 15:02
  • @ErikHart: The requirement for a specific layout of the returned data is just another confirmation that your client programmers are, there really is no other term for it: idiots. Any programmer worth his salt should not care if the data is returned in columns or rows. It is not the job of the database to return data pivoted one way or the other. – Thomas Kejser Sep 21 '14 at 21:30
  • The pivoted metadata table is some kind of legacy, some older programs relied on it (and their successors still use it), and I heard repeatedly that "loading metadata in one row is faster than loading many rows, one for each metadata value". Not my opinion, or at least, the performance gain is rather irrelevant. Also, the pivoted table had been used for "poor man's search engine" (LIKE '%...%') until some time ago. BTW, I am in no responsible position for any "my" programmers I could give orders. – Erik Hart Sep 21 '14 at 23:40
  • @ErikHart: If leaving the job isn't an option, your first step is to identify exactly "where it hurts the most". Find out which queries are important to the people paying for the system. Once you do, use the excellent troubleshooting features in a modern relational database to target JUST those queries. In a system like this, full scale replacement of the engine is not the best strategy. Small, incremental changes is. Once you have won a few battles, you may get some influence over the programmers and can start changing bigger things. – Thomas Kejser Sep 22 '14 at 7:39

Never touch a running system, especially not for the sake of making it "better" without adding value. If you add some feature and in the same class can do some refactoring, do it, test it thorough. But never just for the sake of changing it.

In most cases NoSQL reads and stores the whole document too, just like the hibernate mapping you describe, so there might not even be a performance boost. It keeps all data in memory, so 10 GB will be a lot more in memory when you have for example indexes on them.

In SQL the Table+ID for multiple tables could be split into one foreign key for each table that can be null.

NoSQL is great, but you should work a while with it (and real data) to see the limitations. For this use case I don't see a real benefit of one over the other. And since the SQL is already there, it is better to improve performance by using bulk operations like you mention. There you should really measure how big the improvement is. Waiting 15 ms instead of 30 ms is probably not interesting for the most users.

| improve this answer | |
  • An object document should be read in a single request, instead of a relation based object, which has to be traversed, causing at least half a dozen additional queries for sub-entities and properties. Memory in database server is not currently a problem, neither disk nor RAM. Unfortunately, the "never change a running system" attitude greatly contributed to the problems, because design related problems got only quick workarounds and patches, but never lead to correction of the flawed design elements. – Erik Hart Sep 21 '14 at 15:23
  • The question is, other than you don't like it, what problem is the current design causing? If there are none, there is no reason to change anything. If there are problems, those need to be analyzed and a solution found. This might be complete redesign like you want it, but probably some simple changes will be easier to implement. Never fix what is not broken. Otherwise you double the resources spent on the system without adding anything new. If it is broken, of cource fix it! – user47850 Sep 21 '14 at 15:41
  • This is exactly the prevalent attitude towards the system and it's flaws. So, what do you call a problem? A nominal 150 hp car which can actually deliver max. 20hp while consuming ten times as much gas as supposed to? But it's running, you don't need 150 hp, and you don't care for the costs of gas (imagine it would be really cheap!). So, no reason to change anything. – Erik Hart Sep 21 '14 at 18:08
  • 1
    ...And suddenly you notice that your 150 hp car, reduced to 20 hp, can't climb that steep mountain road. Are you going to repair it now? No, that's costly and might make further repairs necessary, or break it down completely! Instead, take out the backseats and spare wheel, fill in only as much gas as needed, to make it lighter. If that doesn't help, select a lengthy alternate route or load it on a truck, to carry it up the mountain! Problem solved! – Erik Hart Sep 21 '14 at 19:07
  • @ErikHart: It is perfectly viable to return the entire object in a single roundtrip on a relational database. This is not something that is unique to NoSQL. In NoSQL, things may be stored together nicely - but they will be taking up more space. Doing a few extra joins is much preferably to storing and digging after XML data. – Thomas Kejser Sep 21 '14 at 21:27

@Erik You don't replace a 150 mph car by one that can do 200 mph just for the fun of it. If you have a customer who runs into performance problems orif you get a new one, who will need more power, okay. But no sane person will spend tons of money rewriting the whole software to do the same thing again. And calling people idiots just when you are narrow minded yourself doesnt make you smarter or more right.

There has a to be a need for change, meaning a real project/customer, and then you look into the options, either make the db layout smarter or the quering program or replace the db. but you cannot expect to change things cause it is cool now.

As far as I remember, hibernate had lazy loading of child elements, so so you only need to load the main object for manipulation. It doesn't really matter, it will stay what it is until there is a need for change. Grow ip and learn to accept it or found your own company where you do things "better", but the explain your employees why you ran out of business with twice the development cost of other companies.

We are not talking about a fresh project here, it is an existing and running legacy system that does not need to be changed .. unless it causes problems like the 20mph you mention. Does it? Right now? or do you just expect it in the future?

Are you going to repair it now? No,

This is why you fail. The answer is yes when the time is right, not earlier.

| improve this answer | |
  • I called nobody an idiot. Many flaws have understandable causes, but that doesn't make them right. NHibernate lazy loading at least requires the related objects to be mapped, which is not the case. Most NHibernate mappings only map single objects. Some time ago, some programmers would say: "don't use those bags in mappings, nobody can handle them!". For now, it gets slow from time to time, with occasional deadlock problems. But copying table contents into the metadata table, as it has been started, will likely run it into disaster. – Erik Hart Sep 21 '14 at 23:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.