The Artima Developer Community
Sponsored Link

Weblogs Forum
Problems with using stored procedures

17 replies on 2 pages. Most recent reply: Nov 17, 2005 3:46 AM by jian wu

Welcome Guest
  Sign In

Go back to the topic listing  Back to Topic List Click to reply to this topic  Reply to this Topic Click to search messages in this forum  Search Forum Click for a threaded view of the topic  Threaded View   
Previous Topic   Next Topic
Flat View: This topic has 17 replies on 2 pages [ « | 1 2 ]
Dave Nicolette

Posts: 6
Nickname: dnicolet
Registered: Sep, 2005

Re: Problems with using stored procedures Posted: Sep 23, 2005 12:43 PM
Reply to this message Reply
Advertisement
Some great points about placing business logic in stored procedures, about portability among DBMSs, about organizational or cultural conflicts, about the potential for minor bugs to affect many applications, and about internal vs external procedures.

It occurs to me that historically the reason some business logic may have been placed in stored procedures was because a solution's architecture called for some logic to be centralized and other logic to be distributed, rather than because of any special functionality unique to stored procedures, or because of any reason at all that was really related to database functionality. Isn't it the case that with the multi-tiered architectures common today, we really don't need to resort to stuffing business logic into stored procedures to accomplish the same goal? Seems like that's an old approach, and never was anything more than a workaround at best.

The question of reducing the number of calls to the DBMS over the network is at least partly solved by having database access code isolated in an application layer that lives on a centralized server; what some people call the 'business tier' or the 'EJB tier'. With that in mind, is this a real reason to resort to stored procedures? It may be a solution to the wrong problem.

The issue of work specialization was mentioned; some people prefer data modeling and others coding, and they may approach things differently. Doesn't the advent of Agile methods partially alleviate that problem? When building a solution that involved object-oriented code development and relational data modeling, you would normally pair with someone who had the other, complementary skill, and between the two of you you'd come up with a solid solution. It may be that some of the answers aren't technical at all.

David Vydra

Posts: 60
Nickname: dvydra
Registered: Feb, 2004

Re: Problems with using stored procedures Posted: Sep 29, 2005 1:24 AM
Reply to this message Reply
Has anyone tried using java stored procedure? Seems like this approach should reduce the tension between between application developers and database folks.

jian wu

Posts: 1
Nickname: jianwu
Registered: Nov, 2005

Re: Problems with using stored procedures Posted: Nov 17, 2005 3:46 AM
Reply to this message Reply
Two points why you should use Stored Procedures:

1. Reduce data traffic between the AppServer and DB Server
unless you are only developing a prototype with only
1000 rows in your tables:-)

2. Reduce the concurrent connections required at the
Mid-tier, even though most App Server support
connection pool, the limit will be very soon
reached if you have a lot of users hitting the server.

Obviously, you need good DB developers not DBAs to make
these store-procedures very well programmed and maintained,
you should think that stored-procedures are programs just
like any other Java Programs not some admin scripts.

Flat View: This topic has 17 replies on 2 pages [ « | 1  2 ]
Topic: C++ Cookbook from O'Reilly Previous Topic   Next Topic Topic: Introducing the Iterable Concept


Sponsored Links



Google
  Web Artima.com   

Copyright © 1996-2014 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use - Advertise with Us