High Entity Framework Core execution times

asp.net-core entity-framework-core

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?

1
1
9/5/2019 12:25:55 PM

Popular Answer

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);
        }
     .....
0
9/5/2019 11:21:35 AM


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