Posted by: shijesh | November 9, 2014

UTL_FILE – Improving Performance (Part 1)

We most often use UTL_FILE in oracle to unload the data into flat files like CSV files. Although UTL_FILE is very useful, it is relatively slow. However, there are ways by which we can improve the data unload speed using UTL_FILE.

Let see one of the way by which we can improve the performance.

Each write operation performed by UTL_FILE is expensive. So, one way to improve performance would be to reduce the number of write operation.

Eg. Suppose we have to unload 100,00 rows.

1. Slower Approach –> Writing each row 1 by 1. So, to unload 100,000 rows we will have 100,000 write operation.

2. Little better/Faster Approach –> Write a group of row (say 100) at a time. So, in this case number of write operation is reduced and hence performanc is impoved.

Let us see an example:-

Below code is slower approach and it took 2.155 seconds.


DECLARE
   L_File_Handle Utl_File.File_Type;
  
   CURSOR C1
   IS
   SELECT Level , 'A' , 'B' FROM Dual CONNECT BY Level <= 147000;
 
  Type T1_Rec
   IS
   Record
   ( Col1 NUMBER , Col2 VARCHAR2(1) , Col3 VARCHAR2(1));  

  Type T1_Tab
   IS
   TABLE OF T1_Rec;

  L_Rec T1_Tab;
  L_Row    VARCHAR2(100);
  L_Buffer VARCHAR2(32767);
  l_delimiter     CHAR(1) := chr(10);

  BEGIN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE , 'DD-MON-YYYY HH:MI:SS'));
    L_FILE_HANDLE := UTL_FILE.FOPEN('DATA_UNLOAD','DATA.TXT','W');

    OPEN C1;
    FETCH C1 Bulk Collect INTO L_Rec;
    CLOSE C1;

    FOR I IN 1..L_Rec.Count
    LOOP
        L_Row := L_Rec(I).Col1 || ',' || L_Rec(I).Col2 || ',' || L_Rec(I).Col3 || L_DELIMITER ;
        Utl_File.Put_Line( L_FILE_HANDLE , L_Row);
 END LOOP;
 Utl_File.Fclose(L_File_Handle);
 DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE , 'DD-MON-YYYY HH:MI:SS'));
 END;

Below code gives the same result but in 0.956 second.

 DECLARE
  L_File_Handle Utl_File.File_Type;
  CURSOR C1
  IS
    SELECT Level , 'A' , 'B' FROM Dual CONNECT BY Level <= 147000;
  
  Type T1_Rec
  IS
  Record
 ( Col1 NUMBER , Col2 VARCHAR2(1) ,Col3 VARCHAR2(1));

  Type T1_Tab IS TABLE OF T1_Rec;
  L_Rec T1_Tab;
  L_Row       VARCHAR2(100);
  L_Buffer    VARCHAR2(32767);
  l_delimiter CHAR(1) := chr(10);
BEGIN
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE , 'DD-MON-YYYY HH:MI:SS'));
  L_FILE_HANDLE := UTL_FILE.FOPEN('DATA_UNLOAD','DATA.TXT','W');
  OPEN C1;
  FETCH C1 Bulk Collect INTO L_Rec;
  CLOSE C1;
  FOR I IN 1..L_Rec.Count
  LOOP
    L_Row := L_Rec(I).Col1 || ',' || L_Rec(I).Col2 || ',' || L_Rec(I).Col3 || L_DElimiter ;
 
   /*Instead of writing directly to file , data is stored in varible */
  IF LENGTH(L_Buffer) + LENGTH(L_Row) > 32700 THEN
    Utl_File.Put_Line( L_FILE_HANDLE , L_Buffer);
    L_BUFFER := L_ROW;
  ELSE
    L_Buffer := L_Buffer || L_Row;
  END IF;
  END LOOP;

  Utl_File.Put_Line(L_File_Handle ,L_Buffer);
  Utl_File.Fclose(L_File_Handle);
  Dbms_Output.Put_Line(TO_CHAR(Sysdate , 'DD-MON-YYYY HH:MI:SS'));

END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: