Weired Problem

Hello All,

I am trying to tune an application which uses cursor extensively. e.g
Let us say I wish to store data in a single table from various sources. What it is doing opening cursors and then storing values one by one. Logic is get rows from first table then keep on adding new rows and updating older rows as they come from different sources.

cursor c1 is select * from emp; -- insert all of it's rows to temp table
cursor c2 is select * from emp2; -- as an example cursor c2 is select * from emp3; as an example

for x in c1
loop

   insert into temptable values(x.a,x.b,x.c ,.......); end loop;

for x in c2
loop

    update temptable set c=c+x.c where a=x.a and b=x.b ;     if sql%NOTFOUND then

       insert into temptable values(x.a,x.b,x.c ,.......);     end if;
end loop;

for x in c3
loop

    update temptable set c=c+x.c where a=x.a and b=x.b ;     if sql%NOTFOUND then

       insert into temptable values(x.a,x.b,x.c ,.......);     end if;
end loop;

What I did , rewrite the procedure to implement the same logic

Insert into temptable
Select
A.a,A.b,A.c+nvl(B.c,0)+nvl(C.c,0)
from emp A,
emp2 B,
emp3 C
where A.a = B.a(+)

and A.a = C.a(+)
and A.b= B.b(+)
and A.b = C.b(+)

I assumed that this will run faster. To my disbelief it consumed same time as earlier version. Then I added /*+append */ hint . Again no change in performance. The join conditions were too complex and I hereby used simple example.

I donot know excatly, but once I did this sort of excercise in SQL Server and seen tremendous improvement . What could be the case here??

Any help is greatly appreciated.

With Warm regards
Jatinder Singh Received on Fri Apr 28 2006 - 05:31:55 CDT