Optimize query. The foreach is too slow

Asked

Viewed 170 times

1

I have this query

[Route("")]
    [HttpGet]
    [ResponseType(typeof(List<MarkupListResponse>))]
    public IHttpActionResult Get(int resellerId)
    {
        var catalogs = _catalogService.GetAllByResellerId(resellerId);
        var model = new List<MarkupListResponse>();

        foreach (var catalog in catalogs)
        {
            model.Add(new MarkupListResponse()
            {
                CreatedOn = catalog.CatalogDate,
                CatalogId = catalog.Id,
                ItemsQuantity = catalog.Items.Count
            });
        }

        return Ok(model);
    }

In Catalogs I have 419 records(Catalogs) each catalog, can have from 1 to 100 products. When I enter the foreach, it gets too slow and each iteration, it mounts a select similar to that

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[ResellerCatalogId] AS [ResellerCatalogId], 
    [Extent1].[ProductId] AS [ProductId], 
    [Extent1].[MarkupPercentual] AS [MarkupPercentual], 
    [Extent1].[MarkupFixed] AS [MarkupFixed], 
    [Extent1].[PriceSale] AS [PriceSale], 
    [Extent1].[EffectivePrice] AS [EffectivePrice]
    FROM [dbo].[ResellerCatalogItem] AS [Extent1]
    WHERE [Extent1].[ResellerCatalogId] = @EntityKeyValue1

Where the parameter @EntityKeyValue1 is the catalog ID(419 in all). The question is to improve it all and I have already made some attempts, such as lambda instead of foreach and yet it didn’t. I wonder if there’s any way to improve the performance of this.

Doing so has improved a lot, but I do not know if there is another more performative way

var qry = catalogs.Select(x => new MarkupListResponse
            {
                CreatedOn = x.CatalogDate,
                CatalogId = x.Id,
                ItemsQuantity = x.Items.Count
            });

            model.AddRange(qry);
c# entity-framework

  • When does Savechanges occur?

  • If you can post your models.

  • Are you sure the slowness is in the foreach? I don’t understand why it assembles a select at each iteration, if the result of the query already came in the service call.

  • If it is slow does SQL not hand, runs in a View with FromSQL and be happy, or else make a Linq objective more or less like this _catalogService.Select(x => new MarkupListResponse(){ CreatedOn = x.CatalogDate, CatalogId = x.Id, ItemsQuantity = x.Items.Count()}).ToList()

  • Do a test removing the line catalog.Items.Count and let me know if there’s been a lot of difference

  • @Ronaldoaraújoalves, I commented on Count and gave the same result in time. I made an edit, put in the new code and got a more performative result. I believe that when it is published, without the influence of Debug, I believe that it will improve even more. This site, has many bottleneck points and I can’t change much, like create proc as suggested by someone, this can’t be done.

  • When you ask about a problem in your code, you’ll get better answers if you give people code that they can use to reproduce the problem. See how to create a minimum, complete and verifiable example to use in your question.

Show 2 more comments

1 answer

1

Apparently when you use x.Items.Count the Lazy Loading make a complete query (SELECT * FROM ...) and then use Linq for the counting of records, so the slowness.

An alternative is to use a proc to get the count, it would be more efficient. Or else use the IQueryable, that puts the "LIN" commands in the query that will be sent in the SQL command instead of obtaining the query’s return and then filtering.

Example:

The command below: 

IQueryable<SalesPerson> top10SalesByBonusQueryable = 
   context.SalesPersons.OrderByDescending(p => p.Bonus).Take(10);

is equivalent to this query:

SELECT TOP (10)
...
FROM [Sales].[SalesPerson] AS [Extent1]
ORDER BY [Extent1].[Bonus] DESC


Below is a useful link:

Ienumerable VS Iqueryable

Browser other questions tagged c# entity-framework

You are not signed in. Login or sign up in order to post.