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.
In this approach i am calling stored procedure (GET_NOS) from my session bean.
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.)
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
I would have thought a stored procedure would be MUCH faster than jdbc query?
Joined: Dec 16, 2011
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
Joined: Jan 29, 2011
thankx for reply!
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?
subject: Performence issue:Write query directly in session bean or call storted procedure