load from cursor

Raj

Hi,

We have a process ( a stored proc) that exports data ( about 10
million records) from a huge table,t1 and loads it into another huge
table t2. The export runs for 3 hours everyday because of a very
complex sql...

My question is can we load from the table directly instead of exporting
the data and loading it from the falt file??

like using load from cursor??

does it have any disadvantages?? when we declare a cursor where is the
result stored?? can it overflow to the disk?? people here had lots of
performance issues using cursors in a Stored proceedure...

Thanks,
Raj

Mar 13 '06 #1

Brian Tkatch

I do not know the answer to your question. However, a side note:

The export runs for 3 hours everyday because of a very complex sql...

I do not know if this applies, but using a temp TABLE, a splitting up
the actions of the complex SQL to multiple simple SQL statements may
speed things up a bit.

I just sped up a query that took aboutone minute and forty seconds to
under a second by using a temp table instead.

B.

Mar 13 '06 #2

Shashi Mannepalli

I don't see any disadvantages with using CURSOR except make sure only
after the LOAD only you issue a commit.
Regarding memory issues check other posts..

http://groups.google.com/group/comp....0905d2ad389953

cheers...
Shashi Mannepalli

Mar 13 '06 #3