Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Domain Logic and SQL (2003)

martinfowler.com

Post reply on HN
Print view View original

21–30 of 61 posts

Re: Domain Logic and SQL (2003)

#21

Post by mumblemumble »

jasonhansel wrote: post #18

One thing this leaves out is the danger of concurrency issues, which can sometimes be worsened by using an ORM. Writing correct, race-free SQL can be very hard (with READ COMMITTED transactions) or require complex retry logic (with SERIALIZABLE). Preventing deadlocks can also be a concern. ORMs tend to hide the underlying SQL operations, making it even harder to verify whether operations are concurrency safe.

I think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model.

A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems and keep audit trails.

Re: Domain Logic and SQL (2003)

#22

Post by victor106 »

this really depends on the context.

Scenario 1: No plans to add additional functionality. Sticking to the e-commerce example in the article, if I am designing a system for use by just one customer who wants to offer the discount for just that one scenario and they don’t have any future needs to offer any other kinds of discount then put logic in theDB. It’s quick and easy.

Scenario 2: Plans to expand to add additional functionality. Sticking to the example we have future plans to offer other types of discounts to our customers. Which means we have to develop generic interfaces so it’s easy to add functionality. Have the business logic in the application layer.

Scenario 3: plans to expand and additional functionality but also let users configure and add additional functionality without IT intervention. Sticking to the e-commerce example we want business users to create new offers and expose them to our customers. In this case use a rule engine like drools.

It really depends on what functionality you are delivering and how you see that evolving in the future

Re: Domain Logic and SQL (2003)

#23

Post by mumblemumble »

crazygringo wrote: post #19
nizmow wrote: post #2

One thing this article doesn't mention is that in many cases databases scale poorly, and only vertically. You can throw a bunch of domain logic in complex SQL queries and see significant performance benefits, but at some point you may find that your application has grown and your SQL server is running into IOPS and CPU limits that are difficult to fix. At that point your answer could be, pull logic out of those compl…

I don't think I've ever seen performance improvements from taking logic in a complex SQL query and re-implementing it at the application layer. The bottleneck with databases is virtually never calculations on results (CPU), it's disk access and network latency and bandwidth. And generally, if you do have crazy complex CPU-bound calculations you need to do on data (e.g. scientific stuff)... SQL doesn't provide the nec…

And I've often seen things go dramatically in the opposite direction.

I/O is generally just about the slowest thing a computer can do. So, in general, the more you can reduce the data before sending it across the network, the better. And, heck, a well-crafted SQL query can often save you having to even read large chunks of data off of the disk in the first place, let alone pipe it across the network.

Re: Domain Logic and SQL (2003)

#24

Post by tkyjonathan »

jasonhansel wrote: post #18

One thing this leaves out is the danger of concurrency issues, which can sometimes be worsened by using an ORM. Writing correct, race-free SQL can be very hard (with READ COMMITTED transactions) or require complex retry logic (with SERIALIZABLE). Preventing deadlocks can also be a concern. ORMs tend to hide the underlying SQL operations, making it even harder to verify whether operations are concurrency safe.

Batching is the multi-threadedness of databases.

Its also important to remember that in databases, you are more often optimising for IO usage than CPU.

Re: Domain Logic and SQL (2003)

#25

Post by [deleted] »

mumblemumble wrote: post #20
smoyer wrote: post #9

Earlier quoted context omitted.

"But that's not going to be database agnostic!" Haha ... I'll admit I used to say that but rarely did an internal system (what I work on most) ever actually have that requirement. Systems that are sold to customers to run on their premises are another story but don't worry about using PostgreSQL-only (e.g.) features to speed up both your processing and development times. I have however seen the horrors of 600 line st…

There seem to be two ways that interacting with the database works out in practice: You can embrace the specific DBMS you have, get the most out of it, and end up tightly coupled to it. Or you can try to be database agnostic, increase your development costs and limit your performance and data expressivity in the service of that goal, and still end up tightly coupled to the DBMS. My pet hypothesis is that "database ag…

[deleted]

Re: Domain Logic and SQL (2003)

#26

Post by MiroF »

nizmow wrote: post #2

One thing this article doesn't mention is that in many cases databases scale poorly, and only vertically. You can throw a bunch of domain logic in complex SQL queries and see significant performance benefits, but at some point you may find that your application has grown and your SQL server is running into IOPS and CPU limits that are difficult to fix. At that point your answer could be, pull logic out of those compl…

what an odd comment... I find it difficult to believe that you would run into CPU limits before data movement costs slowed you down.

Also RDBMS are not that difficult to scale, especially easier than an ad-hoc ORM/whatever on your application side.

Re: Domain Logic and SQL (2003)

#27

Post by MiroF »

fr1tkot wrote: post #14
ak39 wrote: post #5

Earlier quoted context omitted.

Most (if not all) modern RDBMSs allow for reliable horizontal and vertical scaling. SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers (for horizontal scaling) in geographically distributed servers. PostgreSQL has this and it's absolutely free. Where is this meme that RDBMSs…

> Where is this meme that RDBMSs do not scale coming from? (I see lots of people saying this as though it is a given. Has there been any published data on this for me to read?). It mostly comes from people who don't know how and when to create an index.

I feel like you should understand how a relational database works before using them, especially if it's at the scale that you're running into "CPU limits"

Re: Domain Logic and SQL (2003)

#28

Post by slim »

mumblemumble wrote: post #21
jasonhansel wrote: post #18

One thing this leaves out is the danger of concurrency issues, which can sometimes be worsened by using an ORM. Writing correct, race-free SQL can be very hard (with READ COMMITTED transactions) or require complex retry logic (with SERIALIZABLE). Preventing deadlocks can also be a concern. ORMs tend to hide the underlying SQL operations, making it even harder to verify whether operations are concurrency safe.

I think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model. A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems…

isn't soft delete an update?

Re: Domain Logic and SQL (2003)

#29

Post by stevedewald »

slim wrote: post #28
mumblemumble wrote: post #21

Earlier quoted context omitted.

I think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model. A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems…

isn't soft delete an update?

Not necessarily. You can insert a new row with a field named ‘deleted’ or similar set to ‘true’. This is how you would implement soft deletes in an append-only table.

Re: Domain Logic and SQL (2003)

#30

Post by boublepop »

slim wrote: post #28
mumblemumble wrote: post #21

Earlier quoted context omitted.

I think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model. A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems…

isn't soft delete an update?

It doesn’t have to be. You Can write reads that don’t return entries if a separate deletion entry exists.

Can’t say I’ve worked that way in sql, but I have seen that pattern in append only data structures. With for instance a “order fulfilled” entry essentially is a delete operation on an outstanding order entry.

Post reply on HN