Any architecture tips for sending out daily, weekly email updates that require calculation.

Question

I have a web app that will send out daily, weekly email updates depending on the user permissions and their alert settings (daily, weekly, monthly, or none).

Each email to an account (which would have multiple users) requires a few DB calls and calculations. Thus making these daily/weekly emails pretty expensive as the number of users increase.

Are there any general tips on writing these services? I'm looking for some architecture tips or patterns and not really topics like email deliverability.

Answer 1

I would cache the data before the processing time, if you are having to handle very large sets of information, so that the DB 'calculations' can be omitted from the processing cycle at the specific times. Effectively break the processing up so that the DB intensive stuff is done a bit before the scheduled processing of the information. When it comes time to actually send these emails out, I would imagine you can process a very large volume quickly without a whole lot of tuning up front. Granted, I also don't know what kind of volume we're talking about here.

You might also thread the application so that your processing data is further split into logical chunks to reduce the overall amount of data that has to be processed all at once, depending on your situation it might streamline things, granted, I normally don't recommend getting into threading unless there is a good reason to, and you may have one. At the very least, use a background worker type of threaded process and fire off a few dependent on how you segment your data.

When handling exceptions, remember to now let those bring your processing down, handle them through logging of some sort or notification and then move on, you wouldn't want an error to mess things up for further processing, I'm sure you probably planned for that though.

Also, send your emails asynchronously so they don't block processing, it's probably an obvious observance but sometimes little things like that are overlooked and can create quite the bottleneck when sending out lots of emails.

Lastly, test it with a reasonable load beforehand, and shoot for well over capacity.

Answer 2

You may want to check out sql reporting services.

You may have to translate the current setup into the sql reporting format but in return you'll get a whole administrative interface for scheduling the report generation, allowing users to modify the report inputs, caching historical/current reports, and the ability for users to manage their own email subscriptions.

http://msdn.microsoft.com/en-us/library/ms160334.aspx