CODEWITHSUNDEEP
sql-servervb.net
user1447679
user1447679

Reputation: 3240

Where to put Business Logic? In VB.Net? Or in SQL?

I'm creating an application that allows me to create scheduled tasks. I have a threaded process that runs in the background every minute, and triggers the following class:

Namespace MyName.space
  Public Class RunJob
  ...
  End Class
End Namespace

A job can either be "Run Once" or "Recurring".

I am querying the job table in vb.net, storing the results in a dataset so that I can iterate over them one at a time. I then started thinking about functions to validate if a job should run or not based on its unique criteria.

For example, the simplest of all would be the "Run Once" jobs:

If Type = "Run Once"
    JobShouldRun = Helpers.Validate_RunOnce(WhenToRun, RunCount)
ElseIf Type = "Recurring"
    ...
End If

The function would check if the "WhenToRun" is within 5 minutes of the current date and time configured on the server. I chose a 5 minute window in the event of a task failure so that a minute later it'll try again and still have 3-4 tries left.

But then I started thinking that I could control more of this within the SQL Query to get the jobs themselves, and skip validating this "easy" on within vb.net, but I'm unsure of which method would be more optimal. So I could limit the result set to begin with in my initial query:

SELECT
     JobId
    ,JobType -- Run Once or Recurring
    ,WhenToRun

FROM JobTable a
WHERE Active = 1
AND (
      JobType = 'Run Once'
      AND TotalRuns = 0
      AND DATEDIFF(minute, getdate(), a.WhenToRun) BETWEEN 1 AND 5
    )
OR JobType = 'Recurring'

Then I started thinking, should I perform all my logic with either UNION or JOINS, or a complex WHERE/AND/OR conditioning on the recurring jobs as well? They get quite a bit more complicated... Such as weekly and what days of the week, and what time, etc. Or every "int" years in "Jan|March|Etc" on the Last day of the month.

So I started thinking along these lines:

SELECT 
     JobID
    ,JobType
    ...
FROM JobTable 
WHERE RecurringType = 'Weekly'
AND ... more conditions based on all the custom job settings

UNION ALL

SELECT
     JobID
    ,...
FROM JobTable 
WHERE RecurringType = 'Monthly'
AND ... more conditions based on all the custom job settings

This query would end up quite large and complex, but my question is, should I handle this in vb.net or SQL, or the easy stuff in SQL and the more complicated conditions in vb.net? I'm unsure of the performance impact of either direction.

Upvotes: 2

Views: 229

Answers (1)

Dave Mason
Dave Mason

Reputation: 4936

You're basically asking "Where do I put code for business logic? In the app? Or in the database?" It can be a hotly debated topic, but it's sensible to consider both options. There are some tradeoffs with each approach, and it seems kinda cavalier to say one way is right and the other is wrong.

If your BL code is in the app, you get the primary benefit of the robustness of the .Net Framework. I love tsql, but you just can't do as much with it as you can VB.Net. If I can stereotype developers, I suspect in general they'll be more comfortable with .Net code. For most, it's probably easier to debug than tsql. Since the .Net code is compiled to an assembly, it's not likely to be altered either.

If your BL code is in tsql, you may find that performance is a bit better. You also abstract away some of the complexity from the .Net code and make that code base a bit smaller (some might argue this is bad thing). If there are bugs that need to be fixed, it's usually easier to redeploy a stored procedure (or user-defined function, view, etc) than to redeploy an application (especially if it involves multiple workstations). On the downside, it's easy for other to see (steal!) your code, or make changes to it.

As a general guideline, the more complex the business logic is, the more likely I'd be to put it in the app. If it's pretty simple and not likely to change, I'll consider putting it in tsql. That being said, if I had to pick one or the other with no exceptions, I'd put the BL in the app.

Upvotes: 2

Related Questions