‫یافتن شناسه‌های ناموجود (Non-Existing IDs) در SQL Server

چکیده: در مهندسی سامانه‌های توزیع‌شده و پردازش دسته‌ای تراکنش‌ها، سنجش هم‌زمانی یک مجموعه از شناسه‌های ورودی با داده‌های پایگاه داده و استخراج موارد ناموجود، نیازمندی شایعی در عملیات اعتبارسنجی (Validation) و همگام‌سازی (Reconciliation) است. پیاده‌سازی غیراصولی این فرایند با چالش‌هایی نظیر تله‌های منطقی سه ارزشی SQL (Three-Valued Logic)، سربار حافظه در لایه داده و افت شاخص‌های زمان پاسخ‌گویی همراه خواهد بود. این مقاله راهکارهای استاندارد مبتنی بر T-SQL را برای استخراج شناسه‌های ناموجود تحلیل کرده، مزیت‌های الگوی NOT EXISTS و LEFT JOIN ... WHERE NULL را در مقایسه با مخاطرات NOT IN شرح می‌دهد و معماری تبادل مجموعه‌ای داده‌ها را از طریق پارامترهای جدول‌مقدار (Table-Valued Parameters - TVP) با بهره‌گیری از ADO.NET و Entity Framework Core تبیین می‌نماید. در نهایت، راهکار جایگزین سمت کلاینت در EF Core ارزیابی شده و ماتریس جامعی از تصمیم‌گیری فنی بر مبنای حجم بار کاری ارائه می‌گردد.

مجموعه شناسه‌های ورودی (Literal/TVP)
                 [ 101, 102, 103, X999 ]
                           │
       ┌───────────────────┴───────────────────┐
       ▼                                       ▼
روش ۱: LEFT JOIN ... WHERE NULL        روش ۲: NOT EXISTS
  (ایجاد Join فیزیکی کامل                 (ارزیابی به شیوه Short-Circuit؛
   و فیلتر سطرهای NULL)                   به محض یافتن اولین تطابق متوقف می‌شود)
       │                                       │
       └───────────────────┬───────────────────┘
                           ▼
             شناسه‌های ناموجود در جدول مقصد
                       [ X999 ]

WITH IncomingList(ID) AS (
    SELECT * FROM (VALUES (101), (102), (103), (104), (105)) AS V(ID)
)
SELECT l.ID
FROM IncomingList l
LEFT JOIN dbo.TargetTable t ON l.ID = t.ID
WHERE t.ID IS NULL;

SELECT l.ID
FROM (VALUES (101), (102), (103), (104), (105)) AS l(ID)
WHERE NOT EXISTS (
    SELECT 1 
    FROM dbo.TargetTable t 
    WHERE t.ID = l.ID
);

• منطق سه ارزشی (Three-Valued Logic): در استاندارد ANSI SQL، مقایسه هر مقداری با NULL حاصل UNKNOWN می‌دهد. اگر ستون هدف حتی شامل یک رکورد با مقدار NULL باشد، گزاره NOT IN به UNKNOWN ارزیابی شده و کوئری کل خروجی را مسدود می‌کند؛ در نتیجه تعداد ۰ رکورد بازمی‌گردد.
• بنابراین، حفظ قطعیت رفتار کوئری مستلزم پایبندی به الگوهای NOT EXISTS یا LEFT JOIN ... WHERE NULL است.

CREATE TYPE dbo.StringIdList AS TABLE (
    StringID NVARCHAR(100) NOT NULL PRIMARY KEY CLUSTERED
);
GO

نکته بهینه‌سازی پیشرفته: تعریف PRIMARY KEY CLUSTERED روی نوع داده جدولی، ایندکس منحصر‌به‌فرد روی شناسه‌های ورودی ایجاد می‌کند. این کار سربار مرتب‌سازی داده‌ها را در هنگام اجرای Hash Join یا Merge Join به حداقل می‌رساند.

