To solve the performance problem of EF hierarchical query, ef hierarchical Query

To solve the performance problem of EF hierarchical query, ef hierarchical Query

In the past two years, I helped my friend develop an Internet cafe management software, which was generated using a dynamic, soft, three-tier architecture SQL statement. Recently, I am working on an e-commerce website with asp.net mvc + ef + autofac because of function changes. Simply re-applied the original underlying architecture to my current architecture EF6.0 + autofac + three-tier architecture, and the upper-layer asp.net did not change. After the change, we found that the shift page was very slow.

Trace the SQL statements generated by EF and find that the generated SQL statements are faulty. The entire table is queried, and the entire table contains nearly 0.1 million pieces of data.

Continue to track the time consumption of the database and find that the time occupied by this statement is indeed

1 public class BaseRepository <T>: IDBbase <T> where T: class 2 {3 // instantiate EF framework 4 protected skdbContext db = new skdbContext (); 5 6 // Add 7 public T AddEntities (T entity) 8 {9 db. entry <T> (entity ). state = EntityState. added; 10 db. saveChanges (); 11 return entity; 12} 13 14 // modify 15 public bool UpdateEntity (T entity) 16 {17 db. set <T> (). attach (entity); 18 db. entry <T> (entity ). state = EntityState. modified; 19 return db. saveChanges ()> 0; 20} 21 22 // modify 23 public bool DeleteEntities (T entity) 24 {25 db. set <T> (). attach (entity); 26 db. entry <T> (entity ). state = EntityState. deleted; 27 return db. saveChanges ()> 0; 28} 29 30 // query 31 public IQueryable <T> LoadEntities (Func <T, bool> wherelamties) 32 {33 return db. set <T> (). where <T> (wherelambda ). asQueryable (); 34} 35 // query a single 36 public T LoadEntitie (Func <T, bool> wherelamtie) 37 {38 return db. set <T> (). firstOrDefault <T> (wherelamties); 39} 40 41 // page 42 public IQueryable <T> LoadPagerEntities <S> (int pageSize, int pageIndex, out int total, 43 Func <T, bool> wherelamloud, bool isAsc, Func <T, S> orderByLambda) 44 {45 var tempData = db. set <T> (). where <T> (whereLambda); 46 47 total = tempData. count (); 48 49 // sort to get the data of the current page 50 if (isAsc) 51 {52 tempData = tempData. orderBy <T, S> (orderByLambda ). 53 Skip <T> (pageSize * (pageIndex-1 )). 54 Take <T> (pageSize ). asQueryable (); 55} 56 else57 {58 tempData = tempData. orderByDescending <T, S> (orderByLambda ). 59 Skip <T> (pageSize * (pageIndex-1 )). 60 Take <T> (pageSize ). asQueryable (); 61} 62 return tempData. asQueryable (); 63} 64}View Code

Call Code

Return jiaobanitem. LoadEntities (t => t. JiaoBanID = jiaobanID & t. GoodsID = GoodsID). FirstOrDefault ();

Refer to nopCommerce to modify baserepository

1 public class EFRepository <T>: IRepository <T> where T: class 2 {3 // instantiate EF framework 4 // protected YaFeiNetContext db = new YaFeiNetContext (); 5 private DbContext _ context; 6 private IDbSet <T> _ entities; 7 8 public EFRepository (DbContext context) 9 {10 this. _ context = context; 11} 12 13 14 // Add 15 public virtual T AddEntities (T entity) 16 {17 try 18 {19 if (entity = null) 20 throw new ArgumentNullException ("entity"); 21 22 this. entities. add (entity); 23 this. _ context. saveChanges (); 24 return entity; 25} 26 catch (DbEntityValidationException dbEx) 27 {28 var msg = string. empty; 29 foreach (var validationErrors in dbEx. entityValidationErrors) 30 foreach (var validationError in validationErrors. validationErrors) 31 msg + = string. format ("Property: {0} Error: {1}", validationError. propertyName, validationError. errorMessage) + Environment. newLine; 32 33 var fail = new Exception (msg, dbEx); 34 throw fail; 35} 36} 37 38 // modify 39 public virtual bool UpdateEntities (T entity) 40 {41 try 42 {43 if (entity = null) 44 throw new ArgumentNullException ("entity"); 45 46 47 // this. entities. attach (entity); 48 // _ context. entry <T> (entity ). state = EntityState. modified; 49 return this. _ context. saveChanges ()> 0; 50} 51 catch (DbEntityValidationException dbEx) 52 {53 var msg = string. empty; 54 foreach (var validationErrors in dbEx. entityValidationErrors) 55 foreach (var validationError in validationErrors. validationErrors) 56 msg + = string. format ("Property: {0} Error: {1}", validationError. propertyName, validationError. errorMessage) + Environment. newLine; 57 58 var fail = new Exception (msg, dbEx); 59 throw fail; 60} 61 62} 63 64 // modify 65 public virtual bool DeleteEntities (T entity) 66 {67 try 68 {69 if (entity = null) 70 throw new ArgumentNullException ("entity"); 71 72 // db2.Set <T> (). attach (entity); 73 // db2.Entry <T> (entity ). state = EntityState. deleted; 74 this. entities. remove (entity); 75 return this. _ context. saveChanges ()> 0; 76} 77 catch (DbEntityValidationException dbEx) 78 {79 var msg = string. empty; 80 foreach (var validationErrors in dbEx. entityValidationErrors) 81 foreach (var validationError in validationErrors. validationErrors) 82 msg + = string. format ("Property: {0} Error: {1}", validationError. propertyName, validationError. errorMessage) + Environment. newLine; 83 84 var fail = new Exception (msg, dbEx); 85 throw fail; 86} 87 88 89} 90 91 // query 92 public virtual IQueryable <T> LoadEntities (Func <T, bool> wherelamties) 93 {94 return this. entities. where <T> (wherelambda ). asQueryable (); 95} 96 // query a single 97 public virtual T LoadEntitie (Func <T, bool> wherelamtie) 98 {99 return this. table. where (wherelambda ). firstOrDefault (); 100} 101 // <summary> 102 // search for 103 // based on the primary key </summary> 104 // <param name = "id"> </param> 105 /// <returns> </returns> 106 public virtual T GetById (object id) 107 {108 return this. entities. find (id); 109} 110 111 112 113 // Page 114 public virtual IQueryable <T> LoadPagerEntities <S> (int pageSize, int pageIndex, out int total, 116 Func <T, bool> wherelamloud, bool isAsc, Func <T, S> orderByLambda) 117 {118 119 var tempData = this. entities. where <T> (whereLambda); 120 121 total = tempData. count (); 122 123 // sort the data on the current page to obtain 124 if (isAsc) 125 {126 tempData = tempData. orderBy <T, S> (orderByLambda ). 127 Skip <T> (pageSize * (pageIndex-1 )). 128 Take <T> (pageSize ). asQueryable (); 129} 130 else131 {132 tempData = tempData. orderByDescending <T, S> (orderByLambda ). 133 Skip <T> (pageSize * (pageIndex-1 )). 134 Take <T> (pageSize ). asQueryable (); 135} 136 return tempData. asQueryable (); 137 138 139 140} 141 142 protected virtual IDbSet <T> Entities143 {144 get145 {146 if (_ entities = null) 147 _ entities = _ context. set <T> (); 148 return _ entities; 149} 150} 151 152 153 public virtual IQueryable <T> Table154 {155 get {return this. entities;} 156} 157 158 159}View Code

Modify the call code

Return jiaobanitem. Table. Where (t => t. JiaoBanID = jiaobanID & t. GoodsID = GoodsID). FirstOrDefault ();

The problem solved page responds less than 100 ms and the SQL statements generated during debugging already have query conditions.

The problem lies in

// Query
Public IQueryable <T> LoadEntities (Func <T, bool> wherelamties)
{
Return db. Set <T> (). Where <T> (wherelambda). AsQueryable ();
}

For verification, I directly call

Return this. context. set <tb_e_jiaoBanItem> (). where (t => t. jiaoBanID = jiaobanID & t. goodsID = GoodsID ). asQueryable (). firstOrDefault ();

The page response is about ms, and the performance is okay. It is okay to directly call the set <> method of dbcontext. However, after passing across several layers, there is a problem. The query statement that I want is not generated.

 

Toluene layering

In addition to the extraction operation, there is a very important factor in many chemical research work, which is often ignored by most people, that is, the pH value (a pH index ). Extraction is a non-homogeneous physical chemical process, but it is considered as a physical process. In fact, there are chemical processes in it, and they play a decisive role! You can give it a try.
Test Strips for pH measurement are available in chemical shops.
Several kinds of alkali solutions with different pH values should be selected for the test, and the test and comparison should be conducted at the same time to obtain the desired effect.
If the results are not satisfactory, the method for further improvement is to add "emulsion breaking" and adjust the surface performance of the extraction agent.
Good luck!

Supplementary answer:
This is the phenomenon of alcoholization. It is very important to adjust the pH value and improve the effect !!

1 problem

Front and back of the moon: Front ::
Back: hiphotos.baidu.com/..60.jpgmaterial Introduction: The moon is also called Moon. The moon is about 4.6 billion years old and closely related to the earth. The moon also has a layered structure, such as shell, yundun, and core. The average thickness of the outermost shell is about 60-65 km. The depth below the monthly shell reaches 1000 km, which accounts for most of the volume of the moon. The moon is a monthly core. The temperature of the monthly core is about 1000 degrees, which may be melting. The moon is about 3476 kilometers in diameter and 3/11 of the Earth. The volume is only 1/49 of the earth, with a mass of about 735 billion million tons, equivalent to 1/81 of the Earth's mass, and the monthly gravity is about 1/6 of the Earth's gravity. The moon has dark and bright areas. When early astronomers observed the moon, they thought that the dark areas were covered with sea water, so they were called the "Sea ". The famous ones include the cloud sea, the wet sea, and the static sea. The bright part is the mountains, where the mountains are stacked, and the mountains are everywhere. The baye ring-shaped Mountain located near the South Pole is 295 km in diameter. The whole Hainan island can be packed in. The deepest Mountain is Newton's ring-shaped mountain, which is 8788 deep. In addition to the ring-shaped mountains, the moon also has ordinary mountains. Mountain and deep valley are stacked, and you can see some special scenery. The front of the moon always faces the earth. On the other hand, except that the area near the edge of the moon is visible between scales, most of the back of the moon cannot be seen from the earth. In the age of no detector, the back of the Moon has always been an unknown world. A major feature of the back of the moon is that it has almost no dark moon features such as the moon sea. When the detector runs to the back of the moon ball, it will not be able to communicate directly with the Earth. Track data average track radius 384,400 km orbital eccentric heart rate 0.0549 Close Location Distance 363,300 km distance 405,500 km distance average public transit cycle 27 days 7 hours 43 minutes 11.559 seconds average public transit speed 1.023 km/second orbital inclination angle between 28.58 ° and 18.28 ° (The intersection angle with the yellow track is 5.145 °) ascending point of chijing 125.08 ° near point of divergence 318.15 ° physical characteristics equator diameter 3,476.2 km polar diameter 3,472.0 km flat rate 0.0012 surface area 3.976 × 107 square kilometer flat rate 0.0012 volume 2.199 × 1010 cubic kilometer quality 7.349 × 1022 kg average density water 3.350 times equator gravity acceleration 1.62 m/s2 Earth 1/6 escape speed 2.38 km/s rotation cycle 27 days 7 hours 43 minutes 11.559 seconds (synchronous rotation) the rotation speed is 16.655 meters/second (at the equator). The angle of the Self-rotating shaft is at 3.60 ° and Variation between 6.69 ° (the intersection with the yellow road is 1.5424 °) the contrast rate is 0.12 when the full moon, such as-12.74 surface temperature (t)-233 ~ 123 ℃ (average-23 ℃) Atmospheric Pressure 1.3 × 10-10 kPa the moon runs around the Earth for about a week in a lunar month, and moves half an hour relative to the background starry sky, that is, the diameter of the month is similar. Unlike other satellites, the orbital plane of the moon is close to the yellow track plane rather than near the earth's track surface. Compared with the background stars, the time required by the moon to run around the Earth (the moon revolution) is called a stellar month, and the time required by the new moon and the next new moon (or between two identical months) is called a lunar month. The reason why the period of the moon is longer than that of the period of time is... the remaining full text>