High Entity Framework Core execution times

Question

I've built a simple Asp.Net Core MVC app for use at work displaying data from a view in our MSSQL database. When querying this view from SSMS, execution time is ~100ms on average. When the same query is executed within my app, execution time is anywhere from ~800ms to ~1.5s.

Here is the LINQ from the controller:

public IActionResult Index()
{
    var query =
    from p in _context.vWebQuery
    where p.Almachine == "600L"
    orderby p.Aldatsta
    select p;
    return View(query);
}

Here is the Entity Class:

namespace BetaKestrel2.Models
{
    public class vWebQuery
    {
        public double Wruntim { get; set; }
        public short Wper { get; set; }
        public double Quantity { get; set; }
        [Column("Total Op TIme")]
        public double? TotalTime { get; set; }
        public string Alwon { get; set; }
        public short Alopnum { get; set; }
        public string Almachine { get; set; }
        public double Alpersta { get; set; }
        [DisplayFormat(DataFormatString = "{0:F2}")]
        public double Allen { get; set; }
        public short Alprevop { get; set; }
        [DisplayFormat(DataFormatString = "{0:d}")]     
        public DateTime Aldatsta { get; set; }
        public string Altimsta { get; set; }
        public string Alstatus { get; set; }
        public short Alperno { get; set; }
        public string Macid { get; set; }
        public string Macdesc { get; set; }
        public string Partid { get; set; }
        public string Partrevisionid { get; set; }
        public string Routingmethod { get; set; }
        public double Wqleft { get; set; }
        public string Wstate { get; set; }
        public string Wdesc { get; set; }
        public string Partdesc { get; set; }
        public string Toolid { get; set; }
        public string Childpartid { get; set; }
        public string msection { get; set; }

    }
}

And the DbContext:(using .Net Core 3.0 for the .HasNoKey())

public partial class EfacDBContext : DbContext
{
    public EfacDBContext()
    {
    }

    public EfacDBContext(DbContextOptions<EfacDBContext> options)
        : base(options)
    {
    }

    public DbSet<vWebQuery>         vWebQuery { get; set; }
    public DbSet<vGRN>              vGRN      { get; set; }
    public DbSet<vQuotationTracker> vQuotationTracker { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        modelBuilder.Entity<vWebQuery>(entity =>
        {
            entity.HasNoKey();
            entity.ToTable("vwebquery");
        });

And an example .cshtml View:

@model IEnumerable<BetaKestrel2.Models.vWebQuery>

@{
    ViewData["Title"] = "600L";
    string highlight = "";
}


<h1>Work Centre Plan - @ViewData["Title"]</h1>

<table class="table table-sm table-bordered">
    <thead>
        <tr>
            <th>Works Order</th>
            <th>Part Number</th>
            <th>Description</th>
            <th>Op Number</th>
            <th>Quantity</th>
            <th>Latest Start Date</th>
            <th>Previous Op</th>
            <th>Total Op Time (mins)</th>
            <th>Status</th>
            <th>Qty Left</th>
        </tr>
    </thead>
    <tbody>

    @foreach (var item in Model)
    {

        @if (item.Wstate == "COMP")
        {
            highlight = "background-color: green;";
        }
        else if (item.Alprevop == 0)
        {
            highlight = "background-color: yellow;";
        }
        else
        {
            highlight = "";
        }
        <tr style="@highlight">
            <td>@Html.DisplayFor(modelItem => item.Alwon)</td>
            <td>@Html.DisplayFor(modelItem => item.Partid)</td>
            <td>@Html.DisplayFor(modelItem => item.Partdesc)</td>
            <td>@Html.DisplayFor(modelItem => item.Alopnum)</td>
            <td>@Html.DisplayFor(modelItem => item.Quantity)</td>
            <td>@Html.DisplayFor(modelItem => item.Aldatsta)</td>
            <td>@Html.DisplayFor(modelItem => item.Alprevop)</td>
            <td>@Html.DisplayFor(modelItem => item.TotalTime)</td>
            <td>@Html.DisplayFor(modelItem => item.Wstate)</td>
            <td>@Html.DisplayFor(modelItem => item.Wqleft)</td>
        </tr>
    }
    </tbody>
</table>

EF Core converts to the following SQL

SELECT 
    [v].[Aldatsta]
    ,[v].[Allen]
    ,[v].[Almachine]
    ,[v].[Alopnum]
    ,[v].[Alperno]
    ,[v].[Alpersta]
    ,[v].[Alprevop]
    ,[v].[Alstatus]
    ,[v].[Altimsta]
    ,[v].[Alwon]
    ,[v].[Childpartid]
    ,[v].[Macdesc]
    ,[v].[Macid]
    ,[v].[Partdesc]
    ,[v].[Partid]
    ,[v].[Partrevisionid]
    ,[v].[Quantity]
    ,[v].[Routingmethod]
    ,[v].[Toolid]
    ,[v].[Total Op TIme]
    ,[v].[Wdesc]
    ,[v].[Wper]
    ,[v].[Wqleft]
    ,[v].[Wruntim]
    ,[v].[Wstate]
    ,[v].[msection]
FROM [vwebquery] AS [v]
WHERE 
    [v].[Almachine] = N'BENDD'
AND [v].[Almachine] IS NOT NULL
ORDER BY 
    [v].[Aldatsta]

Results in:

info: Microsoft.AspNetCore.Mvc.ViewFeatures.ViewResultExecutor[4] Executed ViewResult - view Index executed in 1540.6805000000002ms.

info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2] Executed action BetaKestrel2.Controllers.BenddController.Index (BetaKestrel2) in 1541.4348ms

info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1] Executed endpoint 'BetaKestrel2.Controllers.BenddController.Index (BetaKestrel2)'

info: Microsoft.AspNetCore.Hosting.Diagnostics[2] Request finished in 1541.8467ms 200 text/html; charset=utf-8

Whereas, in SSMS

Total execution time 124ms

I tried the AsNoTracking() and that didn't make a difference. Your last comment got me curious. 291 rows are returned from the query so I tried .Take(5) and execution time went down to 24ms. Could it literally just be the iteration of my foreach loop in the view that is taking up all the time?

Answer 1

When querying this view from SSMS, execution time is ~100ms on average. When the same query is executed within my app, execution time is anywhere from ~800ms to ~1.5s.

SQL Server Management Studio (SSMS)

• RAW SQL NO OVER HEAD

EF Entityfamework

put this

var query = 
from p in _context.vWebQuery
where p.Almachine == "BENDD"
orderby p.Aldatsta
select p;

in a block which times it, loop it 3 times and take the last time.

--create dbContext here. (_context)

--start loop (run 3 times)

    --start timer

var query = ( 
            from p in _context.vWebQuery
           where p.Almachine == "BENDD"
          orderby p.Aldatsta
          select p
        ).Tolist();

    --end timer -this is what you want to compare after the 3 run.
               - yes it will be slower but you could make as non tracking 
               - should be a must fairer comparison.

-- end loop

tip - put this in debug, put break-point on index... change your query to ToList(), so that it executes the query at that point and not wen its in the view section.

Code from test: Modified

        for (int i = 0; i <= 3; i++)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            var query = (from p in _context.vWebQuery
                         where p.Almachine == "600L"
                         orderby p.Aldatsta
                         select p
            ).ToList();

            stopwatch.Stop();
            Console.WriteLine(stopwatch.ElapsedMilliseconds);
        }
     .....