C # Large e-commerce project Optimization (II.)--Abandon EF and discard EF

In the previous blog post, we described the Code Foundation for the development of e-commerce projects using EF, which, after the mention of EF, stirred thousands of waves. Many park friends have said: EF is not suitable for fast-growing Internet projects, EF is only suitable for enterprise-class applications and so on.

Also some experts mentioned the distributed, indeed, performance optimization from the database, the initial index, and then vertical split, horizontal split, read and write separation, even distributed transactions, spring snow, the pattern is very high. However, I hope that through the gradual process to optimize the project, we narrow the pattern, from the details to see the pros and cons of different scenarios.

As mentioned before, the main reason for using EF is that the project time is tight, the EF is set up fast, the familiar colleague is also many, the use is convenient. This decision really helped us to survive the initial difficulties. In the process of increasing the volume of business, some problems are gradually exposed, and we begin to optimize for the problem.

Issue 1: Partial requests respond slowly and affect the user experience.

Using EF to do data additions and deletions, some nonstandard code will also slow down the efficiency of the program, the author has mentioned in the previous article. Some requests may contain multiple data queries and updates, and if these small problems are running inefficiently, the request is really slow. However, optimizing it within the framework of EF may not be effective. To update the product sales for example, we on the program and code:

Scenario 1

First find out a piece of data, and then fill in the new calculated sales, and then update the database, the code is as follows:

  // find the data first, then update            var productsku = UnitOfWork.ProductSku.GetByID (dtos[0]. items[0]. SKUID);             = productsku.salescount + dtos[0]. items[0]. Quantity;            UnitOfWork.ProductSku.Update (Productsku);            Unitofwork.submit ();

Its response time

Scenario 2

Using Iquryable, as mentioned before, this query method will not really load all the data into memory, the code is as follows:

// 2 Update            with IQueryable query var productSKU1 = UnitOfWork.ProductSku.Get (p = p.id = = dtos[0]. items[0]. SKUID);             foreach (var in productSKU1)            {                = Item. Salescount + dtos[0]. items[0]. Quantity;                UnitOfWork.ProductSku.Update (item);            }            Unitofwork.submit ();

Its response time

Scenario 3

Using SQL directly, simple and rude, the code is as follows:

// 3 Using SQL update directly            string @" Update Productsku Set salescount=salescount+ " + dtos[0]. items[0"  where id= '" + dtos[0]. items[0"'";            UnitOfWork.ProductSku.ExecuteSqlCommand (updatesql);            Unitofwork.submit ();

Its response time

Let's analyze these three scenarios:

Solution 1 is easy to understand, the data is isolated, updated and then plugged back to the database, logic clear, the code is clearer. But the data is taken out, loaded into memory, and then the memory of the data to modify, the final generation of SQL back to the database execution, this set to go down, like a large circle, in fact, we just want to update sales.

Scenario 2 looks better, using the idea we talked about in the previous article, not loading the data into memory, only loading it in time, and updating it in this way, actually the SQL generated and the direct execution of SQL are not much worse, but we can see that scenario 2 and scenario 1 are about the same time. In the author's expectation, scenario 2 should be better than scenario 1, as for the time of the same reason, it should be the case 2 used a cycle, the second cycle does not meet the conditions, and then jump out of the loop, resulting in a little time wasted. In the case of volume update sales, Scenario 2 must be much better than scenario 1.

Scenario 3 is naturally simple and rough, a SQL to do, no muddy, its effect is gratifying, almost 1 time times faster than the program 1!

Before a friend mentioned the index problem, in fact, my factory has a full-time DBA, not only for the larger data table added clustered index and nonclustered index, but also wrote a timed task to update the index. So the index of this piece we don't delve into.

Early to chase the development efficiency phase, we might optimize scenario 1 to Scenario 2, and then continue with the new feature development. But now that we have more choices and we have more time to delve into the user experience and system efficiency, then naturally we start to abandon EF. But to this extent, it does not seem appropriate to change the framework. and the Boulevard to Jane, if can be done with SQL, it will be very good. Thus, we began to refactor the key part of the business code, replacing it with the native SQL. This seems to be the beginning of abandoning EF.

Issue 2: Some tables with large data volumes need to be divided, and EF is difficult to maintain

EF is an ORM framework that maps database objects, whereas a table in the same database is split into two or more, and EF doesn't seem to be able to map, and the two table fields are exactly the same, can you write a model? And the table is a dynamic process, perhaps once a year, perhaps one months, and may be scheduled to execute the task, the total can not be a table to change the code. Since then, the contradiction between us and EF has intensified.

There is a blog about database splitting in the garden, we have to change only the data access layer, it is best not to move the business layer. And we mentioned earlier: using Native SQL. Then we'll rewrite the data access with native SQL.

Here for example, for example, we split the order table, split by year, usually the user will only view the current year's orders, so the main table is certainly more than the number of queries, if there are users to check the previous orders, we will expand the scope of the query. With this in mind, we begin to rewrite the data access layer and execute it as follows:

1. Using native SQL

2. Add a date parameter and start a connection query if the date exceeds the range of the primary table

3. You can also add additional criteria to the query, filling the parameters as objects into SQL

4. Abstract a common method of generating SQL to make it easier for everyone to call

Assembling SQL is an extremely test of basic skills, this public method is a master of my factory data experts abstracted out, we can also follow this idea, a simplified version is very easy to do out.

At this point, the important business functions in the project have been disconnected from EF, and we are delighted to reap the efficiency of SQL. In other functional modules, there are no high concurrency scenarios or applications that are not commonly used, and we do not refactor.

Although the title of this article is a dislike, but if it is enterprise-class application, need to take into account development efficiency, and there is no Internet mode of business surges in the situation, I still recommend the use of EF.

C # Large e-commerce project Optimization (II.)--Abandon EF and discard EF