Sql Processing vs. ASP.NET Runtime processing

Question

I know in general it is a good practice to move as much processing as possible from Sql Server to the application (in my case ASP.NET). However what if the processing on the application level means passing 30+ extra parameters to the Sql Server. In this case is it worth moving the processing to the Sql Server?

Here's the specific dilemma I am facing - which procedure will offer better performance overall?

CREATE PROCEDURE MyProc1
  @id int
AS BEGIN
  UPDATE MyTable
  SET somevalue1 = somevalue1 + 1,
      somevalue2 = somevalue2 + 1,
      somevalue3 = somevalue3 + 1,
      ...
      somevalueN = somevalueN + 1
  WHERE id = @id
END

Or

CREATE PROCEDURE MyProc2
  @id int,
  @somevalue1 int,
  @somevalue2 int,
  @somevalue3 int,
  ...
  @somevalueN int
AS BEGIN
  UPDATE MyTable
  SET somevalue1 = @somevalue1,
      somevalue2 = @somevalue2,
      somevalue3 = @somevalue3,
      ...
      somevalueN = @somevalueN
  WHERE id = @id
END

I am using a managed hosting, but I guess it is valid to assume that Sql Server and ASP.NET runtime reside on the same machine, so the transfer of data between the two would probably be pretty fast/negligible(or is it).

---

The 30 Parameters are basically totalNumberOfRatings for different items. So whenever a user of my web app gives a new rating for itemN then totalNumberOfRatingsItemN is incremented by 1. In most cases the rating will be given to several items (but not necessarily all), so totalNumberOfRatings is not the same for different items.

Answer 1

Is performance that critical in this instance? If its not I would personally go for readability and maintainability - which would mean implementing it following the same standards as the rest of the system.

Answer 2

in general it is a good practice to move as much processing as possible from Sql Server to the application

Says who? SQL Server is good at some things, not so good at others. Use your judgement.

and by "application", do you mean

• the web page
• an application-service layer
• an enterprise service bus component
• a web service
• something else?

There are many choices...

As to your specific question, incrementing 30 fields seems ludicrous. Passing 30 parms seems excessive. Some context is in order...

Answer 3

I'll make a wild guess that you've read that SQL Server is not the appropriate place to do math. I think SQL is quite appropriate for doing a handful of arithmetic operations and aggregate operations, like SUM. Only measuring both implementations in realistic load scenarios can say for sure.

What SQL isn't appropriate for are things like multiple regression and matrix inversion.

Moving incrementors to the application tier sounds to me like a micro-optimization that is unlikely to pay for itself. Implement the logic to increment the values in either tier that makes the code readable and maintainable.

Answer 4

I recommend normalizing your table to eliminate the 30+ columns of ratings. Instead, consider having a table with a single rating column and having one row for each different item, like so:

CREATE TABLE ItemRatings
(
    myTableId  INT NOT NULL, -- Foreign key
    itemNumber INT NOT NULL,
    itemRating INT
);

You could then increment a bunch of ratings at once with a query like

UPDATE ItemRatings
   SET itemRating = itemRating + 1
 WHERE myTableId = @id
       AND itemNumber IN (@n1, @n2, @n3, ...)

Something like that, anyways. I'm not exactly clear on how your tables function since you anonymized all the names, but hopefully you get the gist of what I'm saying.

---

That said, if you don't want to or can't normalize your table this way, I agree with the other answers. It's six of one, half a dozen of the other. Personally, I'd choose the first way and let the database do all the heavy lifting. Of course, as in all things, if performance is ultra-critical then the real answer is not to guess; try both ways and get out your stopwatch!