[C#] แนวทางแก้ปัญหา The incoming request has too many parameters. The server supports a maximum of 2100 parameters

น่าจะเป็นเรื่องที่ปวดหัวของ 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)
  • Ref: t sql - Is there any limit for IN results in SQL Server? - Database Administrators Stack Exchange

- วิธีที่ 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) แทนครับ
• เมื่อได้ผลลัพธ์แล้ว Clear Table Temp ออก
• ใช้ในเคสที่ Query เดิม เพราะ
  • ยากซับซ้อนแบบว่าไม่มีเวลามาระเบิดมัน แต่ต้องเสียเวลามาคุม State ของ Table Temp Lookup
  • ถ้าเป็น String ผมว่ามันมีแอบเสี่ยงโดน SQL INJECTION อยู่ดีครับ
  • อาจจะเจอปัญหาเคส Concurrent ตรง Table Temp

- วิธีที่ 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 ค่ายใดค่ายหนึ่งมากไปครับ
• ส่วนวิธีที่ 3 มีใช้งานบ้าง แต่น้อยมากครับ มันต้องมาจัดการเคสซวยเพื่อด้วยมี 2 request มาใช้ SQL ชุดนี้พร้อมกัน ..
• และนอกจาก 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