Is there a way to make this query faster and build where the sentence is outside the loop?

Question

I have the following code (and I am fully aware of parameterized queries and SQL Injection):

foreach(var item in items) { string query = "select sum(convert(decimal(18,3),tbl.Price)) p, sum(convert(decimal(18,2),tbl.Sale)) s from table1 tbl " + $"where tbl.ID = {item .ID}"; Execute(query); //Do stuff with query result } 

The problem is that I have many elements, and I have to execute a query for each of the elements, because the where clause will be complete at every step. I think that if I can make my request a side of my cycle, my request will be faster. But I do not know how to do this. Is there any way to do this?

Answer 1

Instead of executing a query for each item. You can add group by to your request and execute it only once.

 string query = "select tbl.ID, sum(convert(decimal(18,3),tbl.Price)) p, sum(convert(decimal(18,2),tbl.Sale)) s from table1 tbl group by tbl.ID "; var result = Execute(query); foreach(var item in items) { var row = result.Select(r => r.ID == item.ID).FirstOrDefault(); //Do stuff with query result } 

Answer 2

Do not execute the query for each identifier separately. Instead, run one query for all identifiers, using a group to get the p and s values ​​for each id and parameterized in clause (or, even better, a stored procedure with a table parameter).

Here is the version of the in request:

 select Id, sum(convert(decimal(18,3),tbl.Price)) p, sum(convert(decimal(18,2),tbl.Sale)) s from table1 tbl Where Id IN(<1,2,3,4....>) group by Id 

Replace <1,2,3,4....> parameters described in this answer.

The following is a table of query parameter values:

 select tbl.Id, sum(convert(decimal(18,3),tbl.Price)) p, sum(convert(decimal(18,2),tbl.Sale)) s from table1 tbl inner join @items i on tbl.Id = i.Id group by tbl.Id 

For a detailed explanation of using table parameters, read this answer.