|
|
两种数据库二进制字段存取控制方式的对比 |
|
|
作者:未知 来源:月光软件站 加入时间:2005-5-13 月光软件站 |
方式一:对于小容量的数据,进行一次载入内存,一次性获取 /// <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"));
两种方式的比较:
第一种方式:减少数据库压力,数据大小已知 第二种方式:增加数据库压力,数据大小未知
总结: 根据实际应用情况进行选择,在二进制字段内容大小已知,数据库负担压力比较大的情况下选择第一种方式;在二进制字段内容大小未知,数据库负担压力较小的情况下选择第二种方式。

|
|
相关文章:相关软件: |
|