ASP.NET 資料庫操作優化實戰：從 40 分鐘到 70 秒的三階段演進

原始場景

6 萬 Items、4 萬 Skus、99 萬 Orders。需將每筆 Item 的 ShopId 依據 Orders 表更新。原始碼逐筆查詢 Orders 再更新，耗時 40 分鐘。

第一階段：參數化 SQL（25 分鐘）

const string qry = "SELECT TOP 1 shop_id FROM orders WHERE item_id = @id";
const string upd = @"UPDATE items SET shop_id=@sid WHERE id=@id;
                     UPDATE skus SET shop_id=@sid WHERE item_id=@id;";

var sid = (long)DbEntry.Context.ExecuteScalar(new SqlStatement(qry, new DataParameter("@id", item.Id)));
DbEntry.Context.ExecuteNonQuery(new SqlStatement(upd, new DataParameter("@sid", sid), new DataParameter("@id", item.Id)));

效果：執行計畫重用，減少解析開銷，但仍每筆 Item 查詢一次 Orders（99 萬筆大表掃描）。

第二階段：預載入對應關係至記憶體陣列（70 秒）

public class ItemRenameCompanyId : LargerResultProcessor
{
    private readonly long[] _shopIdMap; // 索引 = ItemId

    public ItemRenameCompanyId()
    {
        // 1. 取得最大 ItemId 決定陣列大小
        long maxId = (long)DbEntry.Context.ExecuteScalar("SELECT TOP 1 Id FROM items ORDER BY Id DESC") + 10;
        _shopIdMap = new long[maxId];

        // 2. 單次 SQL 取得所有 ItemId → ShopId 對應
        var sql = new SqlStatement(@"
            SELECT i.id AS itemId, o.shop_id AS shopId
            FROM items i INNER JOIN orders o ON o.item_id = i.id
            GROUP BY i.id, o.shop_id") { SqlTimeOut = 300 };

        foreach (dynamic row in DbEntry.Context.ExecuteDynamicList(sql))
            _shopIdMap[row.itemId] = row.shopId;
    }

    protected override void ProcessItem(Item item)
    {
        long sid = _shopIdMap[item.Id]; // O(1) 記憶體查找
        var upd = new SqlStatement(@"
            UPDATE items SET shop_id=@sid WHERE id=@id;
            UPDATE skus SET shop_id=@sid WHERE item_id=@id;",
            new DataParameter("@sid", sid), new DataParameter("@id", item.Id));
        DbEntry.Context.ExecuteNonQuery(upd);
    }
}

效能對比

核心原則

1. 識別熱點：Profiling 發現 Orders 表重複掃描為瓶頸。
2. 以空間換時間：6 萬筆 long[] 僅約 480 KB，極小記憶體成本換取數量級速度提升。
3. 批次處理：單次 JOIN 取得完整對應，避免 N+1 查詢問題。
4. 資料結構選擇：Id 連續且範圍已知 → 陣列最快；稀疏 Id → Dictionary<long,long>。

生產環境補充

• 正式轉換前備份資料庫，驗證腳本正確性。
• 大量更新建議分批次提交事務，避免鎖升級與記錄檔暴漲。
• 停機時間窗口內完成，或採用雙寫/同步策略實現零停機遷移。