راهنمای جامع SQL Server Cursor کاربرد و کارایی

کارایی و کنترل داده در SQL Server: راهنمای جامع استفاده از Cursor

در سیستم‌های مدیریت پایگاه داده، اغلب نیاز به پردازش داده‌ها به صورت سطر به سطر داریم، به جای عملیات گروهی یا مبتنی بر مجموعه. در چنین سناریوهایی، Cursor (کورسر یا مکان‌نما) در SQL Server ابزاری قدرتمند برای پیمایش و دستکاری داده‌ها به صورت تکی است. این قابلیت به شما امکان می‌دهد تا یک مجموعه نتیجه را تکرار کنید و روی هر سطر عملیات خاصی انجام دهید، درست مانند یک حلقه (loop) در زبان‌های برنامه‌نویسی. با این حال، استفاده از کورسرها نیاز به درک دقیق دارد زیرا می‌توانند بر کارایی (performance) سیستم تأثیر بگذارند. دلیل این امر آن است که کورسرها منابع سیستم را مصرف می‌کنند و عملیات سطر به سطر معمولاً کندتر از عملیات مبتنی بر مجموعه است. آن‌ها ممکن است باعث افزایش قفل‌گذاری (locking) شوند که به نوبه خود می‌تواند همروندی (concurrency) را کاهش دهد و گلوگاه‌هایی را در پایگاه داده ایجاد کند. بنابراین، قبل از استفاده از کورسر، همیشه به دنبال جایگزین‌های مبتنی بر مجموعه باشید که معمولاً کارآمدتر هستند. کورسرها باید آخرین گزینه در نظر گرفته شوند، زمانی که راه حل مبتنی بر مجموعه امکان‌پذیر یا ساده نباشد. بیایید نگاهی به یک مثال عملی از نحوه استفاده از کورسر در SQL Server بیاندازیم. این مثال به شما کمک می‌کند تا مراحل اساسی را برای تعریف، باز کردن، پیمایش، بستن و آزادسازی یک کورسر درک کنید. فرض کنید جدولی به نام `Customers` داریم و می‌خواهیم روی هر سطر آن عملیاتی انجام دهیم.

اولین گام، تعریف کورسر است. در این مرحله، شما نام کورسر و کوئری SELECT آن را مشخص می‌کنید که مجموعه داده‌ای را که کورسر روی آن عمل خواهد کرد، تعیین می‌کند. این کوئری نتایج را برای پردازش بعدی انتخاب می‌کند.

DECLARE @ID INT
DECLARE @Name VARCHAR(50)

DECLARE curSample CURSOR FOR SELECT ID, Name FROM Customers

پس از تعریف، باید کورسر را باز کنید. این کار مجموعه نتیجه را بر اساس کوئری SELECT که تعریف کرده‌اید، جمعیت می‌کند و اشاره‌گر کورسر را به اولین سطر مجموعه نتیجه منتقل می‌کند. این مرحله عملاً کورسر را برای استفاده آماده می‌کند.

OPEN curSample

حالا که کورسر باز شده است، می‌توانیم اولین سطر را واکشی کنیم. دستور FETCH NEXT سطر بعدی را از مجموعه نتیجه دریافت کرده و مقادیر ستون‌ها را به متغیرهایی که تعریف کرده‌اید، اختصاص می‌دهد. این کار اجازه می‌دهد تا داده‌ها به صورت فردی پردازش شوند.

FETCH NEXT FROM curSample INTO @ID, @Name

برای پیمایش در ردیف‌ها و پردازش هر کدام، از یک حلقه WHILE استفاده می‌کنیم که وضعیت عملیات FETCH را بررسی می‌کند. متغیر سیستمی @@FETCH_STATUS پس از هر FETCH به‌روز می‌شود:

@@FETCH_STATUS

این متغیر وضعیت آخرین عملیات FETCH را نشان می‌دهد. اگر مقدار آن ۰ باشد، یعنی FETCH موفقیت‌آمیز بوده و ردیف بعدی در دسترس است. اگر منفی ۲ باشد به معنی ردیف حذف شده است و اگر منفی ۱ باشد به معنی ردیف خارج از محدوده است.

WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- در اینجا عملیات مورد نظر خود را روی داده‌های واکشی شده انجام دهید
    PRINT 'ID: ' + CAST(@ID AS VARCHAR(10)) + ', Name: ' + @Name

    -- سطر بعدی را واکشی کنید
    FETCH NEXT FROM curSample INTO @ID, @Name
END

پس از اتمام پردازش همه سطرها، بسیار مهم است که کورسر را ببندید. این کار باعث آزاد شدن هرگونه قفل (lock) فعال روی ردیف‌هایی می‌شود که توسط کورسر نگه داشته شده بودند. بستن کورسر منابع را آزاد می‌کند اما ساختار کورسر هنوز در حافظه وجود دارد.

CLOSE curSample

در نهایت، برای آزاد کردن کامل منابع تخصیص یافته به کورسر از حافظه، از دستور DEALLOCATE استفاده کنید. این گام نهایی برای تمیز کردن و اطمینان از عدم مصرف غیرضروری منابع است.

DEALLOCATE curSample

یکی دیگر از متغیرهای سیستمی مهم که می‌توانید با کورسرها استفاده کنید، @@CURSOR_ROWS است. این متغیر تعداد ردیف‌های فعلی را در آخرین کورسر باز شده نشان می‌دهد:

@@CURSOR_ROWS

این متغیر اطلاعاتی در مورد اندازه مجموعه نتیجه کورسر ارائه می‌دهد و می‌تواند برای بررسی و اشکال‌زدایی مفید باشد.

انواع مختلفی از کورسرها در SQL Server وجود دارند که هر کدام ویژگی‌ها و موارد استفاده خاص خود را دارند: * **FORWARD_ONLY:** این کورسر فقط به سمت جلو حرکت می‌کند و نمی‌تواند به عقب برگردد. برای دستیابی به عملکرد بهتر، این نوع کورسر اغلب بهینه‌سازی شده است. * **SCROLL:** کورسری که امکان حرکت به جلو و عقب را فراهم می‌کند. این نوع کورسر می‌تواند روی داده‌های اصلی تغییرات را نشان دهد. * **STATIC:** مجموعه‌ای از داده‌ها را در زمان باز شدن کورسر ایجاد می‌کند. تغییرات در داده‌های اصلی پس از باز شدن کورسر در این کورسر منعکس نمی‌شوند. * **KEYSET:** مجموعه‌ای از کلیدها را در زمان باز شدن کورسر ایجاد می‌کند. تغییرات در مقادیر غیر کلیدی و حذف ردیف‌ها را نشان می‌دهد، اما اضافه شدن ردیف‌های جدید را نشان نمی‌دهد. * **DYNAMIC:** این نوع کورسر همیشه مجموعه نتیجه فعلی را نشان می‌دهد و هرگونه تغییر در داده‌های اصلی (اضافه، حذف، به‌روزرسانی) را در زمان واقعی منعکس می‌کند. * **FAST_FORWARD:** یک نوع FORWARD_ONLY و READ_ONLY است که برای بهینه‌سازی عملکرد طراحی شده است. این نوع نمی‌تواند داده‌ها را به‌روزرسانی کند. * **LOCAL:** کورسری است که فقط در محدوده Stored Procedure یا Batch که در آن تعریف شده، قابل دسترسی است. * **GLOBAL:** کورسری که در سطح نمونه SQL Server تعریف می‌شود و می‌تواند در هر Stored Procedure یا Batch دیگری استفاده شود. در پایان، در حالی که کورسرها برای سناریوهای خاصی که نیاز به پردازش سطر به سطر دارند مفید هستند، همیشه باید مزایا و معایب آن‌ها را در نظر گرفت. در بسیاری از موارد، استفاده از عملیات مبتنی بر مجموعه (SET-based operations) با استفاده از دستورات UPDATE، DELETE، INSERT، یا MERGE که برای کار با گروه‌های بزرگ داده بهینه شده‌اند، راه‌حل کارآمدتری برای مدیریت و دستکاری داده‌ها در SQL Server خواهد بود. انتخاب صحیح بین کورسر و عملیات مبتنی بر مجموعه برای دستیابی به حداکثر کارایی و مقیاس‌پذیری پایگاه داده شما حیاتی است.