C#•2y ago

Looping a query bad practice? Good alternative?

This is my end-point atm:

c++
[HttpPost]
public async Task<ActionResult<List<Step>>> PostNewStepInfo(List<Step> steps)
{
    using var connection = new SqlConnection(_config.GetConnectionString("Default"));

    // loop?

    return Ok(steps);
}

c++
[HttpPost]
public async Task<ActionResult<List<Step>>> PostNewStepInfo(List<Step> steps)
{
    using var connection = new SqlConnection(_config.GetConnectionString("Default"));

    // loop?

    return Ok(steps);
}

The data it receives: (In this case there is only 2, but this can be big, can also reach like 100 objects with each multiple parameters.)

[
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  },
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  }
]

[
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  },
  {
    name: 'fortable1',
    parameters: [
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      },
      {
        something: 'fortable2',
        somethingElse: 'fortable2'
      }
    ]
  }
]

Now ofcourse I could just loop over each object and then also loop over the parameters and execute a query for each item to the table i want. This feels like bad practice and would love to hear your opinion on it and maybe a good alternative. Thanks in advance!

3 Replies

Murphy•2y ago

Doing a bunch of small queries can be slower than doing a single query where you could extract your needed data from one result. With small, simple queries, the overhead is generally from sending the query to the server and receiving the result rather than the actual processing of the query so it's usually better to combine what you can and send only one query and receive only one result. It's hard to say without more info because it really depends on the query and your design goals.

ZilOP•2y ago

hmm i see so how could i combine data for a query ?

Murphy•2y ago

you could iterate through your list and building a single query up from that and then running that one query. like instead of running multiple queries of

select * from table where id = 1

select * from table where id = 1

and

select * from table where id = 2

select * from table where id = 2

... you could use a build up a query like

select * from table where id in (1,2,...)

select * from table where id in (1,2,...)

and run that.

Gaming

Programming

Looping a query bad practice? Good alternative?