Cache Tag Helper And Remote SQL Connection Performance Issiue

[cihadsevim]

I've a sql connection which is connected to an Azure Remote SQL Database. Because it's a remote connection I would like to cache some of the output via using tag helper.Goal is speeding up the page responses.

I implement some @DateTime.Now blocks on view to see is it working?, it works, it caches the page between tags however after the second hit the load speed is the same. Output shows that it cahced however page is still slow at the first load and after other loads too.

Am I missing something?

[HttpGet]
[Route(URLPaths.categoryList)]
public IActionResult List()
{
    return View(_categoryRepository.Read());
}

`<cache enabled="true" expires-after="@TimeSpan.FromMinutes(10)">
    <div class="col-lg-1 col-md-2 col-sm-4 col-xs-6 thumb">
        <div class="thumb-inside">
            <a class="thumbnail" title="@item.Anchor" href="@Discount.Web.URLPaths.category@item.Slug">
                <img class="img-responsive" src="@Discount.Web.FilePaths.category@item.Picture" alt="@item.Anchor">
            </a>
        </div>
        <div class="store_name text-center">
            <h5>
                <a href="@Discount.Web.URLPaths.category@item.Slug" title="@item.Anchor">
                    @item.Name
                </a>
            </h5>
        </div>
    </div>
</cache>

[sebastienros]

You are executing the query on every request. Try to move the query in the cached section of your view, or use IMemoryCache to store the results.

Explanation:

• Whenever your action is executed, it will go though this code: View(_categoryRepository.Read()) which queries the database and provides the result to your view. So even if fragments of your view are cached, you will always hit the database.
• What you should do is use IMemoryCache to store the database results. I found an article with a similar scenario: https://wildermuth.com/2016/04/14/Using-Cache-in-ASP-NET-Core-1-0-RC1

[cihadsevim]

I'm sorry but I didn't understand a single bit. Do you mind showing some code example or little more explanation?

Thanks.

[sebastienros]

I updated my answer in the meantime.

[cihadsevim]

Thanks.

I thought tag works like OutputCache. Sadly it doesn't. And it seems there isn't a solution like OutputCache on Asp.net Core for now.

[sebastienros]

There is an output cache middleware, it's called ResponseCache. And it's supported, that will also work in your case https://docs.microsoft.com/en-us/aspnet/core/performance/caching/response

[cihadsevim]

I tried ResponseCache, didn't work. Tried it again. Still doesn't work. I used SQL Profiler to watch sql queries unfortunately it's keep executing select query.