EF核心：如何更好地提出请求

Question

我有一个带有1M记录的表设备。表有大约40列，对于大多数请求而言，其中只需要10列。

的关系

• 该表与： 载体表具有1000行和50列
• 。 devicemodel仅具有10列和5列的表，但是devicemodel表与表devicecharacteristic（仅5个记录和3列）通过devicemodel_devicecharacteristic_mapping （多次接触），该表有2列和20行。所有这3个具有“静态”数据的表，可以更改非常罕见。
• devicedatausage带有2M记录的表

我的默认linqtoentities请求是：

var query = _context.Devices
                    .Include(a => a.Carrier)
                    .Include(a => a.DeviceModel)
                    .ThenInclude(a => a.CharacteristicMappings)
                    .AsQueryable<Device>();

query = query.Where(d => d.CarrierId != null);
query = query.Where(p => p.SyncVerizon || p.SyncSureMdm);
query = query.Where(d => d.DeviceModel.CharacteristicMappings.Any(t => t.CharacteristicId == deviceCharacteristicId));

IOrderedQueryable<Device> orderedQuery= query.OrderByDescending(device => device.LastConnectionDate);

var devices = new PagedList<DeviceDisplayAdminDto>(orderedQuery.ProjectTo<DeviceDisplayAdminDto>(_mapperConfig), pageIndex, pageSize);

其中devicedisplayadmindto是：

public class DeviceDisplayAdminDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string IMEI { get; set; }
    public int? DeviceModelId { get; set; }
    public string DeviceModelDescription { get; set; }
    public string StatusString { get; set; }
    public DeviceStatus Status { get; set; }

    public bool SyncVerizon { get; set; }
    public bool SyncSureMdm { get; set; }

    public long? DataUsageCurrentMonth { get; set; }
    public long? DataUsageCurrentDay { get; set; }
    public int? CarrierId { get; set; }
    public string CarrierName { get; set; }

    public List<string> Characteristics { get; set; }
    public bool IsDataUsageExist { get; set; }

    public DateTime? LastConnectionDate { get; set; }
    public bool IsInactiveLongTimeAndNotSuspended { get; set; }
    public bool IsSuspensionRequestAllowed { get; set; }
}

自动应用程序规则：

 CreateMap<Device, DeviceDisplayAdminDto>()
            .ForMember(d => d.Status, o => o.MapFrom(s  => s.Status))
            .ForMember(d => d.StatusString, o => o.MapFrom(s => s.Status.ToString().SplitByWords()))
            .ForMember(d => d.DeviceModelDescription, o => o.MapFrom(s => (s.DeviceModel != null) ? s.DeviceModel.GetDeviceModelDescription() : Tms.Core.Constants.Common.NotSet))
            .ForMember(d => d.CarrierName, o => o.MapFrom(s => (s.Carrier != null) ? s.Carrier.Name : string.Empty))
            .ForMember(d => d.IsSuspensionRequestAllowed, o => o.MapFrom(s => s.DeviceModel.CharacteristicMappings.Where(a => a.Characteristic.IsSuspensionRequestAllowed).Count() > 0))
            .ForMember(d => d.IsInactiveLongTimeAndNotSuspended, o => o.MapFrom(s => 
                (s.Status == DeviceStatus.Active || s.Status == DeviceStatus.PreActive)
                && s.DeviceModel.CharacteristicMappings.Where(a => a.Characteristic.IsSuspensionRequestAllowed).Count() > 0
                && (s.LastConnectionDate == null 
                || s.LastConnectionDate.Value < DateTime.UtcNow.AddDays(-Tms.Core.Constants.DeviceManagementConstants.SuspensionDays))))
            .ForMember(d => d.IsDataUsageExist, o => o.MapFrom(s => s.VerizonDataUsage.Count() > 0))
            .ForMember(d => d.Characteristics, o => o.MapFrom(s => (s.DeviceModel != null) ? s.DeviceModel.CharacteristicMappings.Select(a => a.Characteristic.Characteristic) : null))
            ;

此请求从1M记录中执行25个顶级项目4-6秒。我想提高性能。

因此，据我了解，使用相关表的映射仅针对选定（在我的情况25个项目）记录中进行，对吗？我可以通过链接的表现而放松吗？

