Also remember the one-time optimization: Optimization of the Contains method in LINQ to SQL

It's been two months since the last post was updated. Here, the first table of the two months to do something:

The world is so big, I also want to see. In April, I entered an e-commerce company in Shanghai with a job. NET Senior Development engineer. Work for one months, the biggest feeling is a lot faster than the previous small city, work a lot of pressure, the way of development more formal, but a variety of processes are more complex trivial. In writing code, must be rigorous and careful, the validation parameters of the validity of the parameter, the time to throw an exception, the log when writing log, because a careless and reported yellow pages or the main process can not be carried out smoothly, is very face-off things. In addition, I am more concerned about the performance of the Code, the development environment and production environment data volume is not an order of magnitude, perhaps in the development environment page load quickly once the production line is too slow to endure.

All right, talk less. Yesterday, leader scheduled a performance-optimized task. Business to export order information, if the query order time interval is relatively small, no problem, the query of the order time span is large, very very very slow. The ORM used by the project is LINQ to SQL, and as for why it is used, this is a legacy of history.

There is a list of orders, there are dozens of fields, in order to simplify the problem, we built a simple entity:

 Public classorderitem{ Public stringid{Get;Set; }  Public stringorderid{Get;Set; }//Order Number     Public stringproductid{Get;Set; }//Product ID     Public intproductquantity{Get;Set; }//Number of goods}

　　A way to troubleshoot a method, and finally find out where the problem lies:

List<orderitem> Orderitems=orderrepository.find (Orderitem=>orderidlist.contains (orderItem.OrderId)). ToList ();

　　Orderidlist is a collection of list<string> that stores the order number within the query time period. When the number of orders is greater than 50 o'clock, the query starts to slow down, which is much slower when the order number is greater than 100. I filled in the orderidlist with 1000 valid order numbers, then monitored with SQL Server Profiler, and found that the above sentence of LINQ query expression was dynamically compiled into an SQL statement that took a few 10 seconds to generate the SQL probably as follows:

    SELECT id,orderid,productid,productquantity from       orderitems    WHERE OrderId in (...)

　　The above SQL execution time is approximately 0.7 seconds, the order Number OrderID field has been indexed, the data in the test database is about 550,000.

In order to solve the problem that the above LINQ query expression dynamically compiles to SQL, the final decision is to execute the SQL statement directly, and the in query changes to a join query, which is simply rude. Here's how to fix it:

//create temporary table SQL to store the order number in the query time periodstringCreatesql ="CREATE TABLE #TmpOrderId (OrderId varchar);";//order number Insert SQLstringOrderIDs = Orderidlist.aggregate (string. Empty, (current,id) = current + ("('"+ ID +"'),")); OrderIDs= Orderids.remove (Orderids.lastindexof (','));stringInsertsql ="INSERT into #TmpOrderId VALUES"+ OrderIDs +";";//join query SQLstringJoinsql ="SELECT T1. Id,t1. Orderid,t1. Productid,t1. Productquantity from OrderItems as T1 joins #TmpOrderId as T2 on T1. Orderid=t2. OrderId;";//three SQL statements to be executed in a single session, otherwise the temporary table #tmporderid exception will be reportedienumerable<orderitem> query = datacontext.executequery<orderitem> (createsql + insertSql + joinSql);

Finally, the use of this solution, the order details of the basic instant query out, the effect of tension pile Ah, the optimization task completed successfully.

Also remember the one-time optimization: Optimization of the Contains method in LINQ to SQL