Message-ID: <F001.005DC695.20040111052426@fatcity.com>
Date: Sun, 11 Jan 2004 05:24:26 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: ml-errors@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: =?iso-8859-1?q?Connor=20McDonald?= <hamcdc@yahoo.co.uk>
Subject: Re: What is the fastest way to dump oracle data into a human
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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@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@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@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@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@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@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@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).