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 秒就可以完成转换了。