自研ORM Include拆分查詢(遞迴演算法 支援無限層級) 效能最佳化探討

zhong發表於2023-01-04
ORM遞迴演算法

最近我在最佳化 Include 拆分查詢，貼出原始碼供大家交流探討是否還有最佳化空間。

測試程式碼

 1                 Console.WriteLine($"總記錄數:{db.Query<Category>().Count()}");
 2 
 3                 var stopwatch1 = new Stopwatch();
 4                 stopwatch1.Start();
 5                 var data1 = db.Query<Category>().Include(i => i.Products).ToList();
 6                 stopwatch1.Stop();
 7 
 8                 Console.WriteLine($"Include查詢 耗時:{stopwatch1.ElapsedMilliseconds} ms {stopwatch1.ElapsedMilliseconds / 1000.00}s");
 9 
10                 //Console.WriteLine(Json.Serialize(data1[0].Products[0]));
11 
12                 var stopwatch2 = new Stopwatch();
13                 stopwatch2.Start();
14                 var data2 = db.Query<Category>().ToList();
15 
16                 foreach (var item in data2)
17                 {
18                     item.Products = db.Query<Product>().Where(w => w.CategoryId == item.CategoryId).ToList();
19                 }
20                 stopwatch2.Stop();
21 
22                 Console.WriteLine($"迴圈查詢 耗時:{stopwatch2.ElapsedMilliseconds} ms {stopwatch2.ElapsedMilliseconds / 1000.00}s");

 

測試結果

 

Include 生成的Sql語句

SELECT `CategoryId`,`CategoryName` FROM `Category`
--------------------------
--------------------------
SELECT a.`CategoryId`,a.`CategoryName`,b.`ProductId`,b.`CategoryId`,b.`ProductCode`,b.`ProductName`,b.`DeleteMark`,b.`CreateTime`,b.`Custom1`,b.`Custom2`,b.`Custom3`,b.`Custom4`,b.`Custom5`,b.`Custom6`,b.`Custom7`,b.`Custom8`,b.`Custom9`,b.`Custom10`,b.`Custom11`,b.`Custom12` FROM `Category` `a`
INNER JOIN `Product` `b` ON `a`.`CategoryId` = `b`.`CategoryId`
ORDER BY `b`.`ProductId`


Include 方法實現




 1         /// <summary>
 2         /// 包括
 3         /// </summary>
 4         /// <typeparam name="TProperty"></typeparam>
 5         /// <param name="expression">表示式</param>
 6         /// <returns></returns>
 7         public IInclude<T, TProperty> Include<TProperty>(Expression<Func<T, TProperty>> expression) where TProperty : class
 8         {
 9             var result = expression.ResolveSql(new ResolveSqlOptions()
10             {
11                 DbType = ado.DbOptions.DbType,
12                 ResolveSqlType = ResolveSqlType.NewColumn,
13                 IgnoreParameter = true,
14                 IgnoreIdentifier = true
15             });
16 
17             var propertyType = typeof(TProperty);
18 
19             if (QueryBuilder.IncludeInfos.Any(a => a.PropertyType.FullName == propertyType.FullName))
20             {
21                 throw new Exception($"屬性名稱:{result.SqlString} 不能重複使用Include方法.");
22             }
23 
24             var type = propertyType;
25 
26             if (type.IsArray)
27             {
28                 type = type.GetElementType();
29             }
30             else if (type.IsGenericType)
31             {
32                 type = type.GenericTypeArguments[0];
33             }
34 
35             var queryBuilder = SqlBuilderFactory.CreateQueryBuilder(ado.DbOptions.DbType);
36             queryBuilder.EntityDbMapping = typeof(T).GetEntityDbMapping();
37             queryBuilder.EntityDbMapping.Alias = "a";
38 
39             var includeInfo = new IncludeInfo();
40             includeInfo.EntityDbMapping = type.GetEntityDbMapping();
41             includeInfo.EntityDbMapping.Alias = "b";
42 
43             includeInfo.PropertyName = result.SqlString;
44             includeInfo.PropertyType = propertyType;
45             includeInfo.Type = type;
46             includeInfo.QueryBuilder = queryBuilder;
47 
48             QueryBuilder.IncludeInfos.Add(includeInfo);
49 
50             return new IncludeProvider<T, TProperty>(ado, QueryBuilder, includeInfo);
51         }





IncludeInfo 實體結構




 1 using Fast.Framework.Abstract;
 2 using Fast.Framework.Interfaces;
 3 using System;
 4 using System.Collections.Generic;
 5 using System.Linq;
 6 using System.Text;
 7 using System.Threading.Tasks;
 8 
 9 namespace Fast.Framework.Models
