[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 แทนได้

- วิธีที่ 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# แล้ว เหมือนภาษาอื่นๆ จะเจอกัน สามารถนำเอาแนวทางไปประยุกต์ใช้ได้เช่นกันครับ

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