A quick and dirty hack to the existing PL/SQL code
which typically adds good performance improvement is
to fetch from your cursor in bulk collect (say 200-500
records at a time).
Otherwise its Perl/C/etc/etc time.
hth
connor
- Tim Gorman <tim_at_sagelogix.com> wrote: > Options
#1 (Perl) and #2 (PRO*C) would be fastest
> and easiest. The PRO*C
> demo programs provide a decent start, for option #2.
> Option #3 (OCI) would
> be not faster than PRO*C and, due to the increased
> complexity of OCI, a more
> problematic approach.
>
> SQL*Plus is the easiest method to implement by far,
> but the SQL*Plus program
> introduces a surprising amount of "overhead"
> processing, making it much
> slower than Perl or PRO*C, but much faster than
> UTL_FILE. If you are not in
> a big time crunch but want decent performance, use
> SQL*Plus. If you are in
> a time crunch and need the best performance, use
> Perl or PRO*C.
>
>
> on 1/10/04 6:09 PM, Guang Mei at gmei_at_incyte.com
> wrote:
>
> > Hi:
> >
> > I have a program (running on oracle 8173 server)
> that writes 48 Millions
> > lines of data into various text files . The
> selected data is from various
> > tables and I have the query pretty much optimized.
> Now I am trying to find
> > the fastest way to dump the selected data into a
> text file on the same
> > oracle server. The program (written as a pl/sql
> package) now works
> > something like this:
> >
> > str varchar2(32767) := '';
> > NL char(1) := chr(10); -- new line
> character
> > begin
> > fpn := utl_file.fopen(directory, filename, 'w',
> 32767);
> > for x in cur1 loop
> > str := str || x.str || NL; -- keep
> building the str
> > if (length (str) > 31000 ) then
> > str := substr (str,1, length(str) -1 );
> > utl_file.put_line(fpn, str);
> > str := '';
> > end if;
> > end loop;
> >
> > -- dump the last part:
> > str := substr (str,1, length(str) -1 );
> > utl_file.put_line(fpn, str);
> > utl_file.fflush(fpn);
> > utl_file.fclose(fpn);
> > end ;
> >
> >
> > The above code works perfect fine now. But I am
> wondering if there is
> > another way that could increase the writing
> siginificantly faster. This
> > porgram does not have to be in pl/sql. I can
> think of a couple of
> > potential approaches:
> >
> > 1. Write a perl program, basically using perl's
> DBI/DBD to select the data
> > from the database, then calling perl's "print" to
> write data into a file.
> > I have not tested this and don't know if it is
> faster that
> > utl_file.put_line.
> >
> > 2. Write a C program, using ProC to talk to DB,
> then use C's fopen and
> > fwrite(?) to dump data into text file.
> >
> > 3. Write a C program,using OCI to talk to DB,
> then use C's fopen and
> > fwrite(?) to dump data into text file.
> >
> > I don't have direct experience with ProC and OCI,
> so I don't know how
> > faster (or any) it would be by doing Option 2 or
> 3 above. Does anyone
> > know if I would see siginificant performance boost
> by using C? Any other
> > suggestions?
> >
> > TIA.
> >
> > Guang
> >
> >
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Tim Gorman
> INET: tim_at_sagelogix.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sun Jan 11 2004 - 07:24:26 CST