10 {
11 
12     /// <summary>
13     /// 包括資訊
14     /// </summary>
15     public class IncludeInfo
16     {
17         /// <summary>
18         /// 屬性型別
19         /// </summary>
20         public Type PropertyType { get; set; }
21 
22         /// <summary>
23         /// 型別
24         /// </summary>
25         public Type Type { get; set; }
26 
27         /// <summary>
28         /// 屬性名稱
29         /// </summary>
30         public string PropertyName { get; set; }
31 
32         /// <summary>
33         /// 實體資料庫對映
34         /// </summary>
35         public EntityDbMapping EntityDbMapping { get; set; }
36 
37         /// <summary>
38         /// 條件列
39         /// </summary>
40         public string WhereColumn { get; set; }
41 
42         /// <summary>
43         /// 查詢建造
44         /// </summary>
45         public QueryBuilder QueryBuilder { get; set; }
46 
47     }
48 }




 


資料繫結核心類




  1 using System;
  2 using System.Reflection;
  3 using System.Collections;
  4 using System.Collections.Generic;
  5 using System.Data.Common;
  6 using System.Data;
  7 using System.Linq;
  8 using System.Text;
  9 using System.Threading.Tasks;
 10 using Fast.Framework.Abstract;
 11 using Fast.Framework.Interfaces;
 12 using Fast.Framework.Models;
 13 
 14 namespace Fast.Framework.Extensions
 15 {
 16 
 17     /// <summary>
 18     /// 查詢建造擴充套件類
 19     /// </summary>
 20     public static class QueryBuilderExtensions
 21     {
 22 
 23         private static readonly MethodInfo fristBuildMethod;
 24 
 25         private static readonly MethodInfo listBuildMethod;
 26         private static readonly MethodInfo ofTypeMethod;
 27 
 28         private static readonly MethodInfo ofObjTypeMethod;
 29         private static readonly MethodInfo ofObjTypeGenericMethod;
 30 
 31         private static readonly MethodInfo toArrayMethod;
 32 
 33         private static readonly MethodInfo toListMethod;
 34 
 35         private static readonly MethodInfo toObjListMethod;
 36         private static readonly MethodInfo toObjListGenericMethod;
 37 
 38         /// <summary>
 39         /// 構造方法
 40         /// </summary>
 41         static QueryBuilderExtensions()
 42         {
 43             fristBuildMethod = typeof(DbDataReaderExtensions).GetMethod("FristBuild", new Type[] { typeof(DbDataReader) });
 44 
 45             listBuildMethod = typeof(DbDataReaderExtensions).GetMethod("ListBuild", new Type[] { typeof(DbDataReader) });
 46 
 47             ofTypeMethod = typeof(Enumerable).GetMethod("OfType");
 48 
 49             ofObjTypeMethod = typeof(Enumerable).GetMethod("OfType");
 50             ofObjTypeGenericMethod = ofObjTypeMethod.MakeGenericMethod(typeof(object));
 51 
 52             toArrayMethod = typeof(Enumerable).GetMethod("ToArray");
 53 
 54             toListMethod = typeof(Enumerable).GetMethod("ToList");
 55 
 56             toObjListMethod = typeof(Enumerable).GetMethod("ToList");
 57             toObjListGenericMethod = toObjListMethod.MakeGenericMethod(typeof(object));
 58         }
 59 
 60         /// <summary>
 61         /// 初始化
 62         /// </summary>
 63         /// <param name="dbType">資料庫型別</param>
 64         /// <param name="includeInfo">包括資訊</param>
 65         /// <param name="isMultipleResult">是否多結果</param>
 66         private static void Init(Models.DbType dbType, IncludeInfo includeInfo, bool isMultipleResult)
 67         {
 68             var identifier = dbType.MappingIdentifier();
 69             var parameterSymbol = dbType.MappingParameterSymbol();
 70 
 71             //條件列
 72             if (string.IsNullOrWhiteSpace(includeInfo.WhereColumn))
 73             {
 74                 var whereColumn = includeInfo.QueryBuilder.EntityDbMapping.ColumnsInfos.FirstOrDefault(f => f.IsPrimaryKey || f.ColumnName.ToUpper().EndsWith("ID"));
 75                 includeInfo.WhereColumn = whereColumn.ColumnName;
 76             }
 77 
 78             //排序列
 79             var orderByColumn = includeInfo.EntityDbMapping.ColumnsInfos.FirstOrDefault(f => f.IsPrimaryKey || f.ColumnName.ToUpper().EndsWith("ID"));
 80             if (orderByColumn != null)
 81             {
 82                 if (includeInfo.QueryBuilder.OrderBy.Count == 0)
 83                 {
 84                     includeInfo.QueryBuilder.OrderBy.Add($"{identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, orderByColumn.ColumnName)}");
 85                 }
 86             }
 87 
 88             if (!isMultipleResult)
 89             {
 90                 includeInfo.QueryBuilder.Where.Add($"{identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, includeInfo.WhereColumn)} = {parameterSymbol}{includeInfo.WhereColumn}");
 91             }
 92 
 93             var joinInfo = new JoinInfo();
 94             joinInfo.IsInclude = true;
 95             joinInfo.JoinType = JoinType.Inner;
 96             joinInfo.EntityDbMapping = includeInfo.EntityDbMapping;
 97             joinInfo.Where = $"{identifier.Insert(1, includeInfo.QueryBuilder.EntityDbMapping.Alias)}.{identifier.Insert(1, includeInfo.WhereColumn)} = {identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, includeInfo.WhereColumn)}";
 98 
 99             includeInfo.QueryBuilder.Join.Add(joinInfo);
100         }
101 
102         /// <summary>
103         /// Include資料繫結
104         /// </summary>
105         /// /// <param name="queryBuilder">查詢建造</param>
106         /// <param name="ado">Ado</param>
107         /// <param name="obj">物件</param>
108         /// <returns></returns>
109         public static void IncludeDataBind(this QueryBuilder queryBuilder, IAdo ado, object obj)
110         {
111             if (queryBuilder.IncludeInfos.Count > 0 && obj != null)
112             {
113                 var type = obj.GetType();
114 
115                 var isMultipleResult = false;
116 
117                 if (type.IsArray)
118                 {
119                     isMultipleResult = true;
120                     type = type.GetElementType();
121                 }
122                 else if (type.IsGenericType)
123                 {
124                     isMultipleResult = true;
125                     type = type.GenericTypeArguments[0];
126                 }
127 
128                 foreach (var includeInfo in queryBuilder.IncludeInfos)
129                 {
130                     Init(ado.DbOptions.DbType, includeInfo, isMultipleResult);
131 
132                     var propertyInfo = type.GetProperty(includeInfo.PropertyName);
133 
134                     object data = null;
135 
136                     if (!isMultipleResult)
137                     {
138                         var parameterValue = type.GetProperty(includeInfo.WhereColumn).GetValue(obj);
139                         includeInfo.QueryBuilder.DbParameters.Add(new DbParameterEx(includeInfo.WhereColumn, parameterValue));
140                     }
141 
142                     var sql = includeInfo.QueryBuilder.ToSqlString();
143                     var reader = ado.ExecuteReader(CommandType.Text, sql, ado.CreateParameter(includeInfo.QueryBuilder.DbParameters));
144 
145                     var fristBuildGenericMethod = fristBuildMethod.MakeGenericMethod(includeInfo.Type);
146 
147                     var listBuildGenericMethod = listBuildMethod.MakeGenericMethod(includeInfo.Type);
148 
149                     var ofTypeGenericMethod = ofTypeMethod.MakeGenericMethod(includeInfo.Type);
150 
151                     var toArrayGenericMethod = toArrayMethod.MakeGenericMethod(includeInfo.Type);
152 
153                     var toListGenericMethod = toListMethod.MakeGenericMethod(includeInfo.Type);
154 
155                     if (isMultipleResult)
156                     {
157                         data = listBuildGenericMethod.Invoke(null, new object[] { reader });
158 
159                         var list = ofObjTypeGenericMethod.Invoke(null, new object[] { data });
160 
161                         list = toObjListGenericMethod.Invoke(null, new object[] { data });
162 
163                         var objList = list as List<object>;
164 
165                         if (objList.Any())
166                         {
167                             var whereColumnProInfo = objList.FirstOrDefault()?.GetType().GetProperty(includeInfo.WhereColumn);
168                             if (whereColumnProInfo != null)
169                             {
170                                 foreach (var item in obj as IList)
171                                 {
172                                     var parameterValue = type.GetProperty(includeInfo.WhereColumn).GetValue(item);
173 
174                                     object value = null;
175 
176                                     if (includeInfo.PropertyType.IsArray || includeInfo.PropertyType.IsGenericType)
177                                     {
178                                         value = objList.Where(w => Convert.ToString(whereColumnProInfo.GetValue(w)) == Convert.ToString(parameterValue)).ToList();
179 
180                                         value = ofTypeGenericMethod.Invoke(null, new object[] { value });
181 
182                                         if (includeInfo.PropertyType.IsArray)
183                                         {
184                                             value = toArrayGenericMethod.Invoke(null, new object[] { value });
185                                         }
186                                         else if (includeInfo.PropertyType.IsGenericType)
187                                         {
188                                             value = toListGenericMethod.Invoke(null, new object[] { value });
189                                         }
190                                     }
191                                     else
192                                     {
193                                         value = objList.FirstOrDefault(w => Convert.ToString(whereColumnProInfo.GetValue(w)) == Convert.ToString(parameterValue)).ChanageType(includeInfo.PropertyType);
194                                     }
195 
196                                     propertyInfo.SetValue(item, value);
197                                 }
198                             }
199                         }
200                     }
201                     else
202                     {
203                         if (includeInfo.PropertyType.IsArray || includeInfo.PropertyType.IsGenericType)
204                         {
205                             data = listBuildGenericMethod.Invoke(null, new object[] { reader });
206 
207                             if (includeInfo.PropertyType.IsArray)
208                             {
209                                 data = toArrayGenericMethod.Invoke(null, new object[] { data });
210                             }
211                         }
212                         else
213                         {
214                             data = fristBuildGenericMethod.Invoke(null, new object[] { reader });
215                         }
216                         propertyInfo.SetValue(obj, data);
217                     }
218 
219                     if (includeInfo.QueryBuilder.IncludeInfos.Count > 0)
220                     {
221                         includeInfo.QueryBuilder.IncludeDataBind(ado, data);
222                     }
223 
224                 }
225             }
226         }
227 
228     }
229 }




 


 







相關文章