时间戳

首页 > 解决方案 > Linq 查询不到 100 条记录的表耗时超过 20 秒

sql - Linq 查询不到 100 条记录的表耗时超过 20 秒

问题描述

不幸的是，我还没有为这个问题找到一个好的答案。到目前为止，我在这里看到的答案和问题是关于有很多记录的大表。

我正在尝试使用以下代码查询名为 Tickets 的表：

var Status = ticketStatusService.GetByName("New");
string StatusID = Status.Id;
var tickets = db.Tickets.Where(e => 
              !e.Deleted && 
              e.Project == null && 
              e.Status != null && 
              e.Status.Id == StatusID);
var list = tickets.ToList();

该表当前少于 100 条记录，此查询平均需要 22 秒才能执行。

它的代码优先模型如下：

public class Ticket : Base
{
    [Key]
    [Required]
    public Guid Id { get; set; }

    [Display(Name = "Date")]
    public DateTime RowDate { get; set; } = DateTime.Now;

    public bool Deleted { get; set; } = false;

    [Index(IsUnique = true)]
    public int? Number { get; set; }

    [Display(Name = "Ticket Subject")]
    public string Subject { get; set; }

    [Display(Name = "Notes (Employees Only)")]
    public string Notes { get; set; }

    [Display(Name = "E-Mail")]
    public string From { get; set; }

    [Display(Name = "Phone Number")]
    public string Phone { get; set; }

    [Display(Name = "Secondary Phone Number")]
    public string PhoneAlt { get; set; }

    [Display(Name = "Client Name")]
    public string Name { get; set; }

    [Display(Name = "Message")]
    public string Messages { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime? OpenDate { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime? CloseDate { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime? AssignedDate { get; set; }

    public bool? Origin { get; set; }

    public virtual User AssignedUser { get; set; }

    public virtual List<TicketFile> TicketFiles { get; set; }

    public virtual List<Task> Tasks { get; set; }

    public virtual Project Project { get; set; }

    public virtual TicketStatus Status { get; set; }

    public virtual TicketClosingCategory TicketClosingCategory { get; set; }
    public virtual TicketGroup TicketGroup { get; set; }

    public virtual TicketPriority TicketPriority { get; set; }
}

对此问题的任何见解将不胜感激。非常感谢！

编辑：直接在 SQL Server Management Studio 上运行相同的查询也需要很长时间，大约 9 到 11 秒。因此，表本身可能存在问题。

标签： sqlasp.netsql-serverentity-frameworklinq

解决方案

我看到了几个可能的改进。

出于某种原因，您选择了偏离实体框架代码拳头约定。其中一个是使用 aList而不是 an ICollection，另一个是您省略提及外键的 it。

使用 ICollection 而不是 List

你确定它Ticket.TicketFiles[4]有明确的含义吗？这Ticket.TicketFiles.Insert(4, new TicketFile())意味着什么？

最好坚持使用禁止使用没有明确含义的功能的界面。使用ICollection<TicketFile>. 这样，您将只有在数据库上下文中具有适当含义的函数。此外，它使实体框架可以自由选择最有效的集合类型来执行其查询。

让你的类代表表格

让您的课程成为 POCO。不要添加任何不在您的表格中的功能。

在实体框架中，表的列由非虚拟属性表示。虚拟属性表示表之间的关系（一对多，多对多，...）

让实体框架决定初始化序列中的数据最有效的方法。不要在创建 List 的地方使用构造函数，它会立即被实体框架丢弃以替换为自己的ICollection. Deleted如果实体框架立即将其替换为自己的值，请不要自动初始化属性。

您可能只有一个向数据库添加票证的过程。使用此功能正确初始化任何“新添加的工单”的字段

不要忘记外键

您在表之间定义了多个关系（一对多，还是多对多？），但您忘记定义外键。由于您使用virtual实体框架可以理解它需要外键并将其添加，但是在您的查询中您需要编写e.Status != null && e.Status.Id == statusId，而显然您可以只使用外键e.StatusId == statusId。为此，您不必加入 Statuses 表

指定外键的另一个原因是：它们是表中的真实列。如果您定义这些类代表您的表，它们应该在这些类中！

仅选择您实际计划使用的属性

数据库查询中较慢的部分之一是将所选数据从数据库管理系统传输到本地进程。因此，明智的做法是仅选择您实际计划使用的数据。

例子。Usera和 a之间似乎是一对多的Ticket：每个 User 都有零个或多个Tickets，每个都Ticket恰好属于一个User。假设User4 有 20 Tickets。每个Ticket都有一个UserId值为4 _ _ _关系）。多么浪费处理能力！TicketsSelectUserTicketUser

始终使用 Select 来查询您的数据并仅选择您实际计划使用的属性。仅当您计划更新包含的数据时才使用包含。

var tickets = dbContext.Tickets.Where(ticket => !ticket.Deleted

    // improvement: use foreign keys
    && ticket.ProjectId == 0 (or == null, if ProjectId nullable)
    && ticket.StatusId == statusId)       // no Join with Statuses needed

    .Select(ticket => new
    {
        ... 
    }

推荐阅读