Views with business logic vs code

Question

For an application I need to send personalized invitation emails to end-users every hour. Those users email-addresses, and the rest of their information are in the database.

• All users that have a bit Planned.IsPlannable set to True and have a Planned.DateStart and a Planned.DateEnd between a certain period are available for receiving an email message.
• There are about 350 messages to sent every hour.
• All information that must be included in the message is from the Database.
• The application is a .NET4.0 Console application, for data access I use Subsonic3.0.

There are at least two scenarios to choose from: retrieving the appropriate data via a

1:) View something like:

SELECT    [Computer].ComputerName, 
          [User].UserEmail, 
          [Planned].DateAvailable, 
          [Planned].DatePlanned
FROM      [Computer] 
INNER JOIN
          [Planned] ON [Computer.ComputerID] = [Planned.ComputerID] 
INNER JOIN
          [User] ON [Computer].UserID = [User].UserID
WHERE     (DATEDIFF(dd, GETDATE(), [Planned.DateAvailable]) < 10) 
          AND Planned.IsPlannable = 1

and compose, based on the results of this view, the appropriate messages in the C# code of this application.

2:) Compose the entire message on the sql-server in the view and return something like

[EmailTo]
[Subject]
[Body]

Then only iterate trough the results and create MailMessage Objects from it

in both cases I get the messages like:

foreach (vwGetInvitableComputer u in UserController.GetAllInvitableUsers())
{
    // where the usercontroller wraps the Subsonic logic.
    // the composition happens here
}

Iterate trough that in the C# code and just compose the mailmessage from that preformatted data.

What scenario is performance and resources wise to choose?

update: There is indeed text manipulation in the database in option 2. However, this is replacing three strings in the messagbody to the CompterName, DateStart and DateEnd.Perhaps, SQL-views are smart enough to spread the performance for this, while the c# code does it "when requested"?

Farmelo

What do you mean by 'spread the performance'? Do you mean across threads? You could just as easily use a parallel foreach, or something, in C#. – Bipack

Answer 1

First consideration: Can you envision a time when changing the emails format will become necessary? If not then it probably doesn't matter.

Second consideration: Can you envision a time when the emails will require more manipulation than SQL is easily capable of delivering? If so then you definitely should choose C#.

Third consideration: How problematic would a redeploy be for the environment?

And finally an alternative option: Use C# for the message composition, but use a database or file based Template that can easily be updated.

Bipack

It was a risky question, I know, but I wanted to know if someone had experience with this. However your considerations are good ones, I stick to keeping my BL in the pplication, not in the DB. – Farmelo

Answer 2

I would choose option 2, composing all the information required to deliver the emails on the server either using a view or a stored procedure. Ideally database calls should result in the least number of round trips to the server as possible.

Leasia

I think this is bad advice. This is a limiting optimization where it is not needed. The same number of trips to the server will be made in either of his example options (if he gets all the data in one fell swoop). And composition is much harder (prone to erros) in SQL than C#. – Bipack

Obviously I was envisaging some sort of templating system, as described in your answer ;) – Leasia