In some cases, like when customers use Object-relational Mapping tools (ORM) Like Entity framework or LINQ, part of the code at the end will be converted to a TSQL executable statement.
For example, The LINQ with .Where () method will be a TSQL Query with a Where clause :
Query Syntax and Method Syntax in LINQ (C#) | Microsoft Learn
Write LINQ queries in C# | Microsoft Learn
This can cause issues like creating an IN clause that explicitly includes a large number of values (maybe a hundred or more of values separated by commas), which means more consume of resources and an Index\table scan or return errors like 8623 or 8632
For more information: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver16#...
Now, I will give an example using AdventureWorks database, the table SalesOrderDetail:
1- I will create an Index:
Creating an index :
create index ix_SalesOrderDetail_productidx on [SalesLT].[SalesOrderDetail](productid) include([UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid])
2- If I run the following Query:
SELECT [ProductID] , [UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] FROM [SalesLT].[SalesOrderDetail] where productid =708
The query's execution is optimal, and the execution plan shows the use of Index seek:
3- The following Select is using an IN predicate with a list of 100 values for the Product IDs:
SELECT [ProductID] , [UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] FROM [SalesLT].[SalesOrderDetail] where productid in ( 708, 711, 712, 714, 715, 716, 717, 718, 722, 738, 739, 742, 743, 747, 748, 779, 780, 781, 782, 783, 784, 792, 793, 794, 795, 796, 797, 798, 799, 800, 801, 808, 809, 810, 813, 822, 835, 836, 838, 858, 859, 860, 864, 865, 867, 868, 869, 870, 873, 874, 875, 876, 877, 880, 881, 883, 884, 885, 886, 889, 891, 892, 893, 894, 895, 896, 899, 900, 904, 905, 907, 908, 909, 910, 913, 916, 917, 918, 920, 924, 925, 926, 935, 936, 937, 938, 939, 940, 944, 945, 947, 948, 949, 951, 952, 953, 954, 955, 956, 957)
The execution plan is showing an Index Scan instead of Seek:
4- But, if the list has less than that number, for example 25 values as below example, the optimizer will choose an index seek:
SELECT [ProductID] , [UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] FROM [SalesLT].[SalesOrderDetail] where productid in ( 779, 780, 781, 782, 783, 784, 792, 793, 794, 795, 796, 797, 798, 799, 800, 801, 944, 945, 947, 948, 949, 951, 952, 953, 954, 955)
Solution:
- change the IN values list to a SELECT subquery within an IN clause, like the following:
SELECT [ProductID] , [UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] FROM [SalesLT].[SalesOrderDetail] S where productid in ( SELECT * FROM (VALUES (707), (708), (711), (712), (714), (715), (716), (717), (718), (722), (738), (739), (742), (743), (747), (748), (779), (780), (781), (782), (783), (784), (792), (793), (794), (795), (796), (797), (798), (799), (800), (801), (808), (809), (810), (813), (822), (835), (836), (838), (858), (859), (860), (864), (865), (867), (868), (869), (870), (873), (874), (875), (876), (877), (880), (881), (883), (884), (885), (886), (889), (891), (892), (893), (894), (895), (896), (899), (900), (904), (905), (907), (908), (909), (910), (913), (916), (917), (918), (920), (924), (925), (926), (935), (936), (937), (938), (939), (940), (944), (945), (947), (948), (949), (951), (952), (953), (954), (955), (956), (957)) mylist (productid) );
Or
SELECT [ProductID] , [UnitPrice] ,[UnitPriceDiscount] ,[LineTotal] ,[rowguid] FROM [SalesLT].[SalesOrderDetail] S where EXISTS ( SELECT 1 FROM (VALUES (707), (708), (711), (712), (714), (715), (716), (717), (718), (722), (738), (739), (742), (743), (747), (748), (779), (780), (781), (782), (783), (784), (792), (793), (794), (795), (796), (797), (798), (799), (800), (801), (808), (809), (810), (813), (822), (835), (836), (838), (858), (859), (860), (864), (865), (867), (868), (869), (870), (873),
(874), (875), (876), (877), (880), (881), (883), (884), (885), (886), (889), (891), (892), (893), (894), (895), (896), (899), (900), (904), (905), (907), (908), (909), (910), (913), (916), (917), (918), (920), (924), (925), (926), (935), (936), (937), (938), (939), (940), (944), (945), (947), (948), (949), (951), (952), (953), (954), (955), (956), (957)) mylist (productid) WHERE S.productid = mylist.productid );
- Use table hints (like forceseek hint):
..... ,[rowguid] FROM [SalesLT].[SalesOrderDetail] with(forceseek) where productid in ( 708, 711, ........
- Change the application Script to use a stored procedure, create a stored procedure and call it from your application code, like Entity framework or Linq.