查询性能问题 返回
XXX 发布于2025/9/28
目前用的是5.1.4.173版本，表中有2000条数据，一次查询其中1000条
记录到的SQL语句，执行时间只有十几或几十毫秒，但是框架转为实体，却要耗时3-5秒。
var query = DB.Queryable<OrderDeliveryProductBaseEntity>()
.InnerJoin<OrderProductSimpleEntity>((orderDeliveryProduct, orderProduct) => orderDeliveryProduct.OrderProductId == orderProduct.OrderProductId)
.InnerJoin<OrderDeliverySimpleEntity>((orderDeliveryProduct, orderProduct, orderDelivery) => orderDeliveryProduct.DeliveryNo == orderDelivery.DeliveryNo)
.InnerJoin<OrderSimpleEntity>((orderDeliveryProduct, orderProduct, orderDelivery, order) => orderDelivery.SaleNo == order.SaleNo)
.InnerJoin<CustomerEntity>((orderDeliveryProduct, orderProduct, orderDelivery, order, customer) => order.CustomerId == customer.CustomerId)
.OrderByDescending((orderDeliveryProduct, orderProduct, orderDelivery, order, customer) => orderDelivery.AddTime)
.OrderBy((orderDeliveryProduct, orderProduct, orderDelivery, order, customer) => orderDeliveryProduct.Sequence)
.Select((orderDeliveryProduct, orderProduct, orderDelivery, order, customer) => new OrderDeliveryProductStatis()
{
DeliveryProductId = orderDeliveryProduct.DeliveryProductId,
DeliveryNo = orderDeliveryProduct.DeliveryNo,
Sequence = orderDeliveryProduct.Sequence,
OrderProductId = orderDeliveryProduct.OrderProductId,
ProductNum = orderDeliveryProduct.ProductNum,
ProductPrice = orderDeliveryProduct.ProductPrice,
OrderProduct = new OrderProductSimpleEntity()
{
OrderProductId = orderProduct.OrderProductId,
Sequence = orderProduct.Sequence,
Source = orderProduct.Source,
PurchasePrice = orderProduct.PurchasePrice,
Name = orderProduct.Name,
BatchName = orderProduct.BatchName,
Color = orderProduct.Color,
Size = orderProduct.Size
},
CustomerEmail = customer.Email,
OrderDelivery = new OrderDeliverySimpleEntity()
{
DeliveryNo = orderDeliveryProduct.DeliveryNo,
Forwarder = orderDelivery.Forwarder,
ReceiveNo = orderDelivery.ReceiveNo,
SendTime = orderDelivery.SendTime,
TransitDate = orderDelivery.TransitDate,
LogisticsProviderId = orderDelivery.LogisticsProviderId,
LogisticeNo = orderDelivery.LogisticeNo,
LogisticsFee = orderDelivery.LogisticsFee,
},
Order = new OrderSimpleEntity()
{
SaleNo = order.SaleNo,
BusinessTeamId = order.BusinessTeamId,
WebsiteId = order.WebsiteId,
BusinesserId = order.BusinesserId,
OrderType = order.OrderType,
InnerRemark = order.InnerRemark,
PaidTime = order.PaidTime
}
});
return query.ToPageList(pager.Page, pager.Limit);
热忱回答（10）
-
fate sta VIP0
2025/9/28
二维对象都注释掉看看，要用多久
0 回复 -
XXX VIP0
2025/9/28
@fate sta：二维对象全部注释掉，时间就掉到几十毫秒。
刚刚又试了下，不注释二维对象，改用GetDataTable方法，时间也是毫秒级的的
0 回复 -
fate sta VIP0
2025/9/28
@XXX：复杂对象这种SELECT映射一次查太多会慢些。
改用查询一维的方式
或者用导航查询试试。
0 回复 -
fate sta VIP0
2025/9/28
多层级查询，导航最快
0 回复 -
XXX VIP0
2025/9/28
@fate sta：慢一些可以理解，但是慢这么多，太夸张了吧。几十毫秒与几千毫秒的差别。
只能改用一维查询了吗？
主要是查询条件，经常要用到关联的其它表字段，想着都innerjoin了，就一次全返回，不要再去数据库中再捞一次关联表了。
0 回复 -
fate sta VIP0
2025/9/280 回复
-
fate sta VIP0
2025/9/28
建表+测试数据 。不提供DEMO我这边不能重现 那就会浪费你的时间。
0 回复 -
XXX VIP0
2025/9/280 回复
-
fate sta VIP0
2025/9/29
这个我看一下，好不好优化。
0 回复 -
XXX VIP0
2025/9/30
感觉应该是浪费在无谓的字段反射上了。一个Db类，如果字段多的话，耗时就长，不论我是全字段查，还是指定只查某几个字段。
只保留要查询的字段，暂时删除其它字段，速度就快非常多，能从几百毫秒降到几十毫秒
0 回复
