How to increase the speed for writing clob data to a file

Question

We are extracting data from an oracle database to a file using C# oracleDataReader, we are using multiple threads and I found that reading data other than clob is very fast say say 4 min for 1 GB. But when data contains clob it is very very slow, even a 12 MB clob data is taking some 3 hours.

I tried increasing the buffer from 64 KB to 5MB.

Please suggest how to increase the speed to write clob to a file.

Source code

we are using 2 files 1 for actual csv and 1 for clob. we write recordpointer in place of clob in csv file and in clob file we write recordpointer, clob value.

else if (objVal is OracleClob)
{
    OracleClob oraVal = (OracleClob)objVal;
    if (oraVal.IsEmpty) 
        sw.Write("");
    else
    {
        //    using (StreamWriter writer = new StreamWriter(fileName, true))
        {
            Interlocked.Increment(ref recordPointer);
            if (recordPointer == 1)
            {
                string fileName = outputFileName.Remove(outputFileName.LastIndexOf("."));
                fileName = fileName + ".clobcsv";
                clobWriter = new StreamWriter(fileName, true);
                log.Info("CLOB data is found in this file " + outputFileName + " and clob data is stored in " + fileName + " file");
                clobWriter.WriteLine("Id," + cols[i]);
            }
            StringBuilder sb = new StringBuilder("\"");
            StringBuilder value = new StringBuilder();
            value.Append(oraVal.Value);
            //CsvEscape(value.ToString());
            value.Replace("\"", "\"\"");

            sb.Append(recordPointer);
            sb.Append("\"");
            sb.Append(delimiter);
            sb.Append("\"");
            //sb.Append(oraVal.Value.Replace("\"", "\"\""));
            sb.Append(value);
            value.Clear();
            sb.Append("\"");

            clobWriter.WriteLine(sb);
            //clobWriter.WriteLine("\"" + recordPointer + "\"" + delimiter + "\"" + oraVal.Value.Replace("\"", "\"\"") + "\"");                                    
        }
        sw.Write(recordPointer);
    }
}

You say you're using multiple threads - have you checked to see if you're locking records while you read them? If you are waiting around for locked records, that would drastically decrease performance. — RQDQ, Mar 18 '11 at 15:00
@RQDQ yes couple of places, but other data is loading very fast except clob. — Naveen Chakravarthy, Mar 18 '11 at 15:06
@bunny - What does the code look like for reading / writing the CLOB? — RQDQ, Mar 18 '11 at 15:14
What version of Oracle rdbms is in use ? If 11g, are you using the old lob implementation or the secure files implementation? How does the code for reading the clob look like? The 'speed' you mention is ridiculous. There should be an error involved. — , Mar 18 '11 at 15:16
@ik_zelf We are using oracle 9i with oracle 10g driver for ODP.Net. Regarding speed I mean it takes to 4 minutes to write 1GB of data. When that data contains clob then clob file with 12 MB and csv file of around 170 MB takes about 2 - 3 hours. we are using 2 files for clob actual csv and clob file. — Naveen Chakravarthy, Mar 18 '11 at 15:35
@bunny, still it is amazing slow. Are you aware where the time is spent? It is the reading of the lob from the database or the writing to the file? Something like 10MB/s for me looks like a minimal speed that could easily be reached on kitchen hardware. Maybe you can test this separately (read lob, no write, what happens with the elapsed time?) — , Mar 18 '11 at 16:12
@ik_zelf my senior developer did a small test yesterday by commenting the code that writes the clob, based on that I can say reading clob is slow here. I am using OracleDataReader with buffer size 5MB and write each column to a file. I guess there should be a way to increase the read speed on clob. — Naveen Chakravarthy, Mar 18 '11 at 18:32
It looks like you read a byte at a time. I would say reduce the cache size to 32KB and see what happens. — , Mar 24 '11 at 18:35
That is why I suggested to decrease the buffer size, to see if the speed changes at all. — , May 16 '11 at 20:05

score 0 · Answer 1 · answered Mar 18 '11 at 19:58

0

Try setting LOBFetchSize to -1. http://www.stanford.edu/dept/itss/docs/oracle/10g/win.101/b10117/features003.htm

answered Mar 18 '11 at 19:58

Please treat your mods well.

4,321
1
25
35

score 0 · Accepted Answer · edited May 23 '17 at 12:19

0

You may want to consider using an Oracle tool for exporting data. There are a many ways to perform file IO, and some of them would probably be much faster than what you're doing. For example, I just used dbms_xslprocessor.clob2file to write a 12 MB CLOB in 16 seconds on a slow PC. Here's a partial list of options.

edited May 23 '17 at 12:19

Community

1
1

answered Mar 19 '11 at 05:45

Jon Heller

34,999
6
74
132

How to increase the speed for writing clob data to a file

2 Answers2