Example of database operation optimization under asp.net

Here's the code that was originally implemented, where LargerResultProcessor is a base class that iterates through the database tables pointed to by the generic parameter T, paging through 100 items per page, and calling the ProcessItem function for each item, while the subclass simply implements the ProcessItem function:

 
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()); 
} 
} 
} 

The logic of the above code is relatively simple. For each item, Select statement is used to fetch Shop_Id and execute Update. There is only one problem, that is, the execution speed is slow.
This code, it was 1 time sex operation, but for the operating system, downtime as short as possible, so 1 some optimization work, the database for a large number of repeated statements, if use parameters, because can avoid the repetition of the for statement analytical work, so can fast 1, according to this train of thought, simple change as follows:

 
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()); 
} 
} 
} 

Test the program and it will take about 25 minutes to complete the conversion. It's a little bit better, but the amount of data that we're actually going to modify is not that big, it's only about 60,000 plus 40,000 about 100,000 pieces of data, so 25 minutes is still a little bit longer. After a simple analysis, Orders is the largest table. If the overall speed is slow, the possible factor leading to the maximum speed is to query Orders. Therefore, a slightly different way of thinking is to find out the corresponding relationship between Item_Id and Shop_Id in advance and put them in memory, so as to avoid every time ProcessItem needs to query Orders table. As for the data in the memory, was prepared with Dictionary, 1 want to later, Id are long type data, and can't calculate "sparse matrix, the basic can be called" dense "matrix, therefore, directly with array should be faster, so the first query Items Id biggest, is used to set the array size, according to the index assignment again can:

 
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()); 
} 
} 
} 

To test this one section of the program, run 70 seconds to complete the data transformation, in addition, the corresponding relations between the query that 1 other SQL, because is for recovering the database, with about 3, 40 seconds, so the actual using query manager, in a running database that 1 other SQL, need only 1 seconds can be completed, so the estimate at the time of actual transformation, 3, 40 seconds can complete the transformation.