|
|
You're in the
dotnet distributed apps
group:Stored procedures aren't scaleable?
dotnet distributed apps:
logic. From what I have heard, it is usually a good idea ***from a performance point of view*** to place business logic in stored procedures if the cost of parsing the logic is fairly large compared to the cost of accessing the data (e.g. if you place a simple select statement in a SP that returns tons of rows, the relative benefit of doing that might not be noticed). HOWEVER, there are other good arguements against placing business logic into stored procedures. They are much more difficult to debug (though I understand Microsoft is addressing that in the near future), are not object-oriented, can be edited/changed by customers (believe me, there are some pretty stupid customers out there :-), are not portable to other database vendors (if you care about that). I try and strike a balance between the specific performance improvement that a stored procedure would give me vs. the ease and flexibility of a business layer build using OO programing languages like C#. Eric "Paul Ritchie" <Paul.Ritchie@REMOVEpayglobalREMOVE.comREMOVE> wrote in message news:OQcmyjNmDHA.2488@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > What do you say to a person who believes that putting logic into stored
> procedures is not ultimately scaleable? > > eg He believes the database is the bottleneck and therefore all interaction > with it should be kept simple so as not to tax the server, with any kind of > data manipulation logic implemented outside the database in the business > logic (tier). > > cheers, > Paul Ritchie. > >
What do you say to a person who believes that putting logic into stored
procedures is not ultimately scaleable? eg He believes the database is the bottleneck and therefore all interaction with it should be kept simple so as not to tax the server, with any kind of data manipulation logic implemented outside the database in the business logic (tier). cheers, Paul Ritchie.
Used stored for data access/data manipulation - that's what they were
intended. BRs should be implemented in a layer above because programming langs are better equiped to handle the recursiveness and rules of BRs hence why MSFT is moving the CLR into SQL Server. Besides scalability, do you have a lot of custom code? Do clients or support personnel need to replace modules? Finally, its not easy to scale database servers out (you'll have to scale up), you can scale out more easily with app servers. ice "Paul Ritchie" <Paul.Ritchie@REMOVEpayglobalREMOVE.comREMOVE> wrote in message news:OQcmyjNmDHA.2488@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > What do you say to a person who believes that putting logic into stored > procedures is not ultimately scaleable? > > eg He believes the database is the bottleneck and therefore all interaction > with it should be kept simple so as not to tax the server, with any kind of > data manipulation logic implemented outside the database in the business > logic (tier). > > cheers, > Paul Ritchie. > >
Oracle 9i RAC
"Paul Ritchie" <Paul.Ritchie@REMOVEpayglobalREMOVE.comREMOVE> wrote in message news:OQcmyjNmDHA.2488@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > What do you say to a person who believes that putting logic into stored > procedures is not ultimately scaleable? > > eg He believes the database is the bottleneck and therefore all interaction > with it should be kept simple so as not to tax the server, with any kind of > data manipulation logic implemented outside the database in the business > logic (tier). > > cheers, > Paul Ritchie. > >
Thanks for your response Ice,
Forgive my ignorance but when does data manipulation become a business rule, thereby worthy of it's own tier? For example I believe that using stored procedures I can perform any business-rule/data-manipulation that our application will need to perform . This would appear to support a 2-tier argument. And MIcrosoft and IBM adding CLR to the database appears to me to only enhance that argument. For me that only leaves the scalability issue to justify a business-logic layer. Yes, it is easier to scale application servers more easily, but when is this an advantage? eg I can calculate the tax on an employee's payroll transactions for a year and write it back to his employee record. At what point is loading all 500 transaction records back to the middle tier, calculating tax on the total and writing it back to the server, going to be quicker than executing that on the server? I can sort of see the scalability argument in theory but when it comes to practice I can't help but wonder how complex my stored procedure is going to have to be before it executes slower than the total round trips required to assemble the data in the middle tier for calculation, then sending it back. To me this seems to be the crux of the argument, and if it is then I would really like to know how one calculates this extremely important business decision. cheers, Paul. [quoted text, click to view] "Ice" <ice@nospam.com> wrote in message news:OTM7jGimDHA.2160@TK2MSFTNGP10.phx.gbl... > Used stored for data access/data manipulation - that's what they were > intended. BRs should be implemented in a layer above because programming > langs are better equiped to handle the recursiveness and rules of BRs hence > why MSFT is moving the CLR into SQL Server. > > Besides scalability, do you have a lot of custom code? Do clients or > support personnel need to replace modules? > > Finally, its not easy to scale database servers out (you'll have to scale > up), you can scale out more easily with app servers. > > ice > "Paul Ritchie" <Paul.Ritchie@REMOVEpayglobalREMOVE.comREMOVE> wrote in > message news:OQcmyjNmDHA.2488@TK2MSFTNGP12.phx.gbl... > > What do you say to a person who believes that putting logic into stored > > procedures is not ultimately scaleable? > > > > eg He believes the database is the bottleneck and therefore all > interaction > > with it should be kept simple so as not to tax the server, with any kind > of > > data manipulation logic implemented outside the database in the business > > logic (tier). > > > > cheers, > > Paul Ritchie. > > > > > >
In my opinion if the business rule is simple, then putting it into a stored
procedure is fine. However complex rules such as those involving granular row level processing are difficult to debug in stored procedures, and are much better handled within a rich programming language like visual basic or c#. Stored procedures should be used to extract data and then commit changes back into the database, letting the stored procedure handle the transactions when possible. This lets the database do what it does best. "Paul Ritchie" <Paul.Ritchie@REMOVEpayglobalREMOVE.comREMOVE> wrote in message news:OQcmyjNmDHA.2488@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > What do you say to a person who believes that putting logic into stored > procedures is not ultimately scaleable? > > eg He believes the database is the bottleneck and therefore all interaction > with it should be kept simple so as not to tax the server, with any kind of > data manipulation logic implemented outside the database in the business > logic (tier). > > cheers, > Paul Ritchie. > >
Your example of calculating tax is actually very good for my thoughts on
this subject. Tax calculations can be incredibly complex things and worthy of business rule engines or using a remote webservice to do the actual calculations for you. In this scenario, only the job of storing the results in the database can be done by the SP. The calculation is the job of the middle tier using facade patterns behind which calls to webservices etc are made. Nowadays graphical business rule engines are getting better and in the near future plugging them into apps and easily using them will become a everyday occurrence. So keeping business logic out of SP's and in a middle tier will help to migrate to this situation. my tuppence worth .. cheers ben "Paul Ritchie" <Paul.Ritchie@REMOVEpayglobalREMOVE.comREMOVE> wrote in message news:eVAbCYMnDHA.3700@TK2MSFTNGP11.phx.gbl... [quoted text, click to view] > Thanks for your response Ice, > > Forgive my ignorance but when does data manipulation become a business rule, > thereby worthy of it's own tier? > > For example I believe that using stored procedures I can perform any > business-rule/data-manipulation that our application will need to perform .. > This would appear to support a 2-tier argument. And MIcrosoft and IBM > adding CLR to the database appears to me to only enhance that argument. > > For me that only leaves the scalability issue to justify a business-logic > layer. > > Yes, it is easier to scale application servers more easily, but when is this > an advantage? eg I can calculate the tax on an employee's payroll > transactions for a year and write it back to his employee record. At what > point is loading all 500 transaction records back to the middle tier, > calculating tax on the total and writing it back to the server, going to be > quicker than executing that on the server? > > I can sort of see the scalability argument in theory but when it comes to > practice I can't help but wonder how complex my stored procedure is going to > have to be before it executes slower than the total round trips required to > assemble the data in the middle tier for calculation, then sending it back. > > To me this seems to be the crux of the argument, and if it is then I would > really like to know how one calculates this extremely important business > decision. > > cheers, > Paul. > > "Ice" <ice@nospam.com> wrote in message > news:OTM7jGimDHA.2160@TK2MSFTNGP10.phx.gbl... > > Used stored for data access/data manipulation - that's what they were > > intended. BRs should be implemented in a layer above because programming > > langs are better equiped to handle the recursiveness and rules of BRs > hence > > why MSFT is moving the CLR into SQL Server. > > > > Besides scalability, do you have a lot of custom code? Do clients or > > support personnel need to replace modules? > > > > Finally, its not easy to scale database servers out (you'll have to scale > > up), you can scale out more easily with app servers. > > > > ice > > "Paul Ritchie" <Paul.Ritchie@REMOVEpayglobalREMOVE.comREMOVE> wrote in > > message news:OQcmyjNmDHA.2488@TK2MSFTNGP12.phx.gbl... > > > What do you say to a person who believes that putting logic into stored > > > procedures is not ultimately scaleable? > > > > > > eg He believes the database is the bottleneck and therefore all > > interaction > > > with it should be kept simple so as not to tax the server, with any kind > > of > > > data manipulation logic implemented outside the database in the business > > > logic (tier). > > > > > > cheers, > > > Paul Ritchie. > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
| home · blog · groups | Questions? Comments? Contact the dn |