asp.net下数据库操作优化一例

时间:2022-06-01 19:44:51

下面是最初实现的代码,其中 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 秒就可以完成转换了。