aspose 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 Spring in Action this week in the Spring 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