By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,275 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,275 IT Pros & Developers. It's quick & easy.

Simple question but smart answer!

P: n/a
Yesterday I had a heated discussion with my colleagues on what is a
data centric application and having business logic in sql. I have
group of people who wants to include all the business logic in the sql
stored proc and I for one consider to have most of the business logic
in the C# code (specifically in a controller class of MVC). These are
my points to them.

1. Our application depends a lot on the data and we have huge amount
of data in the sql server and all it has to do is simple SQL
operations and some complicated selects. Nothing more.
2. If we have most of the business logic in the C# code, it is very
easy to test and easy to maintain if it is developed using TDD. (Using
mock data we can even hide the sql side for our testing). It is little
difficult to test sql side in CI environment.
3. App servers are cheap compared to sql servers and it is easy scale
app servers.
4. It easy to get C# developers than SQL developers.

I know I can get smart answers from this group. Could you please let
me know what do you all think?
Oct 18 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
CSharper wrote:
Yesterday I had a heated discussion with my colleagues on what is a
data centric application and having business logic in sql.
I have group of people who wants to include all the business logic in the
sql stored proc
This is usually a bad idea, but it depends on your goals.
and I for one consider to have most of the business logic in the C# code
(specifically in a controller class of MVC).
This is usually a good idea, but it depends on your goals.
These are my points to them.

1. Our application depends a lot on the data and we have huge amount
of data in the sql server and all it has to do is simple SQL
operations and some complicated selects. Nothing more.
Amount of data is actually an argument in favor of putting logic in stored
procedures. The fastest way of processing data is doing it right at the
database. While T-SQL takes a performance hit from being an interpreted
language, this usually pales to the performance hit from shuffling data over
the network (including the waits for roundtrips).

Complexity of operation, on the other hand, is an argument in favor of
writing the logic in C#, or any programming language more convenient than
T-SQL (which is practically all of them). That said, SQL is capable of
complex set-based operations that would be much more tedious to write in C#,
so what is "complex" varies.

For business logic, "complex" usually means "contrived", from a programming
point of view: this action is allowed but only if such-and-such condition is
satisfied, unless... This needs maintainability and robustness over
everything else, which makes T-SQL a poor choice (as we'll get to in a moment).

That said, putting as much "trivial" consistency and validation in your
database as possible is still a good idea. Everything will need to pass it
before being stored, so any errors you can catch there are things you never
need to worry about. Inconsistent data is a huge pain to fix afterwards, if
it can be fixed at all. Anything you *can* chisel in stone regardless of the
variable business logic (like "the price of an item may not be negative")
*should* be chiseled in stone, in the form of constraints. Where to draw the
line is a design issue.

Even this is *in addition to* checking it at the business logic level, not
*instead of*, as your database usually can't tell you what was wrong with
the data on a logical level, and you (usually) can't subject your users to
an "INSERT statement violates constraint 'CK_Price_not_negative'" message.
This is also another argument against putting your business logic in stored
procedures: you'll have to devise a consistent, machine-readable way for
reporting errors back and stick to it. Error-handling strategies are
difficult enough to get right for applications written in one language, let
alone for those written in two.
2. If we have most of the business logic in the C# code, it is very
easy to test and easy to maintain if it is developed using TDD. (Using
mock data we can even hide the sql side for our testing). It is little
difficult to test sql side in CI environment.
Ease of development and quality of implementation are the most important
arguments against logic in stored procedures: T-SQL is slow and clumsy,
tends to produce reams of code that's hard to structure (passing information
between stored procedures is an art unto itself), is tricky to write
robustly (even with the new TRY statement) and is hard to version. All this
is bad news for maintainability.

A less mentioned drawback (because most people don't care about this
scenario) is that T-SQL (procedural SQL in general) isn't portable. If you
want to move to another RDBMS to store your data, good luck doing that if
you've constructed 2,000 line stored procedures that only work on SQL
Server. Not that porting your data to another RDBMS is trivial if you don't
do this, but at least it's a lot less impractical.

