How the rewrite this IN condition logic to handle in best performance for dotnet core webapi?

Elaya Raja 96

How the below logic handle in best way for dotnet core webapi?

//Query is like * where JoinDateTime>@startDate AND JoinDateTime<@endDate AND EmpId IN (@EmpId )

var result = await _database.QueryAsync<Employee>(query,
new
{
startDate,
endDate,
EmpId = listOfEmployeeID.ToArray() // Empid is integer and always passing more than 1000+
});

How to split this where IN condition into easy way to avoid the performance issue.?

Thanks
Jo

1 answer

Bruce (SqlWork.com) 55,601 Reputation points

2022-06-06T20:11:41.787+00:00

if you are using sql parameters then each in item is a separate parameter:

in (@EmpId1, @EmpId2)

typically you build the in clause with a loop, and add the parameters to the command

with EF linq, you can use the contains and pass an array

EmpId.Contains(listOfEmployeeID)
Please sign in to rate this answer.
Elaya Raja 96 Reputation points

2022-06-07T03:41:52.81+00:00

Thanks for the reply Bruce. Actually my question is In where condition IN it will be huge no also possible. for that time it will take more time to execute the Query which will give the performance issue. To avoid that what way can handle this IN condition when lot of values comes.

Jack J Jun 24,286 Reputation points Microsoft Vendor

2022-06-07T09:20:18.863+00:00

@Elaya Raja , If you indeed are worry about the performance issue when you meet a lot of values, Have you considered using Raw SQL Queries? It may be faster than linq query.

Bruce (SqlWork.com) 55,601 Reputation points

2022-06-09T22:43:52.593+00:00

sqlserver has no optimizations for in (...). if the list is large, then it can be better to load the in clause values in a table, and join to the table..

if the list is over 1000 items, then you should use a temp table and build an index, otherwise you can just pass a table variable.

be sure the column using the in clause is indexed. if not its a full scan, and no optimization required.
Sign in to comment