locked
Core 3.1 First query to db takes a long time RRS feed

  • Question

  • User-954539637 posted

    Hi,

    I have seen this issue all over the web and I have tried many of the suggested scenarios without a lot of success. Sure, I can shave a little bit off the DB query, but not a significant amount of time.

    Here is my setup

    1) .Net Core 3.1.3 web API

    2) SQL 2019

    3) Postman

    4) Two huge tables (appx 25  million records)

    5) I am using migrations (code first)

    The first request will take about 20 second to execute and sometime will timeout. But all subsequent requests will take about 2-3 seconds to return results. I am doing a search with "contain" as part of the condition. So I realize that will add some inefficiency to the query.

    I have tried the warming suggestion (https://andrewlock.net/running-async-tasks-on-app-startup-in-asp-net-core-part-1/) and that did little or nothing to solve this issue.

    I have also seen the suggestion of running a task on startup, which I do not think is a solution to this issue.

    So I am looking for a suggestion on the best way to shave time off the query without recreating the wheel. 

    Here is a sample of a query.

    await _context.Companies
    .AsNoTracking()
    .Where(s => s.Zip == postalCode && s.CompanyName.Contains(companyName))
    .ToListAsync();

    Any suggestions would be very helpful.

    Thanks,

    Tom

    Friday, May 29, 2020 4:48 PM

Answers

All replies