| Performance slow due to repeated execution. [message #65417] |
Thu, 16 September 2004 00:11  |
Reema
Messages: 50
Registered: July 2003
|
Member |
|
|
Hi All,
We are facing a peculiar problem. We have a stored proc which is executed repeatedly executed 1 lac 20,000 times. one iteration is taking 60 msec. Now we r facing the problem that 1 lac 20,000 thousand iterations make it a very expansive procedure taking 3 hrs to run. According to our requirements it shud take 45 minutes to run or max an hour. The business rules are such that we can't change the model of execution.
Can anybody point out some solution to it or pointers to such a solution.
Regards
Reema
|
|
|
|
| Re: Performance slow due to repeated execution. [message #65425 is a reply to message #65417] |
Sat, 18 September 2004 07:01  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If 1 execution takes 60 msec, then 120000 (I guess that 1 lac = 100 000) execs take AT LEAST 120 000 * .060 = 7200 secs = 2 hours.
There are a few things that might speed things up:
- If your proc is not in pl/sql, make sure you use binds.
- Work in sets (bulk collect, forall)
- use SQL whenever possible instead of pl/sql
120 000 executions of a single proc sounds a bit like a wrong design anyway. Try to do things in single SQL-statements !
hth
Frank
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
