File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB and other Java EE Technologies and the fly likes Performence issue:Write query directly in session bean or call storted procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Performence issue:Write query directly in session bean or call storted procedure" Watch "Performence issue:Write query directly in session bean or call storted procedure" New topic
Author

Performence issue:Write query directly in session bean or call storted procedure

chandr prakash
Ranch Hand

Joined: Jan 29, 2011
Posts: 32
Enviornment : Weblogic 10.x,Oracle11gR2,EJB3
Both ApplicationServer and Database server are installed on different machine.From my sessionBean deployed on application server i am retrieve some rows from database(table Department).There are 40000000(40 milion rows) rows are present in database table. Data source is deployed on application server.To do this i wrote two approaches.
Approach1
In this approach i am calling stored procedure (GET_NOS) from my session bean.
SessionBean



Stored Procedure



Approach 2 : In this approach i didnt use stored procedure and wrote sql query in session bean code.(The only difference between both approach is, i wrote same query directly in session bean.)
Session bean


Both Approaches are working successfully,but for same input, approach 1 is 10 times slower than approach2.(All conditions are exactly same while testing). For same input if i run code then approach2 is taking less time, and i dont want to write quey in session bean,i want to use stored procedure.
What am i doing wrong in approach1? please reply
chandr prakash
Ranch Hand

Joined: Jan 29, 2011
Posts: 32
Kindly reply!
Andrew Moko
Ranch Hand

Joined: Dec 16, 2011
Posts: 55
Definitely the problem lies with your SP.
Andrew Moko
Ranch Hand

Joined: Dec 16, 2011
Posts: 55
Run the SP seperately on your database and calculate the time it takes to execute
john lazeraski
Ranch Hand

Joined: Nov 14, 2011
Posts: 76
I would have thought a stored procedure would be MUCH faster than jdbc query?
Andrew Moko
Ranch Hand

Joined: Dec 16, 2011
Posts: 55
john lazeraski wrote:I would have thought a stored procedure would be MUCH faster than jdbc query?


Yes, but not when it's wrongly written
chandr prakash
Ranch Hand

Joined: Jan 29, 2011
Posts: 32
thankx for reply!
@Andrew
Yeah,its true.....when i executed stored procedure from sqlplus stand alone, its taking time 10 times as comapred to jdbc query. what is wrong with my stored procedure...where it got stuck up?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performence issue:Write query directly in session bean or call storted procedure
 
Similar Threads
Prepared Statement and Date class
java.sql.Timestamp to Oracle date
SQLException Cursor Closed
Oracler Date function
Prepared Statement and Date class - Problem still exists