Linq takes more than 20 seconds to query a table with less than 100 records

Question

Unfortunately I haven't found a good answer for this problem yet. The answers and questions I have seen so far in here are about big tables with a lot of records.

I'm trying to query a table called Tickets with the following code:

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();

The table currently has less than 100 records, this query takes an average of 22 seconds to execute.

The code first model for it is as follows:

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; }
}

Any insight into this issue would be appreciated. Thank you very much!

Edit: Running the same query directly on SQL Server Management Studio also takes very long, about 9 to 11 seconds. So there might be an issue with the table itself.

Answer 1

I see several possible improvements.

For some reason you chose to deviate from the entity framework code fist conventions. One of them is the use of a List instead of an ICollection, another it that you omit to mention the foreign keys.

Use ICollection istead of List

Are you sure that Ticket.TicketFiles[4] has a defined meaning? And what would Ticket.TicketFiles.Insert(4, new TicketFile()) mean?

Better stick to an interface that prohibits usage of functions that have no defined meaning. Use ICollection<TicketFile>. This way you'll have only functions that have a proper meaning in the context of a database. Besides it gives entity framework the freedom to chose the most efficient collection type to execute its queries.

Let your classes represent the tables

Let your classes just be POCOs. Don't add any functionality that is not in your tables.

In entity framework the columns of a table are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

Let entity framework decide what's the most efficient to initialize the data in your sequences. Don't use a constructor where you create a List, which will be immediately thrown away by entity framework to replace it with its own ICollection. Don't automatically initialize property Deleted, if entity framework immediately replaces it with its own value.

You will probably have only one procedure where you will add a Ticket to the database. Use this function to properly initialize the field of any "newly added Ticket"

Don't forget the foreign keys

You defined several relations between your tables (one-to-many, or many-to-many?) but you forgot to define the foreign keys. Because of your use of virtual entity framework can understand that it needs foreign keys and will add them, but in your query you need to write e.Status != null && e.Status.Id == statusId, while obviously you could just use the foreign key e.StatusId == statusId. For this you don't have to join with the Statuses table

Another reason to specify the foreign keys: they are real columns in your tables. If you define that these classes represent your tables, they should be in these classes!

Only select the properties you actually plan to use

One of the slower parts of a database query is the transport of the selected data from the database management system to your local process. Hence it is wise to select only the data you actually plan to use.

Example. There seems to be a one-to-many between a User and a Ticket: every User has zero or more Tickets, every Ticket belongs to exactly one User. Suppose User 4 has 20 Tickets. Every Ticket will have a UserId with a value 4. If you fetch these 20 Tickets without a proper Select you will fetch all properties of the same User 4 once per Ticket, and you will transport the data of this same User 20 times (with all his properties, and maybe all his relations). What a waste of processing power!

Always use Select to query your data and Select only the properties you actually plan to use. Only use Include if you plan to updated the Included data.

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
    {
        ... 
    }