Oracle clob列的大小超过32M时使用分割发送,小于32M时正常发送
Oracle clob列性能测试的测试用例:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace db
{
class Program
{
// private OracleConnection mycon = new OracleConnection("Data Source=DB1;User Id = system;Password= asdf1234;");
private OracleConnection mycon = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.167.133.89)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db1)));User Id = system;Password= asdf1234;");
private OracleCommand mycom = null;
private void open()
{
mycon.Open();
}
private void close()
{
mycon.Close();
}
private void delete()
{
mycom = new OracleCommand("delete from system.tb1", mycon);
mycom.ExecuteNonQuery();
}
private void insert(int num, Int32 length)
{
String insertStr = "insert into system.tb1(CINT,CCLOB) values(:p1,:p2)";
mycom = new OracleCommand(insertStr, mycon);
string s = "a";
DateTime dt1, dt2;
OracleClob clob = null;
OracleDataReader dr;
if (length>=16)
{
if (length == 128)
{
for (int i = 0; i < 27; i++)
{
s += s;
}
}
else if (length == 32)
{
for (int i = 0; i < 25; i++)
{
s += s;
}
}
else
{
s = "";
string tmp = "a";
for (int i = 0; i < 20; i++)
{
tmp += tmp;
}
for (int j = 0; j < length; j++)
{
s += tmp;
}
}
dt1 = DateTime.Now;
char[] charArray = null;
for (int i = 0; i < num; i++)
{
mycom.CommandText = "insert into system.tb1(CINT,CCLOB) values(" + i + ",'a')";
mycom.ExecuteNonQuery();
mycom.CommandText = "select CINT,CCLOB from system.tb1 where CINT=" + i;
dr = mycom.ExecuteReader();
dr.Read();
clob = dr.GetOracleClobForUpdate(1);
clob.Erase();
charArray = s.ToCharArray();
for (int j = charArray.Length; j >0; j -= 1024 * 1024)
{
clob.Write(charArray, 0, 1024 * 1024);
}
}
dt2 = DateTime.Now;
mycom = new OracleCommand("select * from system.tb1", mycon);
dr = mycom.ExecuteReader();
dr.Read();
Console.WriteLine(dr.GetOracleClob(1).Length / 1024 / 1024 / 2 + "M");
}
else
{
s = "";
string tmp = "a";
for (int i = 0; i < 20; i++)
{
tmp += tmp;
}
for (int j = 0; j < length; j++)
{
s += tmp;
}
dt1 = DateTime.Now;
for (int i = 0; i < num; i++)
{
mycom.Parameters.Clear();
/*
para1 = new OracleParameter("p1", OracleDbType.Int32);
para2 = new OracleParameter("p2", OracleDbType.Clob);
para1.Value = i;
para2.Value = s;
mycom.Parameters.Add(para1);
mycom.Parameters.Add(para2);
*/
mycom.Parameters.Add("p1", OracleDbType.Int32).Value=i;
mycom.Parameters.Add("p2", OracleDbType.Clob).Value=s;
mycom.ExecuteNonQuery();
}
dt2 = DateTime.Now;
mycom = new OracleCommand("select * from system.tb1", mycon);
dr = mycom.ExecuteReader();
dr.Read();
Console.WriteLine(dr.GetString(1).Length / 1024 / 1024 + "M");
}
TimeSpan ts = dt2 - dt1;
Console.Out.WriteLine("insert time: " + ts.TotalSeconds);
}
private void select(int num)
{
OracleDataReader dr = null;
string selStr = "";
mycom = new OracleCommand(selStr, mycon);
DateTime dt1 = DateTime.Now;
String s;
for (int i = 0; i < num; i++)
{
mycom.CommandText = "select * from system.tb1 where CINT=" + i;
dr = mycom.ExecuteReader();
while(dr.Read())
s=dr.GetString(1);
//Console.Out.WriteLine(s.ToString().Length);
dr.Close();
}
DateTime dt2 = DateTime.Now;
TimeSpan ts = dt2 - dt1;
Console.Out.WriteLine("select time: " + ts.TotalSeconds);
}
static void Main(string[] args)
{
try
{
Program p = new Program();
Console.Out.WriteLine("please input 3 parameters:");
Console.Out.WriteLine("1.sql type (insert/select)");
string sqlType;
sqlType = Console.In.ReadLine();
while (!sqlType.Equals("insert") && !sqlType.Equals("select"))
{
Console.Out.WriteLine("sql type is not insert or select,please input insert or select");
sqlType = Console.In.ReadLine();
}
Console.Out.WriteLine("2.loop time");
string loopNumStr = Console.In.ReadLine();
int loopNum = 0;
try
{
loopNum=Convert.ToInt32(loopNumStr);
}
catch (System.Exception e)
{
System.Console.WriteLine(e.Message.ToString());
}
Console.Out.WriteLine("3.data length(MB)");
string dataLengthStr = Console.In.ReadLine();
int dataLength = 0;
try
{
dataLength=Convert.ToInt32(dataLengthStr);
}
catch (System.Exception e)
{
System.Console.WriteLine(e.Message.ToString());
}
p.open();
if (sqlType.Equals("insert"))
{
p.delete();
p.insert(loopNum, dataLength);
}
else if (sqlType.Equals("select"))
{
p.select(loopNum);
}
p.close();
Console.In.Read();
}
catch (System.Exception e)
{
System.Console.WriteLine(e.Message.ToString());
}
}
}
}
分享到:
相关推荐
asp.net操作oracle clob字段demo
JDBC读写Oracle的CLOB字段
java操作oracle clob,基础教程,教你clob在java中的用法,简单易学。
ORACLE中CLOB字段转String类型
ibatis 读取oracle clob类型
Oracle导出Clob,Blob等大字段工具,自己写的工具,方便大家下载使用
java操作oracle clob,基础教程,教你clob在java中的用法,简单易学。
J2EE开发中,经常会遇到存储大文本的文字信息,而oracle数据库的vachar字段最大能存储4000个字节,存储更大的信息必须使用clob或blob字段,本文档就是针对j2ee开发中对colb字段操作的实现。
当ORACLE需要存放一些不定长而长度又长于4000字符的字符串时,可考虑使用ORACLE的CLOB类型进行存储,此时就要将该字符串转化为CLOB类型。
向Oracle数据库插入Clob大段文本解决方法
sql server中的image类型的数据导出到oracle的clob字段中
NULL 博文链接:https://bijian1013.iteye.com/blog/2233121
hibernate存取oracle的clob
简单写的一个小工具,把图片存入oracle中,按clob和blob两种方式存储,并读取图片
Oracle导出Clob,Blob工具 ,支持导出CLob工具版本2,解决上个版本导出时间有问题的bug
大数据量,返回值虽然是CLOB,依然会报错; CLOB变量需要dbms_lob.createtemporary,临时表空间中,建立临时LOB。 大数据量,返回值虽然是CLOB,依然会报错; CLOB变量需要dbms_lob.createtemporary,临时表空间中,...
把oracle数据库中字段类型为clob的字段值以字符串的形式读取出来
运用Java如何存取Oracle中的CLOB类型字段
jsp结合html编辑器FCKeditor对Oracle CLOB大字段操作范例。