EF takes ~90 seconds to do a one table query

c# entity-framework-core

Question

I have the following query in Entity Framework Core:

private static List<Provider> JoinProvTables(State state)
{
   using (var db = new ProvContext(state))
   {
        return db.Provider
            //.Include(provider => provider.ServLocation)
            .ToList();
   }
}

public static IEnumerable<T> Name(State state, string first, string last, string middle = null)
{
          return (IEnumerable<T>)JoinProvTables(state)
                .Where(provider => String.Equals(provider.FirstName, first, StringComparison.OrdinalIgnoreCase)
                      && String.Equals(provider.LastName, last, StringComparison.OrdinalIgnoreCase))
                .ToList();
}

This query is actually a bit bigger in code (the logic I cut is the same logic, but for different table joins) than what I have above, but right now I'm only looking at this one part, where I want to get someone's name. This part is where the problem is. The query here takes 80-90 seconds to go through ~65,000 rows.

And the JoinPRNTables method will only get worse as I further develop it, because I need data on about 5-6 related tables I haven't yet Included()ed. Due to the use case of my application, every query will always need data from all the included tables, that's not something I can get around.

Here's the context:

namespace Company.Domain.Application.Models
{
    using Microsoft.EntityFrameworkCore;

    public class ProvContext : DbContext
    {
        private string _state;

        public ProvContext(State state)
        {
            _state = state.ToString().ToUpper();
        }

        public DbSet<Provider> Provider { get; set; }
        public DbSet<ServLocation> ServLocation { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                $"server=SERVER;database=DATABASE_{_state};trusted_connection=true;");
        }
    }
}

And the Provider model:

namespace Company.Domain.Application.Models
{
    using System;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("TABLE", Schema = "SCHEMA")]
    public class PRN_Provider
    {
        [Key]
        public int ProvID { get; set; }
        public PRN_ServLocation PRN_ServLocation { get; set; }

        [Display(Name = "First Name")]
        [StringLength(100)]
        public string FirstName { get; set; }

        [Display(Name = "Middle Name")]
        [StringLength(100)]
        public string MiddleName { get; set; }

        [Display(Name = "Last Name")]
        [StringLength(100)]
        public string LastName { get; set; }

        //... more of the same structure for fields
   }
}

Is there a way I can cut this query time down? When I call it as QueryBy<PRN_Provider>.Name(state, "JOHN", "SMITH").FirstOrDefault().FirstName, it takes 90 seconds, and personal information like FirstName, etc are all on the first and only table being used at the moment, so for now there are no JOINs or related tables being used. My Scrum master is telling me 90 seconds is alright, but I'd still like to know if there's a way to cut the the time down.

1
1
10/18/2019 3:54:58 PM

Popular Answer

You are loading everything in memory. Just don't do that.

Your problem starts with using (var db = new ProvContext(state)), this forces you to eager-load the entire table.

  • Use dependency injection or another strategy to keep the context open.

  • make sure you perform the .First() directly on the IQueryable

public static IQueryable<T> Name(State state, string first, string last, string middle = null)
{
      return // (IEnumerable<T>)JoinProvTables(state)
             db.Provider  // -- manage the lifetime some other way
            .Where(provider => String.Equals(provider.FirstName, first, StringComparison.OrdinalIgnoreCase)
                  && String.Equals(provider.LastName, last, StringComparison.OrdinalIgnoreCase))
           // .ToList()  -- this takes all that time
            ;
}
3
10/18/2019 4:07:00 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow