Home » RDBMS Server » Performance Tuning » Performance slow due to repeated execution.
Performance slow due to repeated execution. [message #65417] Thu, 16 September 2004 00:11 Go to next message
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 Go to previous message
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
Previous Topic: display no. of seconds elapsed
Next Topic: Rolling ports problem on WAN
Goto Forum:
  


Current Time: Fri Mar 29 10:00:35 CDT 2024