Design question - stored prosedures vs. components

It is a design question which affects SQL Server.
Scenario A. Business logic in SQL Server stored procedures. No middle tier
components is used. As a result the stored procedures are very long, up to
5,000 lines.
Scenario B. Business logic is in components. Stored procedures are used for
elementary operations, insert, update, delete and therefore are very short.
Components have code that glues everything together. Components can run on a
separate server.
In my opinion, scenario A doesn't scale well and causes very high cpu
utilization on SQL Server but I would like to listen somebody else's opinion
on that ...
Thanks,
-Stan"Stan" <nospam@.yahoo.com> wrote in message
news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> It is a design question which affects SQL Server.
> Scenario A. Business logic in SQL Server stored procedures. No middle tier
> components is used. As a result the stored procedures are very long, up to
> 5,000 lines.
> Scenario B. Business logic is in components. Stored procedures are used
for
> elementary operations, insert, update, delete and therefore are very
short.
> Components have code that glues everything together. Components can run on
a
> separate server.
> In my opinion, scenario A doesn't scale well and causes very high cpu
> utilization on SQL Server but I would like to listen somebody else's
opinion
> on that ...
> Thanks,
> -Stan
Stan,
Let me give you my opinion. We have a similar set up at our location.
We developed a system where all business logic laid in SQLServer SPs,
triggers, etc, and no middle tier. Only light validations were performed at
the UI and the heavy stuff took place at the database server. Pros: Any
change in business rules affected 80%-100% the database layer (SPs) and
0%-20% on the client side. This made it a lot easier for us to modify or
correct code, save and go, without having too much impact on the client
layer. Also, having all database objects at hand (tables, views, other SPs,
etc.) is always a great advantage if you place your business logic in SPs.
Cons: As you said, it causes very high CPU utilization, but that will always
depend on the hardware you are using, and how intense will be used your
application. This drawback may be compensated with a good state-of-the-art
server, if feasible.
Having business logic in a middle tier also has its great advantages, and
may free your database server machine of hardwork. But then, in my opinion,
you may have to deal with C++/VB (or whatever language your are using) code,
that definitely is more difficult to maintain than SQL statements.
In conclusion, there is actually no better approach between these two. That
will depend on the requirements specifications for your system (how
complicate it will be, what would fit better based on your requirements) and
the expected usage.
C.|||I agree entirely with Carlitos, but would like to add... for MAXIMUM
scalability you must go to a middle tier... Like Carlitos, I find loading
the back end, easy and quick to create and maintain... However, if you need
HIGHEST scalability, when you have already purchased the largest SQL Server
box that money can buy, what do you do?... you have to move work OFF of the
SQL Server onto another box - middle tier...
There is no best answer - except the one that meets your needs
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Stan" <nospam@.yahoo.com> wrote in message
news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> It is a design question which affects SQL Server.
> Scenario A. Business logic in SQL Server stored procedures. No middle tier
> components is used. As a result the stored procedures are very long, up to
> 5,000 lines.
> Scenario B. Business logic is in components. Stored procedures are used
for
> elementary operations, insert, update, delete and therefore are very
short.
> Components have code that glues everything together. Components can run on
a
> separate server.
> In my opinion, scenario A doesn't scale well and causes very high cpu
> utilization on SQL Server but I would like to listen somebody else's
opinion
> on that ...
> Thanks,
> -Stan
>|||I concur with Wayne in what he added.
C.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I agree entirely with Carlitos, but would like to add... for MAXIMUM
> scalability you must go to a middle tier... Like Carlitos, I find loading
> the back end, easy and quick to create and maintain... However, if you
need
> HIGHEST scalability, when you have already purchased the largest SQL
Server
> box that money can buy, what do you do?... you have to move work OFF of
the
> SQL Server onto another box - middle tier...
> There is no best answer - except the one that meets your needs
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > It is a design question which affects SQL Server.
> >
> > Scenario A. Business logic in SQL Server stored procedures. No middle
tier
> > components is used. As a result the stored procedures are very long, up
to
> > 5,000 lines.
> >
> > Scenario B. Business logic is in components. Stored procedures are used
> for
> > elementary operations, insert, update, delete and therefore are very
> short.
> > Components have code that glues everything together. Components can run
on
> a
> > separate server.
> >
> > In my opinion, scenario A doesn't scale well and causes very high cpu
> > utilization on SQL Server but I would like to listen somebody else's
> opinion
> > on that ...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||I agree - it all depends on situation.
I am trying to feel the magnitude of a difference between these two
scenarios.
It is amazingly easy, though, to load 8-way the-best-money-can-buy SQL
server box
with stored procedures that are long, run often and use a lot of temp tables
...
Despite that, there are a lot of vendors and developers who opt for keeping
business logic on SQL Server...
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I agree entirely with Carlitos, but would like to add... for MAXIMUM
> scalability you must go to a middle tier... Like Carlitos, I find loading
> the back end, easy and quick to create and maintain... However, if you
need
> HIGHEST scalability, when you have already purchased the largest SQL
Server
> box that money can buy, what do you do?... you have to move work OFF of
the
> SQL Server onto another box - middle tier...
> There is no best answer - except the one that meets your needs
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Stan" <nospam@.yahoo.com> wrote in message
> news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > It is a design question which affects SQL Server.
> >
> > Scenario A. Business logic in SQL Server stored procedures. No middle
tier
> > components is used. As a result the stored procedures are very long, up
to
> > 5,000 lines.
> >
> > Scenario B. Business logic is in components. Stored procedures are used
> for
> > elementary operations, insert, update, delete and therefore are very
> short.
> > Components have code that glues everything together. Components can run
on
> a
> > separate server.
> >
> > In my opinion, scenario A doesn't scale well and causes very high cpu
> > utilization on SQL Server but I would like to listen somebody else's
> opinion
> > on that ...
> >
> > Thanks,
> >
> > -Stan
> >
> >
>|||You have to get rid of the temp tables then, they are expensive resource
wise. A lot of cases I have seen where temporary tables were used in stored
procedures they could be rewritten into one SQL statement, usually with
derived tables, and performance went up by what I think is the rule-of-thumb
for these kind of scenarios: 50 times. Yes, you read that correct, writing
proper SQL code instead of using a procedural approach and temp tables (and
even worse cursors) makes your code run 50 times as fast.
Jacco Schalkwijk
SQL Server MVP
"Stan" <nospam@.yahoo.com> wrote in message
news:O8Nbr9pjDHA.3688@.TK2MSFTNGP11.phx.gbl...
> I agree - it all depends on situation.
> I am trying to feel the magnitude of a difference between these two
> scenarios.
> It is amazingly easy, though, to load 8-way the-best-money-can-buy SQL
> server box
> with stored procedures that are long, run often and use a lot of temp
tables
> ...
> Despite that, there are a lot of vendors and developers who opt for
keeping
> business logic on SQL Server...
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:uiic90pjDHA.3504@.TK2MSFTNGP11.phx.gbl...
> > I agree entirely with Carlitos, but would like to add... for MAXIMUM
> > scalability you must go to a middle tier... Like Carlitos, I find
loading
> > the back end, easy and quick to create and maintain... However, if you
> need
> > HIGHEST scalability, when you have already purchased the largest SQL
> Server
> > box that money can buy, what do you do?... you have to move work OFF of
> the
> > SQL Server onto another box - middle tier...
> >
> > There is no best answer - except the one that meets your needs
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> >
> > "Stan" <nospam@.yahoo.com> wrote in message
> > news:eUc1IQpjDHA.708@.TK2MSFTNGP10.phx.gbl...
> > > It is a design question which affects SQL Server.
> > >
> > > Scenario A. Business logic in SQL Server stored procedures. No middle
> tier
> > > components is used. As a result the stored procedures are very long,
up
> to
> > > 5,000 lines.
> > >
> > > Scenario B. Business logic is in components. Stored procedures are
used
> > for
> > > elementary operations, insert, update, delete and therefore are very
> > short.
> > > Components have code that glues everything together. Components can
run
> on
> > a
> > > separate server.
> > >
> > > In my opinion, scenario A doesn't scale well and causes very high cpu
> > > utilization on SQL Server but I would like to listen somebody else's
> > opinion
> > > on that ...
> > >
> > > Thanks,
> > >
> > > -Stan
> > >
> > >
> >
> >
>|||On Thu, 9 Oct 2003 21:57:14 +0100, "Jacco Schalkwijk"
<NOSPAMjaccos@.eurostop.co.uk> wrote:
>You have to get rid of the temp tables then, they are expensive resource
>wise. A lot of cases I have seen where temporary tables were used in stored
>procedures they could be rewritten into one SQL statement, usually with
>derived tables, and performance went up by what I think is the rule-of-thumb
>for these kind of scenarios: 50 times. Yes, you read that correct, writing
>proper SQL code instead of using a procedural approach and temp tables (and
>even worse cursors) makes your code run 50 times as fast.
I just want to concur with what so far is an amazingly consistent
thread!
Putting everything in the back end is probably extreme, as is putting
nothing in the back end. I will share this wisdom with you, that what
the back end is all about is not tables, but a data model. If all
your middle tier asks of the database is simple stuff, it is extreme
in another dimension, and doing things the hard way.
5,000 lines is way too long for a single SP, I'd guestimate from just
that, that something is majorly wrong. Lots of cursors, too, I'll
bet, unnecessary and horribly slow cursors, is what I mean. Clean up
the database tier, and the entire problem might just vanish. I've
heard that it's easy to load up an 8-way system with SQLServer because
neither Wintel nor SQLServer is very good at it, but the only case
I've actually seen had such crude code running on it, that it was
really inappropriate to bitch about the hardware.
No easy answers.
Joshua Stern|||Hi Stan,
Yes, there is no easy answer. I'm including some supplemental information
below:
Where Should Business Logic Go?
http://www.4guysfromrolla.com/webtech/121501-1.shtml
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.