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

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

---

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