C
C#•2y ago
Zil

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
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.
Zil
ZilOP•2y ago
hmm i see so how could i combine data for a query ?
Murphy
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.

Did you find this page helpful?