两种数据库二进制字段存取控制方式的对比
2024-07-21 02:22:56
供稿：网友
方式一：对于小容量的数据，进行一次载入内存，一次性获取
/// <summary>
/// 小容量附件数据读取性能测试
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public static bool processdatafromdatabasebyadapter(string strsql,out string strerr)
{
long t0 = environment.tickcount;
datatable table ;
if(!oledatabaseproxy.executesql(strsql,out table,out strerr))return false;
long imagedatasizecount = 0;
if(!capabilityproxy.processdatafromdatabase(ref table,out imagedatasizecount,out strerr))return false;
long t1 = environment.tickcount;
logproxy.writelog("数据库性能测试:总耗时 "+ convert.tostring(t1-t0) +" ms,数据量:" + imagedatasizecount.tostring() + " bytes");
strerr = "";
return true;
}
/// <summary>
/// 执行数据查询操作
/// </summary>
/// <param name="strsql"></param>
/// <param name="table"></param>
/// <param name="strerr"></param>
/// <returns></returns>
public static bool executesql(string strsql,out system.data.datatable table,out string strerr)
{
system.data.oledb.oledbconnection cnn = new oledbconnection();
cnn.connectionstring = configproxy.getvaluebykey("oleconnectionstring");
system.data.oledb.oledbdataadapter adapter = new oledbdataadapter(strsql,cnn);
table = new system.data.datatable();
try
{
adapter.fill(table);
}
catch(exception err)
{
strerr = err.message;
return false;
}
strerr = "";
//释放资源
cnn.dispose();
adapter.dispose();
gc.collect();
return true;
}
/// <summary>
/// 对数据库记录进行处理
/// </summary>
/// <param name="table"></param>
/// <param name="imagedatasizecount"></param>
/// <param name="strerr"></param>
/// <returns></returns>
private static bool processdatafromdatabase(ref datatable table,out long imagedatasizecount,out string strerr)
{
imagedatasizecount = 0;
for(int i = 0;i < table.rows.count;i ++)
{
byte [] imagecontent = (byte[])table.rows[i]["附件内容"];
imagedatasizecount += convert.toint64(table.rows[i]["附件容量"]);
capabilityproxy.processimagedata(ref imagecontent);
}
strerr = "";
return true;
}
方式二：在线进行，按指定尺寸分段获取
/// <summary>
/// 大容量附件数据读取性能测试
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public static bool processdatafromdatabasebyreader(string strsql,out string strerr)
{
long t0 = environment.tickcount;
long imagedatasizecount = 0;
system.data.oledb.oledbcommand cmd = new oledbcommand();
oledbconnection cnn = new oledbconnection(configproxy.getvaluebykey("oleconnectionstring"));
cmd.connection = cnn;
cmd.commandtext = strsql;
oledbdatareader reader;
//开启连接
try
{
cnn.open();
}
catch(exception err)
{
strerr = err.message;
return false;
}
byte[] pixels = new byte[numpixels];
long readcount = 0;
reader = cmd.executereader();
//逐条处理
while(reader.read())
{
for(long i = 0; i< convert.toint64(reader.getstring(7)); i = i + numpixels)
{
readcount = reader.getbytes(6,i,pixels,0,numpixels);
if(readcount == 0)
{
break;
}
else if(readcount == numpixels)
{
processimagedata(ref pixels);
}
else
{
byte[]buff = new byte[readcount];
processimagedata(ref buff);
}
imagedatasizecount += readcount;
}
}
reader.close();
//关闭连接
if(cnn.state == system.data.connectionstate.open)
{
cnn.close();
}
long t1 = environment.tickcount;
logproxy.writelog("数据库性能测试:总耗时 "+ convert.tostring(t1-t0) +" ms,数据量:" + imagedatasizecount.tostring() + " bytes");
//释放资源
cnn.dispose();
cmd.dispose();
gc.collect();
strerr = "";
return true;
}
/// <summary>
/// 缓冲区大小
/// </summary>
public static int numpixels = int.parse(configproxy.getvaluebykey("buffersize"));
/// <summary>
/// 处理器延时
/// </summary>
public static int processimagerepeats = int.parse(configproxy.getvaluebykey("cpulatetime"));
两种方式的比较:
第一种方式：减少数据库压力，数据大小已知
第二种方式：增加数据库压力，数据大小未知
总结：
根据实际应用情况进行选择，在二进制字段内容大小已知，数据库负担压力比较大的情况下选择第一种方式；在二进制字段内容大小未知，数据库负担压力较小的情况下选择第二种方式。
