• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Simple queries or Stored Procedures??

 
David Mathew
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34078
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34078
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
<<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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic