An example of database operation optimization under ASP.net

Here is the code that was originally implemented, where Largerresultprocessor is a base class that traverses the database table pointed to by the generic parameter T, and pagination in 100 items per page and calls the Processitem function on each item, and the subclass only implements The Processitem function can:
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, the logic is relatively simple, for each item, use the Select statement to remove shop_id, and execute Update, just have a problem, is the execution speed is slow, for us about 60,000 item,4 around sku,99 about order table, need to execute about 40 Minutes before the transition is complete.
This code, though a one-time operation, but for the running system, the shorter the better, so do some optimization work, the database for a large number of repeated statements, if the use of parameters, because you can avoid the repeated parsing of statements work, so the speed will be a little faster, according to this idea, simple changes 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 for about 25 minutes to complete the conversion. There are some improvements, but the amount of data we really want to modify is not large, with a total of only 60,000 plus 40,000 about 100,000 data, so 25 minutes is still a bit long. After a simple analysis, orders are the largest table, if the overall speed is slow, the most likely cause of slow speed, should be the query Orders, so a little change of ideas, the item_id and shop_id in advance to find out the corresponding relationship, put into memory, so as to avoid each Processitem to the Orders table query. As for the data in memory, originally prepared to use Dictionary, later a thought, IDs are long data, and can not be counted as "sparse" matrix, the basic can be called "dense" matrix, so the direct use of the array should be faster, so first query out the maximum Id of Items, used to set the size of the array , and then assign values by 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 IDs 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.i d,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 this program again, run 70 seconds to complete the data conversion, in addition, the query corresponding to the SQL, because the database is just recovered, so use about 3, 40 seconds, the actual use of the query manager, in the running of the database to execute that SQL, only need 1 seconds or so can be completed, so, It is estimated that the conversion can be done in 3, 40 seconds at the actual conversion time.