One argument that has to be mentioned as definitely in favor of T-SQL, on
the other hand, is flexibility. Executing a single ALTER PROCEDURE statement
to fix a problem is significantly faster than having to roll out a new
version of your application. Of course, this is again bad news for
maintainability, because tracking down a problem caused by some bozo making
a SELECT procedure do a stealth INSERT somewhere is no fun. Being able to do
this might save your bacon one day, but as a certain arachnid superhero is
wont to say, with great power comes great responsibility.
3. App servers are cheap compared to sql servers and it is easy scale
app servers.
This depends on what you're doing more: retrieving data or processing data.
With a separate app server, processing data is offloaded from the database
server at the expense of extra data traffic. The amount of data retrieved
may or may not increase depending on how much logic is redistributed. If
you're using one "dumb" SQL server that has to pump boatloads of data to
many "smart" app servers, processing power will not be distributed evenly,
and your database (and network) will need scaling anyway.
4. It easy to get C# developers than SQL developers.
While this bolsters point #2 (T-SQL is harder to maintain), you can't get
away with having no SQL developers either because, left to their own
devices, the C# developers are likely to construct databases and queries
that are grossly inefficient for large data sets. Not because they're dumb,
but because appreciating the performance differences between in-memory data
structures and on-disk tables takes some experience.

The whole point of SQL is to shield you from these concerns, so programmers
will write queries expecting that the database will take care of everything
somehow, but this abstraction will eventually break down when the data goes
past a critical size. Retooling the design after the fact can be costly and
incur downtime. Upgrading the server(s) is a simple and thought-free
solution, but not always a cost-effective or practical one.

That said, for most purposes it's sufficient to have one or two C#
programmers with experience who can advise the others on database structure
and potential performance issues, so the design is at worst "not optimal"
instead of "critically deficient". I think I can fairly confidently say
this, because I'm one of those programmers at my company. :-)

--
J.
Oct 18 '08 #2

P: n/a
I guess, since this is a C# group, most people would side with you.
However, if you post the same question in the SQL group, you might get
a different answer.

I've played around with SQL and, like Linq and Lambda notation in C#,
it takes a while to get used to, but, after about a couple of weeks of
it you start getting the hang of it. Also Visual Studio has a "drag
and drop" system of checkboxes and links that will allow you to
graphically construct common SQL queries based on columns of
databases, which is kind of neat.

I don't understand your points #2 and #3 below, but maybe I'm
inexperienced in this area. What is TDD? What is an "app server" as
compared to a SQL server? The latter pays a license to Microsoft?
Big deal if so; you can afford to pay if you're a quality company.

RL

CSharper wrote:
Yesterday I had a heated discussion with my colleagues on what is a
data centric application and having business logic in sql. I have
group of people who wants to include all the business logic in the sql
stored proc and I for one consider to have most of the business logic
in the C# code (specifically in a controller class of MVC). These are
my points to them.

1. Our application depends a lot on the data and we have huge amount
of data in the sql server and all it has to do is simple SQL
operations and some complicated selects. Nothing more.
2. If we have most of the business logic in the C# code, it is very
easy to test and easy to maintain if it is developed using TDD. (Using
mock data we can even hide the sql side for our testing). It is little
difficult to test sql side in CI environment.
3. App servers are cheap compared to sql servers and it is easy scale
app servers.
4. It easy to get C# developers than SQL developers.

I know I can get smart answers from this group. Could you please let
me know what do you all think?
Oct 18 '08 #3

P: n/a
raylopez99 wrote:
I don't understand your points #2 and #3 below, but maybe I'm
inexperienced in this area. What is TDD?
Test Driven Development
What is an "app server" as
compared to a SQL server?
IIS & ASP.NET

Arne
Oct 18 '08 #4

P: n/a
On Oct 18, 12:54*pm, Jeroen Mostert <jmost...@xs4all.nlwrote:
CSharper wrote:
Yesterday I had a heated discussion with my colleagues on what is a
data centric application and having business logic in sql.
I have group of people who wants to include all the business logic in the
sql stored proc

