Entity Framework with Web Api 2 - Performance issue

Question

First of all, I want to say that I’m a beginner in .NET and I'm a beginner in english. :(

I have the following code in my controller: The intention is to send the data set to the clients. I need to make that treatment to provide a suitable visualization in the front end application. However, I noticed that the code execution is very slow especially when the first conditional is executed. The "result" variable contains all my data and in fact, its size is small, around 40 records only (result.Count() is 40).

var response = new List<object>();
//the results are retrieved quickly from the database

var result = (from a in obj.TBRelPadraoCorEstado
              join b in obj.TBCorAlarme on a.idCor equals b.idCor
              join c in obj.TBEstadoAlarme on a.idEstado equals c.idEstado
              join d in obj.TBPadraoCor on a.idPadrao equals d.idPadraoCor
              select new
              {
                  idRelPadraoCor = a.idRelPadraoCor,
                  idPadrao = (int)d.idPadraoCor,
                  nomePadrao = d.nomePadrao,
                  idEstado = a.idEstado,
                  idCor = a.idCor,
                  hexCor = b.hexCor,
                  nomeCor = b.nomeCor,
                  estadoAlarme = c.estadoAlarme,
                  permiteReconhecimento = a.permiteReconhecimento,
                  isReconhecido = a.isReconhecido,
                  prioridade = a.prioridade
              }).OrderBy(d => d.idPadrao).AsEnumerable();

int size = result.Count();
List<object> buffer = new List<object>();

for (int i = 0; i < size - 1; i++)
{
    //at this point, the code execution become very slow.
    if ((int)result.ElementAt(i).idPadrao == (int)result.ElementAt(i + 1).idPadrao)
    {
        buffer.Add(new
        {
            idRelPadraoCor = result.ElementAt(i).idRelPadraoCor,
            idPadrao = result.ElementAt(i).idPadrao,
            idEstado = result.ElementAt(i).idEstado,
            idCor = result.ElementAt(i).idCor,
            hexCor = result.ElementAt(i).hexCor,
            nomeCor = result.ElementAt(i).nomeCor,
            estadoAlarme = result.ElementAt(i).estadoAlarme,
            permiteReconhecimento = result.ElementAt(i).permiteReconhecimento,
            isReconhecido = result.ElementAt(i).isReconhecido,
            prioridade = result.ElementAt(i).prioridade
        });
   }
    else
    {
        buffer.Add(new
        {
            idRelPadraoCor = result.ElementAt(i).idRelPadraoCor,
            idPadrao = result.ElementAt(i).idPadrao,
            idEstado = result.ElementAt(i).idEstado,
            idCor = result.ElementAt(i).idCor,
            hexCor = result.ElementAt(i).hexCor,
            nomeCor = result.ElementAt(i).nomeCor,
            estadoAlarme = result.ElementAt(i).estadoAlarme,
            permiteReconhecimento = result.ElementAt(i).permiteReconhecimento,
            isReconhecido = result.ElementAt(i).isReconhecido,
            prioridade = result.ElementAt(i).prioridade
        });
        var copyList = new List<object>(buffer);
        response.Add(new { nomePadrao = result.ElementAt(i).nomePadrao, idPadrao = result.ElementAt(i).idPadrao, stateSet = copyList });
        buffer = new List<object>();
    }
}

return Ok(response);

To get a sense of my problem: If I send only the “result” content, it takes somewhat 40 milliseconds. On the other hands, if I perform the snippet of the loop, it takes around 8000 or even 10000 milliseconds ![enter image description here ]1

Can someone tell me about what is happening?

Thanks for your patience.

You can use the indexing `list[int index]` operator instead of `ElementAt`, it will make your code easier to read. — Dai, Aug 24 '18 at 16:10
Your code calls `AsEnumerable()` but doesn't cache the results, so your query will be re-run multiple times. Use `ToList()` instead. — Dai, Aug 24 '18 at 16:11
Hello Dai. I can't do it because, my results are IEnumerable type, therefore I get: "cannot aplying indexing with [] to an expression of type System.Collections.Generic.IEnumerable". — Rogério Oliveira, Aug 24 '18 at 16:16
Please note that `result` variable does *not* contain the data, but the `query` to get that data when executed. And it's executed thousands of times (for each `ElementAt` call in your code). — Ivan Stoev, Aug 24 '18 at 16:16
Folks, my issue was solved! I followed Dai suggestion and the execution time falled drastically. This happened because I was using IEnumerable( ) instead of ToList( ). I will take a look at this article to get more Knowledge about it: https://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable Thank you very much. — Rogério Oliveira, Aug 24 '18 at 16:27
@RogérioOliveira, first of all, being a beginner in both .Net and English, you have done a good job. :). Now coming to the point, what’s the reasons you are using List of Objects everywhere? Is there any particular reason? Basically, when you use Object, the underlying types have to be converted both ways (when you are adding item to the List and when you are retrieving it from the List) and that kills the performance big time. Unless you have a specific need of using List of Objects, I would suggest you to use Type specific List to get full performance benefits of Generic Types. — dj079, Aug 26 '18 at 10:12
Hello guy! Thanks… I’m suffering a little with english and .NET, but I’ll get it :D Let’s go! This format is necessary to build a comfortable data visualization in the client app. My original data is raw, the result of an agregation (a sql view), therefore, there is a lot of duplicate values for the same columns Please, take a look: https://drive.google.com/open?id=1OfQV4BPaGtvpGgT8vcAfNWMpxS2CXT9L I hope wich you understood my idea. Thank you for your comment. — Rogério Oliveira, Aug 27 '18 at 15:21
I will have a look at it in sometime and see if I can be of any help. — dj079, Aug 28 '18 at 02:14

Entity Framework with Web Api 2 - Performance issue

0 Answers0