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

.

Relevant Pages

  • RE: Oracle cursor help
    ... rids dbms_utility.uncl_array; ... where <your where clause> ... Subject: Re: Oracle cursor help ... exit the loop: */ ...
    (perl.dbi.users)
  • Command object stops prematurely without error?
    ... However, when run via the ADO Command object, the outer loop Applications ... Declare cApps CURSOR for Select ApplicationID from UCM_Applications ...
    (microsoft.public.data.ado)
  • Re: Calling a SP inside a cursor loop..
    ... every loop iteration of the cursor. ... Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2 ... and If the Fetch stmt is below the begin Stmt, the loop iterations are ... the proper way to program a cursor loop is: ...
    (comp.databases.ms-sqlserver)
  • Re: Weired Problem
    ... I am trying to tune an application which uses cursor extensively. ... cursor c2 is select * from emp3; ... end loop; ... What business problem are you trying to solve? ...
    (comp.databases.oracle.server)
  • Re: => Trigger to split Trailer Loads
    ... I am using a Trigger and a cursor with a while loop, however the recursive behaviour of the cursor is causing me ... I can successfully copy an order from tblOrders to tblSplitOrders, duplicating order details and splitting the load into ... Enters LineSplit>0 ...
    (microsoft.public.sqlserver.mseq)