This is usually a bad idea, but it depends on your goals.
and I for one consider to have most of the business logic in the C# code
(specifically in a controller class of MVC).

This is usually a good idea, but it depends on your goals.
These are my points to them.
1. Our application depends a lot on the data and we have huge amount
of data in the sql server and all it has to do is simple SQL
operations and some complicated selects. Nothing more.

Amount of data is actually an argument in favor of putting logic in stored
procedures. The fastest way of processing data is doing it right at the
database. While T-SQL takes a performance hit from being an interpreted
language, this usually pales to the performance hit from shuffling data over
the network (including the waits for roundtrips).

Complexity of operation, on the other hand, is an argument in favor of
writing the logic in C#, or any programming language more convenient than
T-SQL (which is practically all of them). That said, SQL is capable of
complex set-based operations that would be much more tedious to write in C#,
so what is "complex" varies.

For business logic, "complex" usually means "contrived", from a programming
point of view: this action is allowed but only if such-and-such conditionis
satisfied, unless... This needs maintainability and robustness over
everything else, which makes T-SQL a poor choice (as we'll get to in a moment).

That said, putting as much "trivial" consistency and validation in your
database as possible is still a good idea. Everything will need to pass it
before being stored, so any errors you can catch there are things you never
need to worry about. Inconsistent data is a huge pain to fix afterwards, if
it can be fixed at all. Anything you *can* chisel in stone regardless of the
variable business logic (like "the price of an item may not be negative")
*should* be chiseled in stone, in the form of constraints. Where to draw the
line is a design issue.

Even this is *in addition to* checking it at the business logic level, not
*instead of*, as your database usually can't tell you what was wrong with
the data on a logical level, and you (usually) can't subject your users to
an "INSERT statement violates constraint 'CK_Price_not_negative'" message..
This is also another argument against putting your business logic in stored
procedures: you'll have to devise a consistent, machine-readable way for
reporting errors back and stick to it. Error-handling strategies are
difficult enough to get right for applications written in one language, let
alone for those written in two.
2. If we have most of the business logic in the C# code, it is very
easy to test and easy to maintain if it is developed using TDD. (Using
mock data we can even hide the sql side for our testing). It is little
difficult to test sql side in CI environment.

Ease of development and quality of implementation are the most important
arguments against logic in stored procedures: T-SQL is slow and clumsy,
tends to produce reams of code that's hard to structure (passing information
between stored procedures is an art unto itself), is tricky to write
robustly (even with the new TRY statement) and is hard to version. All this
is bad news for maintainability.

A less mentioned drawback (because most people don't care about this
scenario) is that T-SQL (procedural SQL in general) isn't portable. If you
want to move to another RDBMS to store your data, good luck doing that if
you've constructed 2,000 line stored procedures that only work on SQL
Server. Not that porting your data to another RDBMS is trivial if you don't
do this, but at least it's a lot less impractical.

One argument that has to be mentioned as definitely in favor of T-SQL, on
the other hand, is flexibility. Executing a single ALTER PROCEDURE statement
* to fix a problem is significantly faster than having to roll out a new
version of your application. Of course, this is again bad news for
maintainability, because tracking down a problem caused by some bozo making
a SELECT procedure do a stealth INSERT somewhere is no fun. Being able todo
this might save your bacon one day, but as a certain arachnid superhero is
wont to say, with great power comes great responsibility.
3. App servers are cheap compared to sql servers and it is easy scale
app servers.

This depends on what you're doing more: retrieving data or processing data.
With a separate app server, processing data is offloaded from the database
server at the expense of extra data traffic. The amount of data retrieved
may or may not increase depending on how much logic is redistributed. If
you're using one "dumb" SQL server that has to pump boatloads of data to
many "smart" app servers, processing power will not be distributed evenly,
and your database (and network) will need scaling anyway.
4. It easy to get C# developers than SQL developers.

