Application Design: Single vs. Multiple Hits to the DB

Question

I'm building a service that performs a set of configured activities based on the type of request that it receives. Each activity involves going to the database and retrieving/updating some kind of information. The logic for each activity can be generalized and re-used across different request types. The activities may need to participate in a transaction for the duration of the servicing the request.

One option, I'm considering is having each activity maintain its own access to DAL/database. This fully encapsulates the activity into a stand-alone re-usable piece, but hitting the database multiple times for one request doesn't seem like a viable option. I don't really know how to easily implement the concept of a transaction across the multiple activities here either.

The second option is to encapsulate ALL the activities into one big activity and hit the database once. But this does not allow re-use and configuration of these activities for different requests.

Does anyone have any suggestions and input about what should be the best way to approach my problem? Thanks for any help.

Answer 1

I'm afraid it's a little difficult to chose a design without details. My first choice would be to follow the first option; it sounds like a simpler approach. The second one sounds like it would perform better, but there's a voice in the back of my head whispering about premature optimization and evil.

This really depends on how much this service will be used. Is it going to be under a lot of load? Is the server this is running on a powerful machine running just a few things, or a VM along several other VMs each running several services?

If you want to be on the safe side I would suggest a prototype. Just build a small program that uses the database as much as the app would use it in the first scenario you described and let it run. Nothing fancy, just let it pump random data in and out of the database. Don't forget to store a large volume of data in there to see how it copes. If it seems alright go with your first option. If not you can move to something more elaborate.

On a side note get to know your database of choice. You're not the first one to try to place a heavy load on it; most databases offer mechanisms to cope with this kind of thing.

Answer 2

You may want to consider NServiceBus. You can string multiple message handlers together giving you the pipeline you desire. It also runs all the handlers in one transaction, satisfying your transactional needs. Since each pipeline is autonomous, you can scale out all day long. The only caveat is that you'll have to perform a correlated request/response if you want to maintain that model(NSB supports this as well).

Answer 3

Abstract out the data access implementation from the business logic so you're not tied to it; if you're in .Net you'd typically use an interface.

When you do this, you'll need to design the interface - design it based on logical compartments of work: think ISP, SRP, CRP and CCP.

Having defined the interface you can build as many implementations as you like, whenever you like, and you can swap them out at anytime; There's also no reason why you can't use both at the same time - and have some logic that cotrols execution so that the uber one is called when that's appropriate or another implementation at other times.

Answer 4

The decision is probably determined by the transaction requirements. If all activities need to see the same snapshot of the database, they aren't really independent anymore. So the activities aren't completely independent.

What is the number of requests to the database in both scenarios? I.e. how many activities a typical power-user will have,how many users are on the system, etc. Also, with less requests usually comes more data - how does that look when combining the requests?

Increasing hits per minute from 5 to 10 isn't a problem. increasing by a factor of 5 for 1000 users is.

Could you possibly batch the requests? You could create the interface between activity and DAL such that you can batch the requests. E.g. all activities publish their requests and receive the results, and a request uses a collection of activities to handle. A simple request implementation could still run individual requests.