• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
chandr prakash
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kindly reply!
 
Andrew Moko
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Definitely the problem lies with your SP.
 
Andrew Moko
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Run the SP seperately on your database and calculate the time it takes to execute
 
john lazeraski
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would have thought a stored procedure would be MUCH faster than jdbc query?
 
Andrew Moko
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic