Re: Weired Problem

Jim Kennedy wrote:
> "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

Hello Jim,

Agreed !! Oracle!= SQL Server
The problem I faced is the speed with or without cursor remains same. As per my knowledge , with the insert statement Oracle/SQL Server will have to update indexes , Rollback Segments , Redo Log etc. With Cursor the mangement of objects happen every time ,whereas in case of insert ... select statement it would be done once. Furthermore there may be problem of locking because of cursor.

With Warm regards
Jatinder Singh Received on Mon May 08 2006 - 00:22:15 CDT