创建视图并从中选择有任何意义吗？默认情况下需要哪些索引？还有什么可以帮助我？

添加了

生成的SQL（真正说，我不明白它是如此复杂）

SELECT [t].[CarrierId], 
    CASE WHEN [c].[Id] IS NOT NULL THEN [c].[Name] ELSE N'' END, 
    CASE WHEN [d0].[Id] IS NOT NULL THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, 
    [t].[Id], 
    [c].[Id], 
    [d0].[Id], 
    [t0].[Characteristic], 
    [t0].[DeviceModelId], 
    [t0].[CharacteristicId], 
    [t0].[Id], 
    [t].[DataUsageCurrentDay], 
    [t].[DataUsageCurrentMonth], 
    [d0].[ApplyType], 
    [d0].[Deleted], 
    [d0].[Manufacturer], 
    [d0].[Model], 
    [d3].[DeviceModelId], 
    [d3].[CharacteristicId], 
    [t].[DeviceModelId], 
    [t].[IMEI], 
    CASE WHEN ( SELECT COUNT(*) FROM [DeviceVerizonDataUsage] AS [d4] WHERE [t].[Id] = [d4].[DeviceId]) > 0 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, 
    CASE WHEN ([t].[Status] IN (1, 0) AND (( SELECT COUNT(*) FROM [DeviceModel_DeviceCharacteristic_Mapping] AS [d5] INNER JOIN [DeviceCharacteristic] AS [d6] ON [d5].[CharacteristicId] = [d6].[Id] WHERE (([d0].[Id] IS NOT NULL) AND ([d0].[Id] = [d5].[DeviceModelId])) AND ([d6].[IsSuspensionRequestAllowed] = CAST(1 AS bit))) > 0)) AND (([t].[LastConnectionDate] IS NULL) OR ([t].[LastConnectionDate] < DATEADD(day, CAST(-90.0E0 AS int), GETUTCDATE()))) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, 
    CASE WHEN ( SELECT COUNT(*) FROM [DeviceModel_DeviceCharacteristic_Mapping] AS [d7] INNER JOIN [DeviceCharacteristic] AS [d8] ON [d7].[CharacteristicId] = [d8].[Id] WHERE (([d0].[Id] IS NOT NULL) AND ([d0].[Id] = [d7].[DeviceModelId])) AND ([d8].[IsSuspensionRequestAllowed] = CAST(1 AS bit))) > 0 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END, 
    [t].[LastConnectionDate], 
    [t].[Name], 
    [t].[Status], 
    [t].[SyncSureMdm], 
    [t].[SyncVerizon] 
    FROM ( SELECT [d].[Id], [d].[CarrierId], [d].[DataUsageCurrentDay], [d].[DataUsageCurrentMonth], [d].[DeviceModelId], [d].[IMEI], [d].[LastConnectionDate], [d].[Name], [d].[Status], [d].[SyncSureMdm], [d].[SyncVerizon] FROM [Device] AS [d] WHERE ([d].[CarrierId] IS NOT NULL) AND (([d].[SyncVerizon] = CAST(1 AS bit)) OR ([d].[SyncSureMdm] = CAST(1 AS bit))) ORDER BY [d].[Name] OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY ) AS [t] LEFT JOIN [Carrier] AS [c] ON [t].[CarrierId] = [c].[Id] LEFT JOIN [DeviceModel] AS [d0] ON [t].[DeviceModelId] = [d0].[Id] LEFT JOIN ( SELECT [d2].[Characteristic], [d1].[DeviceModelId], [d1].[CharacteristicId], [d2].[Id] FROM [DeviceModel_DeviceCharacteristic_Mapping] AS [d1] INNER JOIN [DeviceCharacteristic] AS [d2] ON [d1].[CharacteristicId] = [d2].[Id] ) AS [t0] ON [d0].[Id] = [t0].[DeviceModelId] LEFT JOIN [DeviceModel_DeviceCharacteristic_Mapping] AS [d3] ON [d0].[Id] = [d3].[DeviceModelId] ORDER BY [t].[Name], [t].[Id], [c].[Id], [d0].[Id], [t0].[DeviceModelId], [t0].[CharacteristicId], [t0].[Id], [d3].[DeviceModelId]