Re: Weired Problem

"jsfromynr" <jatinder.1975_at_gmail.com> wrote in message news:1146220315.351412.122760_at_j33g2000cwa.googlegroups.com...
> 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
>

Oracle!=SQL Server

What business problem are you trying to solve? You have posed a solution, but not why you are using the solution. Again what is the problem you are trying to solve.
Jim Received on Fri Apr 28 2006 - 09:30:07 CDT