数据库操作优化一例

数据库升级,需要对几个表进行一些数据转换,具体是这样:针对每一个 Item,从 orders 表里查出 Shop_Id,并把此 Id 赋值给 items 和 skus 中的 Shop_Id。下面是最初实现的代码,其中 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 秒就可以完成转换了。

posted @ 2010-11-19 15:23 梁利锋 阅读(...) 评论(...) 编辑 收藏