如何提高将 clob 数据写入文件的速度

发布于 2024-10-24 18:35:27 字数 1852 浏览 3 评论 0原文

我们使用 C# oracleDataReader 将数据从 Oracle 数据库提取到文件，我们使用多个线程，我发现读取除 clob 之外的数据非常快，比如 4 分钟读取 1 GB。但是当数据包含 clob 时，速度非常非常慢，即使是 12 MB 的 clob 数据也需要大约 3 个小时。

我尝试将缓冲区从 64 KB 增加到 5 MB。

请建议如何提高将 clob 写入文件的速度。

源代码

我们使用 2 个，其中 1 个用于实际 csv，1 个用于 clob。我们在 csv 文件中编写记录指针来代替 clob，并在 clob 文件中编写记录指针、clob 值。

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);
    }
}

原文

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);
    }
}

分享到QQ

分享到微博