CREATE PROCEDURE dbo.GetNonExistingStringIDs
    @IncomingList dbo.StringIdList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT l.StringID
    FROM @IncomingList l
    WHERE NOT EXISTS (
        SELECT 1 
        FROM dbo.TargetTable t 
        WHERE t.Code = l.StringID
    );
END;
GO

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;

public class SqlRepository
{
    private readonly string _connectionString;

    public SqlRepository(string connectionString) => _connectionString = connectionString;

    public List<string> FetchMissingIds(IReadOnlyCollection<string> candidateIds)
    {
        var result = new List<string>();

        // نگاشت لیست ورودی به DataTable هم‌ساختار با نوع داده در SQL
        var idTable = new DataTable();
        idTable.Columns.Add("StringID", typeof(string));

        foreach (var id in candidateIds)
        {
            idTable.Rows.Add(id);
        }

        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand("dbo.GetNonExistingStringIDs", connection)
        {
            CommandType = CommandType.StoredProcedure
        };

        // تنظیم نوع ساختاریافته (Structured) برای TVP
        var tvpParam = command.Parameters.AddWithValue("@IncomingList", idTable);
        tvpParam.SqlDbType = SqlDbType.Structured;
        tvpParam.TypeName = "dbo.StringIdList";

        connection.Open();
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            result.Add(reader.GetString(0));
        }

        return result;
    }
}

نکته کارایی دات‌نت: برای مجموعه‌داده‌های چند هزارتایی، کلاس DataTable سربار حافظه بالایی به بار می‌آورد. دات‌نت مدرن امکان پیاده‌سازی رابط IEnumerable را فراهم می‌سازد که داده‌ها را بدون تخصیص حافظه در قالب شیء واسط، به موتور پایگاه داده استریم می‌‌نماید.

public class NonExistingIdResult
{
    public string StringID { get; set; } = null!;
}

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

    public DbSet<NonExistingIdResult> NonExistingIdResults => Set<NonExistingIdResult>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // معرفی مدل به عنوان نهاد بدون کلید اصلی
        modelBuilder.Entity<NonExistingIdResult>().HasNoKey();
    }
}

public async Task<List<string>> GetMissingIdsWithTvpAsync(
    ApplicationDbContext context, 
    List<string> candidateIds, 
    CancellationToken cancellationToken = default)
{
    var idTable = new DataTable();
    idTable.Columns.Add("StringID", typeof(string));
    foreach (var id in candidateIds)
    {
        idTable.Rows.Add(id);
    }

    var tvpParam = new SqlParameter("@IncomingList", idTable)
    {
        SqlDbType = SqlDbType.Structured,
        TypeName = "dbo.StringIdList"
    };

    return await context.NonExistingIdResults
        .FromSqlRaw("EXEC dbo.GetNonExistingStringIDs @IncomingList", tvpParam)
        .Select(r => r.StringID)
        .ToListAsync(cancellationToken);
}

public async Task<List<string>> GetMissingIdsPureLinqAsync(
    ApplicationDbContext context, 
    List<string> candidateIds, 
    CancellationToken cancellationToken = default)
{
    // دریافت شناسه‌های موجود در پایگاه داده
    var existingIds = await context.TargetEntities
        .Where(t => candidateIds.Contains(t.Code))
        .Select(t => t.Code)
        .ToListAsync(cancellationToken);

    // محاسبه تفاضل مجموعه‌ها در حافظه سرور برنامه
    return candidateIds.Except(existingIds).ToList();
}

• محدودیت پارامتر در SQL Server: این موتور حداکثر ۲,۱۰۰ پارامتر را در یک فراخوانی می‌پذیرد. اگر طول لیست ورودی از این آستانه فراتر رود، خطای استثنایی رخ خواهد داد.
• تخریب کش پلن (Plan Cache Fragmentation): تغییر مکرر تعداد عناصر در لیست ورودی باعث تولید ساختارهای SQL با تعداد پارامترهای گوناگون شده و سیستم کش برنامه را تحت فشار قرار می‌دهد. از این رو، این روش صرفاً برای لیست‌های با اندازه کوچک و مشخص توصیه می‌شود.