wood burning stoves*
The moose likes JDBC and the fly likes Simple queries or Stored Procedures?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Simple queries or Stored Procedures??" Watch "Simple queries or Stored Procedures??" New topic
Author

Simple queries or Stored Procedures??

David Mathew
Greenhorn

Joined: Oct 25, 2003
Posts: 10
i am bit confused that in which situation one should use stored procedure and simple queries embedded in methods.
plz give ur opinions.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

David,
A stored procedure is useful if it can eliminate network traffic. For example, if I want to collect values from different tables and insert them into a new table, a stored procedure is useful.
If I have a simple query that can be execute in one shot, like select name from table, a stored procedure does not provide additional benefits.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861

If I have a simple query that can be execute in one shot, like select name from table, a stored procedure does not provide additional benefits.[/QB]

I like stored procs for the following reason:
1) They can be tested independently of java code, simply by using a standard SQL query tool
2) They can be maintained by non-java developers
3) They can be easily reused in other languages besides java.
4) They can easily be accessed from other Java applications
5) They hide the implementation and table structure from the java code (i.e. the java code only knows what they do, not how they do it)
6) They often offer better performance
The biggest reason people don't like them is that often the code in them is database dependent. But, in my experience most people don't stick to ANSI code in their sql anyway. I would hide the fact the fact that I am using stored procs (or straight sql for that matter) in an abstraction layer.
steve - http://www.fdsapi.com - the easiest way to render result sets as html and xml.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

I like stored procs for the following reason:
Steve,
I agree with comments 1, 2 and 3. I think I assumed that a Java programmer was asking as we are on javaranch. (In our shop the java developers maintain the db stuff too.)
Comment 4 is an excellent point. It definitely makes reuse easier. Although, you could just jar up the data access layer and reuse that.
For comment 5, the implementation and table structure are still abstracted from most of the code. Ideally, they are in DAO type object. I can see some benefit to details being in the database though.
For comment 6, do stored procs offer better performance for very simple queries?
I was thinking about a query like: select name from list where id = ?. This is standard SQL. I can't imagine how a stored procedure would give you much benefit over a prepared statement. But I could be wrong.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
<<For comment 6, do stored procs offer better performance for very simple queries?>>
They probably don't offer much improved performance, but improved performance is often touted as a benefit of stored procs (at least in Sybase ASE). Of the 6 reasons I mentioned this is the one I care the least about.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Simple queries or Stored Procedures??