An example of Database Operation Optimization under asp.net

The following is the initial implementation code. LargerResultProcessor is a base class that traverses the database table pointed to by the generic parameter T and pages the table with 100 entries per page, and call the ProcessItem function for each item, and the subclass only needs to implement the ProcessItem function: Copy codeThe Code is 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 = '{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 above code has a simple logic. For each Item, use the Select statement to retrieve the Shop_Id and execute Update, but the execution speed is slow. For about 60 thousand items, for tables with around 40 thousand Sku and around 0.99 million Order, it takes about 40 minutes to complete the conversion.
These codes are a one-time operation, but for the running system, the shorter the downtime, the better, so some optimization work is carried out. If the database uses parameters for a large number of repeated statements, because it can avoid repeated parsing of statements, the speed will be faster. According to this idea, the simple modification is as follows:Copy codeThe Code is 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 this program. The conversion can be completed in about 25 minutes. There are some improvements. However, the amount of data we really want to modify is not large. There are only 60 thousand plus 40 thousand and about 0.1 million pieces of data in total, so 25 minutes is still a little longer. After a simple analysis, Orders is the largest table. If the overall speed is slow, the possible cause of the greatest slow speed is to query Orders. So I just want to change my mind, find out the ing between Item_Id and Shop_Id in advance and put it in the memory, so as to avoid the query of the Orders table for each ProcessItem. As for the data in the memory, we were going to use a Dictionary. Later we thought that the IDs are all long-type data and cannot be regarded as a "sparse" matrix. It can be called a "dense" matrix, directly Using arrays should be faster, so the maximum Id of Items is first queried, used to set the array size, and then assigned a value based on the index:Copy codeThe Code is as follows: 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 ());
}
}
}

Test the program and run it 70 seconds to complete data conversion. In addition, query the corresponding SQL statement because it is for the database that has just been restored, therefore, it takes about 3 to 40 seconds to execute the SQL statement in the running database using the query manager. Therefore, it is estimated that the conversion can be completed in 3 or 40 seconds during actual conversion.