how to decrease the response time of a Web API?

Question

I’m a beginner in C# and I’m developing a CRUD using Angular and Web Api (Entity Framework Core), but the response time to list my Serverscontroller data in my table is very high. What happens nowadays is that it takes a long time to list only 48 records (more than 5 minutes or sometimes not even loads), talking to a colleague I was proposed to do the consultation in the direct bank in my Get method and consume the query by right there, the big question is: how can I consume this query? because the connection/instance of the database I was able to do. Follow the current code below:

  [HttpGet]
  public async Task<ActionResult<IEnumerable<Server>>> GetServer()
  {
     using (var context = new ITControlContext())
     {
        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = "SELECT e.Name [Environment], t.Name [ServerType], p.Name [Plant] FROM [ITControl].[dbo].[Server] s INNER JOIN [ITControl].[dbo].[Environment] e on s.IdEnvironment = e.IdEnvironment INNER JOIN [ITControl].[dbo].[ServerType] t on s.IdServerType = t.IdServerType INNER JOIN [ITControl].[dbo].[Plant] p on s.IdPlant = p.IdPlant";
            context.Database.OpenConnection();
            System.Data.DataTable dataTable = new System.Data.DataTable();
            dataTable.Load(command.ExecuteReader());
        }
     }

       return await _context.Server.Include(i => i.IdEnvironmentNavigation).Include(i => i.IdServerTypeNavigation).Include(i => i.IdPlantNavigation).ToListAsync();
  }

What I tried to do was create a "new instance" of my database and do the query there (I don’t know much what I did) and I would like to return the data of this query to be able to list them in my table without taking so long. Does anyone have any idea what I can do or give me any North of what to research/study?

Ps: I imagine I have to change something in mine return

Answer 1

Well, after a lot of research and tests (with a few hints of frustration) I managed to solve the problem :D I will post the solution below and explain how I got to this result.

Solution:

[HttpGet]
public async Task<ActionResult<IEnumerable<Server>>> GetServer()
{
    using (var context = new ITControlContext())
    {
        System.Data.DataTable dataTable = new System.Data.DataTable();

        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = "SELECT s.IdServer, s.IP, s.Name, s.Description, e.Name [Environment], t.Name [ServerType], p.Name [Plant], s.CreatedAt, s.UserInserted, s.UpdatedAt, s.UserUpdated FROM [ITControl].[dbo].[Server] s INNER JOIN[ITControl].[dbo].[Environment] e on s.IdEnvironment = e.IdEnvironment INNER JOIN[ITControl].[dbo].[ServerType] t on s.IdServerType = t.IdServerType INNER JOIN[ITControl].[dbo].[Plant] p on s.IdPlant = p.IdPlant";
            context.Database.OpenConnection();
            dataTable.Load(command.ExecuteReader());
        }
        
        return Ok(dataTable);
    }          
}

Basically, what I did was recover my connection to the bank inside the [HttpGet] using the following block:

using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "SELECT s.IdServer, s.IP, s.Name, s.Description, e.Name [Environment], t.Name [ServerType], p.Name [Plant], s.CreatedAt, s.UserInserted, s.UpdatedAt, s.UserUpdated FROM [ITControl].[dbo].[Server] s INNER JOIN[ITControl].[dbo].[Environment] e on s.IdEnvironment = e.IdEnvironment INNER JOIN[ITControl].[dbo].[ServerType] t on s.IdServerType = t.IdServerType INNER JOIN[ITControl].[dbo].[Plant] p on s.IdPlant = p.IdPlant";
    context.Database.OpenConnection();
    dataTable.Load(command.ExecuteReader());
}

but instead of returning the datatable that was loading this query, I was still returning the old command (through Includes, thus making the implementation of this connection with the datatable "useless" since I was not consuming it). Another mistake is also that I was calling the System.Data.DataTable dataTable = new System.Data.DataTable(); inside the connection block itself, not being able to return it. So I needed to first leave this instance of datatable outside the connection block and then return inside mine return passing him on to the method Ok(), getting Ok(dataTable) and voila, it worked.

I even separated a comparative image with the response time with the before and after, see below:

It was possible to get out of 34.4 seconds (that didn’t even list everything I needed, because if I put it all in it would not even carry see) and I was able to go to 583ms listing all the necessary data.