CODEWITHSUNDEEP
c#dapper
Marcin Knapik
Marcin Knapik

Reputation: 35

Is there any way not to make so many queries to the database?

In c# I have a list of art_id, for example 10000 elements.

And I make something like that:

for(int i = 0; list.Count; i++)
{
  list[i].someValue = SQL.QueryOne("select ... where p.art_id = list[i].art_id);
}

And it's so slow, is any way to do it better? AND WORKING FASTER. My project using Dapper, and the database is Oracle 19c.

Upvotes: 0

Views: 64

Answers (1)

Neil
Neil

Reputation: 11889

You can send a list of ids in one query, using IN

var ids = list.select(x=>x.someValue);

"select p.art_id, ... where p.art_id IN (@ids)"

that should return the ids found along with their related data, and then you can enumerate that into list[i].someValue.

Amongst the caveats would be that for large numbers of ids, they should be split into a small number of queries (e.g. perhaps one query per 1000 ids). Measure what that number should be.

Upvotes: 2

Related Questions