น่าจะเป็นเรื่องที่ปวดหัวของ Developer ที่ต้องมาทำงาน DBMS ตระกูล Microsoft SQL Server ครับ เวลาส่ง IList เข้าไป Where ผ่าน ADO.NET / Dapper / EF จะเจอปัญหานี้เหมือนกันครับ
The incoming request has too many parameters. The server supports a maximum of 2100 parameters
NOTE: สำหรับตัว EF ทางผมไม่ได้ใช้งานเป็นหลักนะครับ แต่ลอง Search มาเหมือนมีหลายความเห็นบอกว่าเจอเรื่องนี้ หรือไม่เจอครับ sql - Entity Framework Hitting 2100 Parameter Limit - Stack Overflow
2100 parameters มันของอะไรใน SQL Server
- หลายคนเคยแบบเวลาเจอ Error นี้ ลองเขียน SQL ตรงๆใน Clause WHERE IN เลย ปรากฏว่ามันำได้
- จริงๆแล้วข้อจำกัดตรงนี้มาจาก Stored Procedure ของ SQL Server ที่รองรับ Parameter ได้สูงสุด 2100 ตัวครับ ตามนี้ครับ Specify Parameters - SQL Server
- ที่นี้ตัว Library ที่ Implement อย่าง ADO.NET / Dapper มันใช้เทคนิค Parameterized Queries เบื่องหลังมันไปทำงานในลักษณะ Stored Procedure การเข้าไป Query ข้อมูลให้พวกเราเหมือนกันครับ
แก้ไขอย่างไร
- วิธีที่ 1 ไม่ต้องใช้แล้ว Parameterized Queries ต่อ String SQL เองเลย
- อย่าหาทำเด็ดขาด ถึงแม้ว่ามันจะ Query ได้จริงๆ แต่มันไม่ป้องกัน SQL Injection นะครับ ที่ย้ายมาใช้ Parameterized Queries มันช่วยกรองเรื่องนี้ด้วยระดับนึงครับ
- ตัว MS SQL SERVER เองก็มีข้อจำกัดด้วย แม้ว่าจะแก้ปัญหา 2100 แต่จะไปเจอปัญหาใหม่ตรงแถวๆ 32768 จะเจอ Error QUERY_EXPRESSION_TOO_COMPLEX (Msg 8632)
- วิธีที่ 2 ยอม Split ข้อมูล เพื่อมา Where IN แทน
- วิธีนี้จริงๆ ผมว่าดีนะ ถ้า Query มันซับซ้อน แล้วเราค่อยๆ ปรับ SQL ให้มันแบ่ง Query ที่ละชุด อย่างเช่น Split ที่ละ x ตัว แล้วส่งผลลัพธ์มาให้ App Server ประมวลผลต่อ
- ความยากของวิธันี้อยู่ที่ตัว x ถ้าตั้งน้อยไปจะมี Round Trip ในการดึงข้อมูลมากขึ้น แต่ถ้ามากไปอาจจะไปช้าที่ DB Server แทนได้
Sample ประมาณนี้มั่ง ไม่ได้เทส
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
public class DatabaseHelper
{
private readonly string _connectionString;
public DatabaseHelper(string connectionString)
{
_connectionString = connectionString;
}
public DataTable ExecuteQuery(string baseQuery, Dictionary<string, List<int>> parameters)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
StringBuilder queryBuilder = new StringBuilder(baseQuery);
List<SqlParameter> sqlParameters = new List<SqlParameter>();
foreach (var param in parameters)
{
List<string> parameterNames = new List<string>();
for (int i = 0; i < param.Value.Count; i++)
{
string paramName = $"@{param.Key}{i}";
parameterNames.Add(paramName);
sqlParameters.Add(new SqlParameter(paramName, param.Value[i]));
}
string inClause = string.Join(", ", parameterNames);
queryBuilder.Replace($"@{param.Key}", inClause);
}
string finalQuery = queryBuilder.ToString();
using (SqlCommand command = new SqlCommand(finalQuery, connection))
{
command.Parameters.AddRange(sqlParameters.ToArray());
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable resultTable = new DataTable();
adapter.Fill(resultTable);
return resultTable;
}
}
}
}
}
class Program
{
static void Main()
{
string connectionString = "YourConnectionStringHere";
DatabaseHelper dbHelper = new DatabaseHelper(connectionString);
string query = "SELECT * FROM STUDENT WHERE STUDENTID IN (@STUDENTID)";
Dictionary<string, List<int>> parameters = new Dictionary<string, List<int>>
{
{ "STUDENTID", new List<int> { 1, 2, 3, 4, 5, /* up to 2100 IDs */ } }
};
DataTable result = dbHelper.ExecuteQuery(query, parameters);
//Your Business Logic Here !!!
}
}
- วิธีที่ 3 ทำ Table Temp ขึ้นมา ยัด KEY และไป Where ต่อ
- ง่ายๆ ทำ Table แนวๆ Temp Lookup มาเลย มีอยู่ 1 Column
- เริ่มจากตอนแรก ยัด Key ลงไปก่อน
- ใน Query หลัก เปลี่ยน Query เป็น WHERE PERFIDX IN (SELECT IDX FROM TEMPLKP) แทนครับ ลองดู Query จะเข้าได้มากขึ้นครับ
-- แบบเดิม
SELECT * FROM Orders WHERE CustomerID IN (@p1, @p2, ..., @p2101)
-- แบบใหม่ subquery มาดึงจาก Table Temp DSCORE_TEMP_CRITERIA
SELECT * FROM Orders
WHERE CustomerID IN (SELECT DATAVALUE FROM DSCORE_TEMP_CRITERIA
WHERE UNIQUEID = @dataid AND DATAKEY = @datakey)
- เมื่อได้ผลลัพธ์แล้ว Clear Table Temp ออก
- ใช้ในเคสที่ Query เดิม เพราะ
- ยากซับซ้อนแบบว่าไม่มีเวลามาระเบิดมัน แต่ต้องเสียเวลามาคุม State ของ Table Temp Lookup
- ถ้าเป็น String ผมว่ามันมีแอบเสี่ยงโดน SQL INJECTION อยู่ดีครับ
- อาจจะเจอปัญหาเคส Concurrent ตรง Table Temp
ต่อไปตัวอย่าง psudo code SQL Server จริงอาจจะต้องปรับสำหรับ DB2 / Postgresql เน้น Logic นะ ใช้กับ ADO.NET
- Code ของ Table Temp
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
namespace dsCore.DAO.ADO.Helper
{
public static class TempTableHelper
{
// ===== Connection String =====
private static string _connectionString = "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASSWORD;TrustServerCertificate=True;";
public static void SetConnectionString(string connectionString)
{
_connectionString = connectionString;
}
// ===== Insert Overloads =====
public static string InsertIntValue(string pComputerName, string pTempTable, string pDataKey, IList<int> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (string.Empty, (int?)s, (int?)null, (int?)null, (int?)null)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<(int, int)> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (string.Empty, (int?)s.Item1, (int?)s.Item2, (int?)null, (int?)null)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<(int, int, int)> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (string.Empty, (int?)s.Item1, (int?)s.Item2, (int?)s.Item3, (int?)null)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<(int, int, int, int)> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (string.Empty, (int?)s.Item1, (int?)s.Item2, (int?)s.Item3, (int?)s.Item4)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<string> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (s, (int?)null, (int?)null, (int?)null, (int?)null)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<(string, int)> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (s.Item1, (int?)s.Item2, (int?)null, (int?)null, (int?)null)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<(string, int, int)> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (s.Item1, (int?)s.Item2, (int?)s.Item3, (int?)null, (int?)null)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<(string, int, int, int)> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (s.Item1, (int?)s.Item2, (int?)s.Item3, (int?)s.Item4, (int?)null)));
return uniqueId;
}
public static string InsertValue(string pComputerName, string pTempTable, string pDataKey, IList<(string, int, int, int, int)> pValue)
{
string uniqueId = Thread.CurrentThread.ManagedThreadId + "_" + pComputerName;
InsertValue(pTempTable, uniqueId, pDataKey, pValue.Select(s => (s.Item1, (int?)s.Item2, (int?)s.Item3, (int?)s.Item4, (int?)s.Item5)));
return uniqueId;
}
// ===== Constants =====
public const string TEMP_CRITERIA = "DSCORE_TEMP_CRITERIA";
public const string SQL_GET_INTDATA = "(SELECT CRITERIA.DATAVALUE FROM DSCORE_TEMP_CRITERIA CRITERIA WHERE CRITERIA.UNIQUEID = @dataid AND CRITERIA.DATAKEY = @datakey)";
public const string SQL_GET_INT1DATA = "(SELECT CRITERIA.DATAVALUE1 FROM DSCORE_TEMP_CRITERIA CRITERIA WHERE CRITERIA.UNIQUEID = @dataid AND CRITERIA.DATAKEY = @datakey)";
public const string SQL_GET_INT2DATA = "(SELECT CRITERIA.DATAVALUE2 FROM DSCORE_TEMP_CRITERIA CRITERIA WHERE CRITERIA.UNIQUEID = @dataid AND CRITERIA.DATAKEY = @datakey)";
public const string SQL_GET_INT3DATA = "(SELECT CRITERIA.DATAVALUE3 FROM DSCORE_TEMP_CRITERIA CRITERIA WHERE CRITERIA.UNIQUEID = @dataid AND CRITERIA.DATAKEY = @datakey)";
public const string SQL_GET_STRDATA = "(SELECT CRITERIA.DATAVALUESTR FROM DSCORE_TEMP_CRITERIA CRITERIA WHERE CRITERIA.UNIQUEID = @dataid AND CRITERIA.DATAKEY = @datakey)";
public const string UNIQUEID = "UniqueID";
public const string DATAKEY = "DataKey";
public const string CREATETIME = "CreateDate";
public const string DATAVALUESTR = "DataValueStr";
public const string DATAVALUE = "DataValue";
public const string DATAVALUE1 = "DataValue1";
public const string DATAVALUE2 = "DataValue2";
public const string DATAVALUE3 = "DataValue3";
// ===== Core Insert =====
private static void InsertValue(
string pTempTable,
string pUniqueId,
string pDataKey,
IEnumerable<(string, int?, int?, int?, int?)> pValues)
{
DateTime dt = DateTime.Now;
// Build DataTable
DataTable tbl = new DataTable();
tbl.Columns.Add(new DataColumn(UNIQUEID, typeof(string)));
tbl.Columns.Add(new DataColumn(DATAKEY, typeof(string)));
tbl.Columns.Add(new DataColumn(CREATETIME, typeof(DateTime)));
tbl.Columns.Add(new DataColumn(DATAVALUESTR, typeof(string)));
tbl.Columns.Add(new DataColumn(DATAVALUE, typeof(int)));
tbl.Columns.Add(new DataColumn(DATAVALUE1, typeof(int)));
tbl.Columns.Add(new DataColumn(DATAVALUE2, typeof(int)));
tbl.Columns.Add(new DataColumn(DATAVALUE3, typeof(int)));
foreach (var item in pValues)
{
DataRow dr = tbl.NewRow();
dr[UNIQUEID] = pUniqueId;
dr[DATAKEY] = pDataKey;
dr[CREATETIME] = dt;
dr[DATAVALUESTR] = !string.IsNullOrEmpty(item.Item1) ? (object)item.Item1 : DBNull.Value;
dr[DATAVALUE] = item.Item2.HasValue ? (object)item.Item2.Value : DBNull.Value;
dr[DATAVALUE1] = item.Item3.HasValue ? (object)item.Item3.Value : DBNull.Value;
dr[DATAVALUE2] = item.Item4.HasValue ? (object)item.Item4.Value : DBNull.Value;
dr[DATAVALUE3] = item.Item5.HasValue ? (object)item.Item5.Value : DBNull.Value;
tbl.Rows.Add(dr);
}
// BulkCopy ด้วย SqlConnection จริงๆ
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
{
bulk.DestinationTableName = pTempTable;
bulk.ColumnMappings.Add(UNIQUEID, UNIQUEID);
bulk.ColumnMappings.Add(DATAKEY, DATAKEY);
bulk.ColumnMappings.Add(CREATETIME, CREATETIME);
bulk.ColumnMappings.Add(DATAVALUESTR, DATAVALUESTR);
bulk.ColumnMappings.Add(DATAVALUE, DATAVALUE);
bulk.ColumnMappings.Add(DATAVALUE1, DATAVALUE1);
bulk.ColumnMappings.Add(DATAVALUE2, DATAVALUE2);
bulk.ColumnMappings.Add(DATAVALUE3, DATAVALUE3);
bulk.WriteToServer(tbl);
}
}
}
// ===== Delete =====
public static void DeleteValue(string pUniqueId, string pDataKey)
{
const string sql = "DELETE FROM DSCORE_TEMP_CRITERIA WHERE UNIQUEID = @UniqueID AND DATAKEY = @DataKey";
using (SqlConnection conn = new SqlConnection(_connectionString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@UniqueID", pUniqueId);
cmd.Parameters.AddWithValue("@DataKey", pDataKey);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public static void DeleteAll(string pUniqueId)
{
const string sql = "DELETE FROM DSCORE_TEMP_CRITERIA WHERE UNIQUEID = @UniqueID";
using (SqlConnection conn = new SqlConnection(_connectionString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@UniqueID", pUniqueId);
conn.Open();
cmd.ExecuteNonQuery();
}
}
// ===== Cleanup เก่า (Table จะได้ไม่บวม) =====
public static void DeleteExpired(int olderThanMinutes = 60)
{
const string sql = "DELETE FROM DSCORE_TEMP_CRITERIA WHERE CreateDate < @Cutoff";
using (SqlConnection conn = new SqlConnection(_connectionString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@Cutoff", DateTime.Now.AddMinutes(-olderThanMinutes));
conn.Open();
cmd.ExecuteNonQuery();
}
}
// ===== Helper =====
public static string GenerateDataKey(string pName)
{
return Guid.NewGuid().ToString() + "-" + pName;
}
}
}
- Code ตอน Run
string dataKey = TempTableHelper.GenerateDataKey("CustomerFilter");
string uniqueId = TempTableHelper.InsertIntValue(computerName, TempTableHelper.TEMP_CRITERIA, dataKey, customerIds);
string sql = $@"
SELECT * FROM Orders
WHERE CustomerID IN {TempTableHelper.SQL_GET_INTDATA}";
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@dataid", uniqueId);
cmd.Parameters.AddWithValue("@datakey", dataKey);
conn.Open();
// execute...
}
- วิธีที่ 4 ถ้าใช้ Stored Procedure ใช้ MSSQL Table-Valued parameters สิ
- Table-Valued parameters ของ SQL SERVER มาแก้ปัญหาเรื่องนี้แหละครับ
- แต่ถ้าทำไปแล้ว พวกที่ระบบต่อกับ DBMS หลายๆค่ายได้ อาจจะต้องมาประเมิน ว่ามันคุ้มไหมที่จะต้องแยก Class ใน Level ของ ADO ในแต่ละ DBMS
- หรือจะ Apply Table-Valued parameters กับเคสอื่นๆดูครับ
สรุป
- ทีมเลือกวิธีที่ 2 เพราะปัญหาที่เจอส่วนใหญ่จะเป็นประเภท WHERE IN มากกว่าที่จะไปเจอปัญหาของ Stored Procedure ถ้าไปใช้ Table-Valued parameters มันระบบอาจจะยีดติดกับกับ DBMS ค่ายใดค่ายหนึ่งมากไปครับ และแก้ได้เร็วสำหรับเคสไฟไหมต้นช่วงต้นปี 2021
- ส่วนวิธีที่ 3 มันต้องมาจัดการเคสซวยเพื่อด้วยมี 2 request มาใช้ SQL ชุดนี้พร้อมกัน อันนี้ถ้าจัดการได้ แล้วทำเป็น Framework จะดีมากครับ
- และนอกจาก C# แล้ว เหมือนภาษาอื่นๆ จะเจอกัน สามารถนำเอาแนวทางไปประยุกต์ใช้ได้เช่นกันครับ
อ๋อพวก EF เหมือนมีคน Apply ประมาณนี้มั้ง https://www.tabsoverspaces.com/233644-playing-with-parameters-limit-on-sql-server-with-entity-framework
Reference
- Specify Parameters - SQL Server | Microsoft Docs
- Stored Procedure vs Parameterized queries – SQLServerCentral Forums
- Table-Valued Parameters - ADO.NET | Microsoft Docs
- linq - Hitting the 2100 parameter limit (SQL Server) when using Contains() - Stack Overflow
- t sql - Is there any limit for IN results in SQL Server? - Database Administrators Stack Exchange
- c# - How to pass an array into a SQL Server stored procedure - Stack Overflow
- sql - Entity Framework Hitting 2100 Parameter Limit - Stack Overflow
Discover more from naiwaen@DebuggingSoft
Subscribe to get the latest posts sent to your email.
