Outputting flat files...

[mike777]

Hello.
I'm outputting a flat file using the utl_file package.
I have something like:

create or replace procedure prc_OutputSomeThing as
     cursor cur_somecur is
     select col1,col2,col3,col4
     from   sometable;
     vcOutput  varchar2(500);
     rwSomeCur cur_somecur%rowtype;
     file_handle utl_file.file_type;
     begin
          open cur_somecur;
          file_handle:=utl_file.fopen('d:\path','file.txt','w')
          loop
               fetch cur_somecur into rwSomeCur;
               exit when cur_somecur%notfound;
               vcOutput:=rpad(rwSomeCur.col1,20);
               vcOutput:=vcOutput||rpad(rwSomeCur.col2,20);
               vcOutput:=vcOutput||rpad(rwSomeCur.col3,20);
               vcOutput:=vcOutput||rpad(rwSomeCur.col4,20);
               utl_file.put_line(file_handle,vcOutput);
               utl_file.fflush(file_handle);
          end loop;
          close cur_somecur;
          utl_file.fclose(file_handle);
     end prc_OutputSomething;

This works. The problem, though, is that I only get about 1200 rows per second as an output rate. Takes about 3 1/2 hrs to output 12M records.
When I call some SQL from MS FoxPro and output to a flat file from FoxPro, it writes at a rate of about...anyway, I can write the same amount of data to disk in 37 min instead of 240 min (3 1/2 hr.).
In PL/SQL, of course, a varchar variable can be up to 32k long, so if my output record has a width of 150, I can output about 200 records per write instead of 1 per write. Would this improve my performance substantially?
Do you have any other suggestions?
Thank you.
-Mike Kemp

 

[carp]

If you're not trying to read from the file while it is being written to, you might try eliminating the call to utl_file.fflush that's inside your loop.

Elbert, CO
0840 MDT

 

[SantaMufasa]

Mike,

As a performance comparison, I suggest you try INSERTing into a text table instead of outputting to utl_file. Then, when you are done INSERTing, you can do a SELECT * from the table, spooling to a flat file if you choose.

If you take this option, please advise us the results.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:19 (14May04) UTC (aka "GMT" and "Zulu"), 09:19 (14May04) Mountain Time)

 

[sem]

Is your transformation so complex that you can not use sql*plus? I'm sure that the speed may be much higher.

Regards, Dima

http://seminihin.narod.ru

 

[mike777]

carp,
I tried that and it worked great.
Took 28 min. 7k records/second.
My boss and I just thought of spool.
Talk about overlooking the obvious. DOH!!
Anyway...

SantaMufasa, I don't know how to do a text table. I think they only started that in 9i, no? We have 8i. Please advise if it is available in 8i.

sem, you are correct. should have just spooled it. did a lot of work for nothing.

many thanks.

 

[SantaMufasa]

Mike,

Sorry I didn't clarify in my earlier post. By "text table" I mean a standard Oracle table with the following configuration:

SQL> create table txt (lineno number, txt varchar2(4000));

Table created.

...and just insert a sequence number into lineno and your output image into txt.txt. This would be beneficial if you need multiple copies of the output for access over a period of time. If your need is one-time, then I believe that a standard "spool" session (as Dima mentions) should be fine. Throughput would also improve in a "spool" scenario if you preface your commands with "set termout off", which avoids slowdowns due to actual writing to the screen.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:08 (14May04) UTC (aka "GMT" and "Zulu"), 10:08 (14May04) Mountain Time)

 

[mike777]

Mufasa,
Yep, I'll have to try that.
Your idea about a text table is good, too.
Many thanks.
-Mike