Win a copy of Practical SVG this week in the HTML/CSS/JavaScript forum!
  • 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: 77
  • 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?
 
Liar, liar, pants on fire! refreshing plug:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!