While this bolsters point #2 (T-SQL is harder to maintain), you can't get
away with having no SQL developers either because, left to their own
devices, the C# developers are likely to construct databases and queries
that are grossly inefficient for large data sets. Not because they're dumb,
but because appreciating the performance differences between in-memory data
structures and on-disk tables takes some experience.

The whole point of SQL is to shield you from these concerns, so programmers
will write queries expecting that the database will take care of everything
somehow, but this abstraction will eventually break down when the data goes
past a critical size. Retooling the design after the fact can be costly and
incur downtime. Upgrading the server(s) is a simple and thought-free
solution, but not always a cost-effective or practical one.

That said, for most purposes it's sufficient to have one or two C#
programmers with experience who can advise the others on database structure
and potential performance issues, so the design is at worst "not optimal"
instead of "critically deficient". I think I can fairly confidently say
this, because I'm one of those programmers at my company. :-)

--
J.
J, excellent answer and I learned a lot from your answer.
Oct 18 '08 #5

P: n/a
CSharper wrote:
Yesterday I had a heated discussion with my colleagues on what is a
data centric application and having business logic in sql. I have
group of people who wants to include all the business logic in the sql
stored proc and I for one consider to have most of the business logic
in the C# code (specifically in a controller class of MVC). These are
my points to them.
The more important thing is:

Have only one copy of the logic!

It can be in the stored procedure or it can be in a .NET assembly shared by
all applications. But don't let each application have it's own version.

The controller class is the wrong place to put it. The controller is
supposed to handle transferring data between model and view, constraints
should be enforced at the model. (Plus models or parts thereof are more
likely to be shared between applications).
Oct 20 '08 #6

P: n/a
On Oct 20, 4:45*pm, "Ben Voigt [C++ MVP]" <r...@nospam.nospamwrote:
CSharper wrote:
Yesterday I had a heated discussion with my colleagues on what is a
data centric application and having business logic in sql. I have
group of people who wants to include all the business logic in the sql
stored proc and I for one consider to have most of the business logic
in the C# code (specifically in a controller class of MVC). These are
my points to them.

The more important thing is:

Have only one copy of the logic!

It can be in the stored procedure or it can be in a .NET assembly shared by
all applications. *But don't let each application have it's own version..

The controller class is the wrong place to put it. *The controller is
supposed to handle transferring data between model and view, constraints
should be enforced at the model. *(Plus models or parts thereof are more
likely to be shared between applications).
Question, what is the right place to have this logic? Is it possible
to have the validation done in c# code with all the nunit and then use
the C# dll in the sql? is it something you are thinking or something
else that I completly missed?
Thanks.
Oct 21 '08 #7

P: n/a
CSharper wrote:
On Oct 20, 4:45 pm, "Ben Voigt [C++ MVP]" <r...@nospam.nospamwrote:
>CSharper wrote:
>>Yesterday I had a heated discussion with my colleagues on what is a
data centric application and having business logic in sql. I have
group of people who wants to include all the business logic in the
sql stored proc and I for one consider to have most of the business
logic in the C# code (specifically in a controller class of MVC).
These are my points to them.

The more important thing is:

Have only one copy of the logic!

It can be in the stored procedure or it can be in a .NET assembly
shared by all applications. But don't let each application have it's
own version.

The controller class is the wrong place to put it. The controller is
supposed to handle transferring data between model and view,
constraints should be enforced at the model. (Plus models or parts
thereof are more likely to be shared between applications).

Question, what is the right place to have this logic? Is it possible
to have the validation done in c# code with all the nunit and then use
the C# dll in the sql? is it something you are thinking or something
else that I completly missed?
Thanks.
NUnit has to do with code testing, not data validation. I think with new
versions of MS SQL Server you can write stored procedures in C#. But what I
was suggesting was simply putting that logic in a reusable .NET assembly
("data access layer" is what you might hear people call it) that you
reference from each webforms app that uses that database.
Oct 21 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.