兩種數據庫二進制字段存取控制方式的對比
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"));
兩種方式的比較:
第一種方式：減少數據庫壓力，數據大小已知
第二種方式：增加數據庫壓力，數據大小未知
總結：
根據實際應用情況進行選擇，在二進制字段內容大小已知，數據庫負擔壓力比較大的情況下選擇第一種方式；在二進制字段內容大小未知，數據庫負擔壓力較小的情況下選擇第二種方式。
