3 ms·

After years of using Hibernate and NHibernate, considered best of breed and most well thought out and thoroughly engineered ORMS, I agree. They fall of a produc

After years of using Hibernate and NHibernate, considered best of breed and most well thought out and thoroughly engineered ORMS, I agree. They fall of a productivity cliff pretty quickly, particularly on larger projects (I'm talking things with 100+ domain objects). I've settled on: Use CQRS (Command Query Response Segregation) and stuff ORMs where the sun doesn't shine. Separate Update/Insert/Delete commands and queries. Commands return nothing and are assumed to work unless an exception is thrown, then the whole batch is rolled back. Queries return data. Transaction boundaries are arbitrary based on the requirement of the calling service, not the unit of work and/or session. By default all transactions are imperative. Stored procedures, triggers and views are banned. Commands are classes with properties attached (easy to validate, test and provide good encapsulation) and are internally represented as raw SQL with bound parameters. Queries return enumerable lists of objects and are internally represented as a raw SQL query with bound parameters. Everything is programmed to generic interfaces: IStorageCommand<T> and IStorageQuery<T> That is all. I've used Dapper on .Net to do the internal representation. It is portable across non SQL stores as well and has been used to interface with cache and Redis as well. Plus due to the loosely coupled abstraction, you can use AOP to stick read-through cache interceptors in between the IStorageQuery<T> interface and the consumer. Also you can stick cache invalidators between IStorageCommand<T> and the consumer. Moving one of our core subsystems over to this with some careful design and DBA consultation knocked our 48 Core Xeon SQL box from 60% utilisation to 5% (mainly IOPS/storage throughput. CPU is a non issue there).

windowsworkstoo 12y ago
Yes, nice, same here. One question out of curiosity - on your IStorageQuery interface what methods do you have? Do you have the usual by Id's etc or do you have a query object that you pass in that can build/translate to the relevant SQL?
- sudowhodoido 12y ago
  Query object. The interface is as follows (I simplified a bit above: public interface IStorageQuery<T,Q> where Q : IStorageQueryCommand { IEnumerable<T> Query(Q query); } IStorageQueryCommand is an empty marker interface. Internally this is all bound to the query by property name.
aikah 12y ago
At the end of the day you're still doing object relational mapping.You just built something custom instead of using Hibernate,so you're still using an ORM. Your domain objects are still a graph that need to be hydrated from the database. And dont tell me that writing hydrators by hand is fast and dry,especially when you have 100+ domain objects,compared to a few XML files or annotations that can later take advantage of query builders or HQL.
- deleted 12y ago
  [deleted]
- sudowhodoido 12y ago
  Fair point but it's a terribly light ORM behind an abstraction boundary that doesn't make assumptions about the type of store it is communicating with. We don't write any hydrators. On our previous solution they are generated at compile time. Then they were replaced by runtime code generation inside Dapper. There is no XML, there are no query builders, there is no HQL. There are too few guarantees in each of those technologies.
e12e 12y ago
> Stored procedures, triggers and views are banned. So... NoSQL? I'm only half-joking -- I find it strange how people will use an SQL DBMS, and yet choose not to use the main features. Model, Normalize/refactor, then apply views/triggers/stored procedures to present a simple "API" (or maybe just "view") of your datamodel to the application? If you don't use the DBMS for modelling the data, do you really need it for storing your data (obviously, one valid answer is "no" -- see "NoSQL-movement", Object DMBS, Document DBs etc...).
- sudowhodoido 12y ago
  Actually we use both. Both have their advantages so we use SQL Server and Redis. The data model is what we're exposing, not the table model.
xtrumanx 12y ago
> knocked our 48 Core Xeon SQL box from 60% utilisation to 5% Do you believe it was the move to CQRS specifically that caused the drop in utilization or mostly other factors (caching, the DBA consult)? A quick read through of CQRS makes it seem like its mostly a way to organize your data access code and should have little affect to performance.
- sudowhodoido 12y ago
  Yes definitely. The previous solution was thoughtless and poorly abstracted so there were queries with tens of joins all of which multiplied IOPS and index lookups considerably. The dataset is too large to fit in any reasonable amount of RAM (2.2Tb) so we rely on fast IO. Utilisation went down to about 10% after refactoring this. The caching is the icing on the cake and is used to avoid execution latency rather than reduce IO as it can run async with the database queries. It deals with only the most common datasets due to the complexity of what looks like even simple cache invalidations. For ref, I'm a DBA as well as a pile of other things...
_random_ 12y ago
You should write a blog post.
- sudowhodoido 12y ago
  I'd need a blog for that and the time to do it :) Not a bad idea though.
  - _random_ 12y ago
    CQRS/Event Sourcing sounds like a good idea, but not something easy to introduce into an enterprise, unlike e.g. packaging or micro-services.
    - sudowhodoido 12y ago
      CQRS and event sourcing are separate concepts. I wouldn't introduce event sourcing into any product. It makes consistency very hard and expensive; more so than to ignore it and buy much larger machines. In fact, after watching people follow Udi Dahan down the ES hole a couple of times, scrap the architecture entirely and keep the CQRS bit it's a proper computer science ghetto I don't want to see again. Packaging and micro-services have a lot more promise if you ask me, so I'm all for that. Unfortunately the complexity of some systems, particularly in the financial sector, tend to make micro-services impossible as the smallest possible abstraction is huge and everything is tangled like spaghetti.
angersock 12y ago
(database here is shorthand for your SQL RDBMS of choice) Out of curiosity, where do you fall in terms of having business logic in the database via functions and triggers? Why do you feel that way? I'm kind of of the opinion that database should only store data, maintain relations, and handle filtered querying (read-only)...anything more complicated should be handled by application-side logic. What's your stance on that?
- sudowhodoido 12y ago
  My stance is exactly the same as yours. All the logic sits in the service layer in front of this abstraction and nothing touches anything but the service layer. Nothing touches the tables at all unless it goes through both layers. This was a massive battle to get the organisation to accept. I've seen many many piles of fail where all the logic is pumped into the database where possible. The result is making scale up expensive and scale out impossible. Also where people do ETL straight into the tables resulting in constraint failures outside the limited capability of the database engine. No we killed those expectations, everyone sees the benefits.
  - angersock 12y ago
    Awesome, thanks for the response--I'm not a DBA, so I'm having to try and play catchup. :) How do you (or others?) feel about foriegn keys? They seem like a really good idea for read-heavy workloads and for documentation and maintenance and interop with tools, but for write-heavy loads leaving them off seems to have non-trivial performance benefits (reference case is 9.2 PGSQL, if it matters). Again, my instincts are that they're probably the right thing to do, and that removing them later is going to be easier (if it turns out we need to) than trying to shoehorn them in too late.
    - sudowhodoido 12y ago
      From my perspective, foreign keys are important as they are the last guarantee that two bits of data belong to each other. Also they do have a positive performance impact in JOIN scenarios. If FK performance is an issue, then there are better solutions before you break them (denormalise on write etc).

windowsworkstoo 12y ago

Yes, nice, same here. One question out of curiosity - on your IStorageQuery interface what methods do you have? Do you have the usual by Id's etc or do you have a query object that you pass in that can build/translate to the relevant SQL?

sudowhodoido 12y ago
Query object. The interface is as follows (I simplified a bit above: public interface IStorageQuery<T,Q> where Q : IStorageQueryCommand { IEnumerable<T> Query(Q query); } IStorageQueryCommand is an empty marker interface. Internally this is all bound to the query by property name.

aikah 12y ago

At the end of the day you're still doing object relational mapping.You just built something custom instead of using Hibernate,so you're still using an ORM. Your domain objects are still a graph that need to be hydrated from the database. And dont tell me that writing hydrators by hand is fast and dry,especially when you have 100+ domain objects,compared to a few XML files or annotations that can later take advantage of query builders or HQL.

deleted 12y ago
[deleted]
sudowhodoido 12y ago
Fair point but it's a terribly light ORM behind an abstraction boundary that doesn't make assumptions about the type of store it is communicating with. We don't write any hydrators. On our previous solution they are generated at compile time. Then they were replaced by runtime code generation inside Dapper. There is no XML, there are no query builders, there is no HQL. There are too few guarantees in each of those technologies.

e12e 12y ago

> Stored procedures, triggers and views are banned. So... NoSQL? I'm only half-joking -- I find it strange how people will use an SQL DBMS, and yet choose not to use the main features. Model, Normalize/refactor, then apply views/triggers/stored procedures to present a simple "API" (or maybe just "view") of your datamodel to the application? If you don't use the DBMS for modelling the data, do you really need it for storing your data (obviously, one valid answer is "no" -- see "NoSQL-movement", Object DMBS, Document DBs etc...).

sudowhodoido 12y ago
Actually we use both. Both have their advantages so we use SQL Server and Redis. The data model is what we're exposing, not the table model.

xtrumanx 12y ago

> knocked our 48 Core Xeon SQL box from 60% utilisation to 5% Do you believe it was the move to CQRS specifically that caused the drop in utilization or mostly other factors (caching, the DBA consult)? A quick read through of CQRS makes it seem like its mostly a way to organize your data access code and should have little affect to performance.

sudowhodoido 12y ago
Yes definitely. The previous solution was thoughtless and poorly abstracted so there were queries with tens of joins all of which multiplied IOPS and index lookups considerably. The dataset is too large to fit in any reasonable amount of RAM (2.2Tb) so we rely on fast IO. Utilisation went down to about 10% after refactoring this. The caching is the icing on the cake and is used to avoid execution latency rather than reduce IO as it can run async with the database queries. It deals with only the most common datasets due to the complexity of what looks like even simple cache invalidations. For ref, I'm a DBA as well as a pile of other things...

_random_ 12y ago

You should write a blog post.

sudowhodoido 12y ago
I'd need a blog for that and the time to do it :) Not a bad idea though.
- _random_ 12y ago
  CQRS/Event Sourcing sounds like a good idea, but not something easy to introduce into an enterprise, unlike e.g. packaging or micro-services.
  - sudowhodoido 12y ago
    CQRS and event sourcing are separate concepts. I wouldn't introduce event sourcing into any product. It makes consistency very hard and expensive; more so than to ignore it and buy much larger machines. In fact, after watching people follow Udi Dahan down the ES hole a couple of times, scrap the architecture entirely and keep the CQRS bit it's a proper computer science ghetto I don't want to see again. Packaging and micro-services have a lot more promise if you ask me, so I'm all for that. Unfortunately the complexity of some systems, particularly in the financial sector, tend to make micro-services impossible as the smallest possible abstraction is huge and everything is tangled like spaghetti.

angersock 12y ago

(database here is shorthand for your SQL RDBMS of choice) Out of curiosity, where do you fall in terms of having business logic in the database via functions and triggers? Why do you feel that way? I'm kind of of the opinion that database should only store data, maintain relations, and handle filtered querying (read-only)...anything more complicated should be handled by application-side logic. What's your stance on that?

sudowhodoido 12y ago
My stance is exactly the same as yours. All the logic sits in the service layer in front of this abstraction and nothing touches anything but the service layer. Nothing touches the tables at all unless it goes through both layers. This was a massive battle to get the organisation to accept. I've seen many many piles of fail where all the logic is pumped into the database where possible. The result is making scale up expensive and scale out impossible. Also where people do ETL straight into the tables resulting in constraint failures outside the limited capability of the database engine. No we killed those expectations, everyone sees the benefits.
- angersock 12y ago
  Awesome, thanks for the response--I'm not a DBA, so I'm having to try and play catchup. :) How do you (or others?) feel about foriegn keys? They seem like a really good idea for read-heavy workloads and for documentation and maintenance and interop with tools, but for write-heavy loads leaving them off seems to have non-trivial performance benefits (reference case is 9.2 PGSQL, if it matters). Again, my instincts are that they're probably the right thing to do, and that removing them later is going to be easier (if it turns out we need to) than trying to shoehorn them in too late.
  - sudowhodoido 12y ago
    From my perspective, foreign keys are important as they are the last guarantee that two bits of data belong to each other. Also they do have a positive performance impact in JOIN scenarios. If FK performance is an issue, then there are better solutions before you break them (denormalise on write etc).