Stored procedures aren't scaleable?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Ritchie - 22 Oct 2003 21:16 GMT

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.

Reply to this Message

Eric Johannsen - 23 Oct 2003 00:11 GMT

I would say that can depend on your environment and the specific business
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

> What do you say to a person who believes that putting logic into stored
> procedures is not ultimately scaleable?
[quoted text clipped - 6 lines]
> cheers,
> Paul Ritchie.

Reply to this Message

Ice - 24 Oct 2003 12:26 GMT

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

> What do you say to a person who believes that putting logic into stored
> procedures is not ultimately scaleable?
[quoted text clipped - 6 lines]
> cheers,
> Paul Ritchie.

Reply to this Message

Paul Ritchie - 27 Oct 2003 21:10 GMT

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.

> Used stored for data access/data manipulation - that's what they were
> intended. BRs should be implemented in a layer above because programming
[quoted text clipped - 20 lines]
> > cheers,
> > Paul Ritchie.

Reply to this Message

Ben - 22 Nov 2003 00:58 GMT

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

> Thanks for your response Ice,
>
[quoted text clipped - 53 lines]
> > > cheers,
> > > Paul Ritchie.

Reply to this Message

brad more - 24 Oct 2003 16:23 GMT

Oracle 9i RAC

> What do you say to a person who believes that putting logic into stored
> procedures is not ultimately scaleable?
[quoted text clipped - 6 lines]
> cheers,
> Paul Ritchie.

Reply to this Message

Frank - 06 Nov 2003 15:04 GMT

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.

> What do you say to a person who believes that putting logic into stored
> procedures is not ultimately scaleable?
[quoted text clipped - 6 lines]
> cheers,
> Paul Ritchie.

Reply to this Message

Stored procedures aren't scaleable?

Free Magazines