asp.net下資料庫操作最佳化一例

下面是最初實現的代碼，其中 LargerResultProcessor 是一個基類，負責遍曆泛型參數 T 所指向的資料庫表，並以每頁 100 項的方式分頁，並對每一項調用 ProcessItem 函數，而子類只需實現 ProcessItem 函數即可： 複製代碼 代碼如下:public class ItemRenameCompanyId : LargerResultProcessor<Item>
{
protected override void ProcessItem(Item item)
{
const string template1 = @"select top 1 shop_id from orders where Item_id = '{0}'";
var sql1 = string.Format(template1, item.Id);
const string template2 = @"update Items set shop_id={0} where id = {1};
update skus set shop_id={0} where item_id = {1};";
try
{
var obj = DbEntry.Context.ExecuteScalar(sql1);
var sql2 = string.Format(template2, long.Parse(obj.ToString()), item.Id);
DbEntry.Context.ExecuteNonQuery(sql2);
}
catch (Exception exception)
{
Logger.Default.Warn(exception + item.Id.ToString());
}
}
}

上面這段代碼，邏輯比較簡單，針對每一項，使用 Select 語句取出 Shop_Id，並且執行 Update，只是有個問題，就是執行速度比較慢，對於我們 6 萬左右 Item，4 萬左右 Sku，99 萬左右 Order 的表，需要執行約 40 分鐘，才能轉換完畢。
這些代碼，雖然是一次性操作，但是對於運行系統，停機時間越短越好，於是進行一些最佳化工作，資料庫對於大量重複的語句，如果使用參數的方式，因為可以避免對於語句的重複解析工作，所以速度會快一些，按照這個思路，簡單的修改如下： 複製代碼 代碼如下:public class ItemRenameCompanyId : LargerResultProcessor<Item>
{
protected override void ProcessItem(Item item)
{
const string template1 = @"select top 1 shop_id from orders where Item_id = @id";
const string template2 =
@"update Items set shop_id=@sid where id = @id;
update skus set shop_id=@sid where item_id = @id;";
try
{
var sql1 = new SqlStatement(template1, new DataParameter("@id", item.Id));
var sid = Convert.ToInt64(DbEntry.Context.ExecuteScalar(sql1));
var sql2 = new SqlStatement(template2, new DataParameter("@sid", sid), new DataParameter("@id", item.Id));
DbEntry.Context.ExecuteNonQuery(sql2);
}
catch (Exception exception)
{
Logger.Default.Warn(exception + item.Id.ToString());
}
}
}

測試這個程式，大概 25 分鐘可以完成轉換。有一些提高，不過，我們真正要修改的資料量並不大，一共只有 6 萬 加 4 萬 大約 10 萬條資料，所以 25 分鐘還是有些長了。簡單分析後，Orders 是最大的表，如果整體速度慢，則導致速度慢最大的可能因素，應該是查詢 Orders，所以稍換一個思路，提前把 Item_Id 和 Shop_Id 的對應關係尋找出來，放到記憶體裡，從而避免每次 ProcessItem 都要進行 Orders 表的查詢。至於記憶體裡的資料，本來準備用 Dictionary 的，後來一想，Id 都是 long 型的資料，而且不能算“稀疏”矩陣，基本可以稱為“稠密”矩陣，所以，直接用數組應該是速度更快，所以先查詢出 Items 的最大 Id，用於設定數組大小，再按索引賦值即可： 複製代碼 代碼如下:public class ItemRenameCompanyId : LargerResultProcessor<Item>
{
private readonly long[] _dic;
public ItemRenameCompanyId()
{
var count = Convert.ToInt64(DbEntry.Context.ExecuteScalar("select top 1 Id from items order by id desc")) + 10;
_dic = new long[count];
var sql =
new SqlStatement(
"select items.id as xiid,orders.shop_id as xsid from items inner join orders on orders.item_id = items.id group by items.id,orders.shop_id")
{SqlTimeOut = 300};
dynamic list = DbEntry.Context.ExecuteDynamicList(sql);
foreach(dynamic row in list)
{
_dic[row.xiid] = row.xsid;
}
}
protected override void ProcessItem(Item item)
{
const string template2 =
@"update Items set shop_id=@sid where id = @id;
update skus set shop_id=@sid where item_id = @id;";
try
{
var sid = _dic[item.Id];
var sql2 = new SqlStatement(template2, new DataParameter("@sid", sid), new DataParameter("@id", item.Id));
DbEntry.Context.ExecuteNonQuery(sql2);
}
catch (Exception exception)
{
Logger.Default.Warn(exception + item.Id.ToString());
}
}
}

再測試這一段程式，運行 70 秒就完成了資料轉換，另外，查詢對應關係那一句 SQL，因為針對的是剛恢複的資料庫，所以用了大概 3、40 秒，實際使用查詢管理器，在運行中的資料庫執行那一句 SQL，只需要 1 秒左右就可以完成，所以，估計在實際轉換的時候，3、40 秒就可以完成轉換了。