I'm experiencing the same problem, reading and writing of CLOB values is 3 to 4 times slower than the same operation using VARCHAR values (in our 1-GBit-LAN).
In a 34-MBit-WAN of one of our customers it's 400 to 600 times slower.
ODP.NET is much faster, especially when InitialLOBFetchSize is set to -1 for the SELECT command.
Here's the sample code:
Code: Select all
using (var c = new Devart.Data.Oracle.OracleConnection("Data Source=***;User Id=***;Password=***"))
{
c.Open();
try
{
var cmddrop = c.CreateCommand();
cmddrop.CommandText = "DROP TABLE TEMP_PERFTEST";
cmddrop.ExecuteNonQuery();
}
catch
{
}
var cmd = c.CreateCommand();
cmd.CommandText = "CREATE TABLE TEMP_PERFTEST(data clob)"; //use varchar2(2000) instead
cmd.ExecuteNonQuery();
cmd = c.CreateCommand();
cmd.CommandText = "INSERT INTO TEMP_PERFTEST(data) VALUES(:data)";
var p = cmd.CreateParameter();
p.ParameterName = "data";
p.OracleDbType = Devart.Data.Oracle.OracleDbType.Clob; //use VarChar instead
cmd.Parameters.Add(p);
var sw = System.Diagnostics.Stopwatch.StartNew();
for(int i = 0; i < 10000; i++)
{
p.Value = new string('x', 1000);
cmd.ExecuteNonQuery();
}
Console.WriteLine(sw.Elapsed);
cmd = c.CreateCommand();
cmd.CommandText = "SELECT data FROM TEMP_PERFTEST";
var r = cmd.ExecuteReader();
while (r.Read())
{
string test = r.GetValue(0) as string;
}
Console.WriteLine(sw.Elapsed);
}
