Efficiency of finding one database record using two ID's

Question

I'm using C#, .NET Core and the entity framework and am currently working on optimizing various functions for better performance.

One of the main functions is getting a calculation based on two indexes; a product and machine. The records in this database table is currently at 500 rows but can go upwards to 10.000.

My code for finding a record in this table is as follows:

public Calculation GetCalculationByProduct(Machine currentMachine, Product currentProduct)
{
    IList<Calculation> calculations = _calculationRepository.Index().ToList();

    for (int i = 0; i < calculations.Count(); i++)
    {
      if (calculations[i].MachineForeignKey == currentMachine.ID && calculations[i].ProductRef.ID == currentProduct.ID)
      {
        return calculations[i];
      }
    }
        return null;
    }

The function searches for the relative calculation based on both the product and the machine; since a machine and a product can both have multiple calculations.

The actual model looks like this:

public class Calculation
{
    public int ID { get; set; }

    public int MachineForeignKey { get; set; }

    public Machine MachineRef { get; set; }

    public Product ProductRef {get; set; }

    public double Value { get; set; }
}

A product model and Machine model both use this like so:

public class Product
{
    [Key]
    public int ID { get; set; }

    [Required]
    public string ProductName { get; set; }

    public string Name { get; set; }

    public IEnumerable<Calculation> Calculations { get; set; }
}

public class Machine
{
    [Key]
    public int ID { get; set; }

    public string OrderNo { get; set; }

    public IEnumerable<Calculation> Calculations { get; set; }
}

The repository used is a basic linq implementation using generics like so:

    public IEnumerable<TEntity> Index()
    {
        return _context.Set<TEntity>();
    }

And the relations for the calculation / model have been set with a modelbuilder like this:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Machine>()
            .HasMany(m => m.Calculations)
            .WithOne(s => s.MachineRef)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<Product>()
            .HasMany(m => m.CalculatedValues)
            .WithOne(s => s.ProductRef)
            .OnDelete(DeleteBehavior.Cascade);

    }

I first used LINQ like so:

 _calculationRepository.Index().Where(c => c.MachineForeignKey == Current_Machine.ID && c.ProductRef.ID == Current_Product.ID).FirstOrDefault();

But that caused quite bad performance; where it was around 2000ms

Currently, it goes through 500 records in about 900ms. Using visual studio's diagnostic tools I could see that this function was slowing everything down quite a lot; especially since the function is used for nearly every calculation done.

I'm looking to optimize the code, but any other advice is welcome.

Answer 1

When using EF, it would be better if you query the database then create a list of the results. in GetCalculationByProduct method, see this line :

IList<Calculation> calculations = _calculationRepository.Index().ToList();

at this line, it will populate all table records and stores it in memory, which is something you don't need to be a habit in most cases.

While this line of code :

 _calculationRepository.Index().Where(c => c.MachineForeignKey == Current_Machine.ID && c.ProductRef.ID == Current_Product.ID).FirstOrDefault();

it'll build the SQL query, then execute it, and then gets the first record.

So, what you are actually missing here is AsNoTracking. You can do it like this :

_calculationRepository.Index().AsNoTracking().FirstOrDefault(c => c.MachineForeignKey == Current_Machine.ID && c.ProductRef.ID == Current_Product.ID);

by default, EF catches any returned entity, so any changes to that entity can be saved by running SaveChanges method. If you want to return results that you don't want to do any changes to them (just read-only results) use AsNoTracking this would make the execution a bit faster as the entity results won't be cached.

Answer 2

Disclaimer: my practical knowledge of EF is limited. But when you are using a database the first step is to run an execution plan against your queries. Make sure that they run fast enough and within predictable times regardless of how many records you are storing.

You may have to add indexes, for instances on fields being searched or JOIned. So I would start by reviewing the table structure and the overall data model. It may be lacking optimization. If the DB structure is the problem EF will not improve things, although there are things it can do like caching data or the execution plan itself for reuse.

Also ask yourself if EF+LINQ is really the best way to do it, or if using a stored procedure would make sense perhaps. The whole logic does not necessarily needs to be handled by the client. Stored procedures can be used with EF too.

As a rule you don't want to pull out more data to the client than strictly necessary.

Suggested reading:

• Better Entity Framework Core Performance by Reading Execution Plans
• Performance considerations for EF 4, 5, and 6 (long read but I learned a lot)