• Post Reply Bookmark Topic Watch Topic
  • New Topic

Firing stored procedures parallely

 
Apurv Adarsh
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all.I am new to java ranch.
We have a situtation where we are firiing same SP thru diffrent threads so that performance of SP may be may be increased. However, we noticed that the SP is taking more time to execute when we fire it paralley.
For eg: 10 threads are taking as much time as 2 threads. Please help us.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65530
108
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"disturbed angel", please check your private messages for an important administrative matter.

 
Apurv Adarsh
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please Help
 
Henry Wong
author
Sheriff
Posts: 22542
109
C++ Chrome Eclipse IDE Firefox Browser Java jQuery Linux VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For eg: 10 threads are taking as much time as 2 threads. Please help us.


You are basically asking us for help with a performance issue, yet you have provided no performance data whatsoever about the running system.

Step 1... I would recommend looking at the load on the App Server and DB Server. If one of them is overloaded, then I would recommend focusing on that side of the system.

Step 2... For the DB and app server, there is no black magic here. You need profiling data. For example, if it is the App server that is under load, you need to profile it to see what is causing the load.

Henry
 
Apurv Adarsh
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We did some basic profiling and found out that the load on DB server is increasing as we increase the number of threads. If we take a 2 CPU box (DB server). It seams that even simple querries are taking propional increase in timings with the number of threads. I can't reproduce the exact data but here is what is a sample of what we found out for a chunk code of code which is executed in loop:

Threads: 1 4 10 20
Time(sec): 2 3 5 6

Hope you can provide some help. We scanned the code for single row updates and things like that but there were none
 
Henry Wong
author
Sheriff
Posts: 22542
109
C++ Chrome Eclipse IDE Firefox Browser Java jQuery Linux VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hope you can provide some help. We scanned the code for single row updates and things like that ...


I don't know what to tell you, as I already stated that there isn't a black magic solution here. For scaling issues, the only solution is to place the application under high load, and use a profiler to find the bottleneck... Searching for "things like that" is silly because it is the bottleneck that is preventing scaling.

Henry
 
Alan Mehio
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Threads: 1 4 10 20
Time(sec): 2 3 5 6



as far as I understand, you are executing an SP from java. Now the question start from the java side: how do you obtain the database connection to execute the SP. do you use the same connection or each thread create its own db connection? Then how do you configure your java application to get the connection? is it via the container throught a JNDI look up for a data source? or through the manager or through connection pooling based on a data source configure from the application?

 
Apurv Adarsh
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Each thread is creating its own connection thru a connection pool whic is common to whole application. Oracle AWR shows that max per querry wait time is taken by a querry which is burried deep inside our rule engine.However this querry is optimized perfetly(cost = 16). we got a slight performancw bost when we switced our DB from 8 core cpu to 16 core cpu. This was expected but even then there was a bottelneck present.
Even our DBA was unable to explain this
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!