business logic in Stored Proc VS aspx.vb page

[A.V.C.]

Hello,

I stuck in a delimma.
Where to put the business logic that involves only one update
but N number of selects from N tables........with N where conditions

[John Bell]

Hi

In general I would put this in the middle tier or the database. Allowing
direct access to tables from a client would raise security issues and make
it less managable.

John

"A.V.C." <yh*****************@hotmail.com> wrote in message
news:d2*************************@posting.google.co m...

Hello,

I stuck in a delimma.
Where to put the business logic that involves only one update
but N number of selects from N tables........with N where conditions

[Erland Sommarskog]

A.V.C. (yh*****************@hotmail.com) writes:

I stuck in a delimma.
Where to put the business logic that involves only one update
but N number of selects from N tables........with N where conditions

I am of the school that puts as much as possible of the business logic
in the stored procedures. The idea is to put the logic where the data is.
If you put logic in the middle tier, you may have a lot network traffic.

The one case where the middle layer is a better place, is when you
have computations that are very intensive on reports. Then you can
take off load from SQL Server, and you can more easilyu scale out.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mountain man]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

A.V.C. (yh*****************@hotmail.com) writes:

I stuck in a delimma.
Where to put the business logic that involves only one update
but N number of selects from N tables........with N where conditions
I am of the school that puts as much as possible of the business logic
in the stored procedures. The idea is to put the logic where the data is.
If you put logic in the middle tier, you may have a lot network traffic.

Which would not have otherwise existed if the logic is internal
to the database stored procedure.
The one case where the middle layer is a better place, is when you
have computations that are very intensive on reports. Then you can
take off load from SQL Server, and you can more easilyu scale out.

Where separate pre-processing (ie: regeneration of the computed
values) into a work table is feasible, then this can allow the logic to
be retained in SQL because the realtime computations have been
reduced.

[A.V.C.]

THanx
Could you pls elaborate the scenario where middle tier is ideal for business logic ?

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

A.V.C. (yh*****************@hotmail.com) writes:

I stuck in a delimma.
Where to put the business logic that involves only one update
but N number of selects from N tables........with N where conditions

I am of the school that puts as much as possible of the business logic
in the stored procedures. The idea is to put the logic where the data is.
If you put logic in the middle tier, you may have a lot network traffic.

The one case where the middle layer is a better place, is when you
have computations that are very intensive on reports. Then you can
take off load from SQL Server, and you can more easilyu scale out.

[Erland Sommarskog]

A.V.C. (yh*****************@hotmail.com) writes:

Could you pls elaborate the scenario where middle tier is ideal for
business logic ?

Permit me to take an example from the system I work with, which is abour
securities trading. When a deal comes in there are a couple of computations
to carry out give the basics: price and the quantity. Most of these
computations are simple: price * qty gives your the purchase amount, and
then you compute charges. And to compute charges you require access to
data, because the charges depends on the customer and instrument and this
is information that is in several tables.

The one exception here is when you trade with bonds, because they may be
traded on interest rather than price, in which case the price has to be
computed from the interest. You also have to compute the accrued interest
for the bond, that the seller is to pay to the buyer. These computations
are not very simple to implement in SQL. What we do is that we call a
COM object that runs on the SQL Server machine that performs the
computation, so we still have this logic on the server.

Our customers have fairly modest volume of bond deals, so this is not an
issue. But assume that you have a site that trades exclusively in bonds,
and can perform many trades a second (not very likely, I think). In this
case, having the COM object on the server would take some toll that would
be bearable. We could make the COM object remote, but it would still be
one server. If instead the middle tier would get all trades to compute,
there could be several machines that each gets their load, so you can scale
better.

It's maybe not the best example, but I think it gives you the idea that
even if you put some logic in the middle tier, it may not be all logic,
but only some specific part.

Finally one more argument for having the logic in stored procedures: you
have all the code in one place. If you use the middle tier, you will jump
forth and back in different languages and the code is more difficult to
follow, debug and maintain.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp