The database is usually a pretty important piece in your application, and it likes to remind you that it should be respected. If you don’t take care of that, it will make sure that there will be a lot of pain in your future. Case in point, let us look at this method:
It looks nice, it is certainly something that looks like a business service. So let us dig down and see how it works.
It seems like a nice thing, the code is clear, and beside the bug where you get 100% discount if you buy enough and the dissonance between the comment and the code, fairly clear. And it seems that we have service logic and entity logic, which is always nice.
Except that this piece of code issues the following queries (let us assume a customer with 50 orders).
1 Query to load the customer, line 34 in this code. And now let us look at line 35… what is actually going on here:
Okay, so we have an additional query for loading the customer’s orders. Let us dig deeper.
And for each order, we have another query for loading all of that order’s items. Does it gets worse?
Phew! I was worried here for a second.
But it turns out that we only have a Select N+2 here, where N is the number of orders that a customer has.
What do you want, calculating the discount for the order is complicated, it is supposed to take a lot of time. Of course, the entire thing can be expressed in SQL as:
SELECT SUM((UnitPrice * Quantity) * (1 - Discount) Income FROM OrderItems o WHERE o.OrderID in ( SELECT Id FROM Orders WHERE CustomerId = @CustomerId )
But go ahead and try putting that optimization in. The architecture for the application will actively fight you on that.
More posts in "Northwind Starter Kit Review" series:
- (26 Jan 2012) Conclusion
- (24 Jan 2012) That CQRS thing
- (23 Jan 2012) It is all about the services
- (20 Jan 2012) From start to finishing–tracing a request
- (18 Jan 2012) If you won’t respect the database, there will be pain
- (16 Jan 2012) Refactoring to an actual read model
- (13 Jan 2012) Data Access review thoughts
- (12 Jan 2012) The parents have eaten sour grapes, and the children’s teeth are set on edge
- (11 Jan 2012) Data Access and the essence of needless work, Part II
- (10 Jan 2012) Data Access and the essence of needless work, Part I