در مطالب قبلی وب سایت شرکت مهندسی شبکه پال نت در خصوص Set Based Thinking صحبت نمودیم. در این مطلب قصد دارم مقداری وارد مسائل حقیقی در سرور SQL که با آن سرو کار داریم بشویم.

مسائل حقیقی Set Based در SQL Server

خوب یک از این مسائل را مطرح می کنیم و شما راه حل هایتان را با آنچه بیان می گردد مقایسه کنید.

متاسفانه در دانشگاهها و موسسات آموزشی اصلا به این موارد نمی پردازند. بعضی از اساتید دانشگاهها متاسفانه اصلا از دانش خوبی در حوزه مسائل حقیقی Set Based در سرور SQL برخوردار نیستند و اگر همین مسائل را بپرسید به سختی میتوانند به شما پاسخ بدهند. همین مسائلی که در اینجا مطرح می گردد از اساتیدی که میشناسید بپرسید و پاسخهای آنها را با جوابهایی که اینجا گفته می شود قیاس نمایید تا خودتان به عمق فاجعه پی ببرید.

یکی از مسائلی که در نرم افزارها با آن مواجه هستیم بحث پیدا کردن شماره های خالی یا Gap بین شماره اسناد یا شماره نامه ها هست.

فرض کنید داده هایی با ID  های 1 الی 66 را در اختیار دارید.

ما میخواهیم شماره اسنادی را بدست بیاریم که داخل این لیست نیستند.

که جواب باید به شرح زیر باشد:
4,7,9,11..14,16..20,22..29,31..44,51..54,59,63,64,65

چگونه شماره های خالی یا Gap بین شماره اسناد پیدا کنیم؟

البته این مساله خیلی ساده بود و اصلا قسمتهای پیچیده آن را که سبب نوشتن کدهای سنگین شود مطرح نکردیم. فقط صرفا هدف آشنایی با مفاهیم Set Based در سرور SQL هست و اینکه مفهوم کدهارو متوجه شویم.

تابع زیر برای تولید اعداد هست که تا یک بازه بزرگی رو تولید می کنه.

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum;
GO

ما سه تا کد مختلف نوشتیم که هر کدامش را توضیح میدهیم.

کد اول:

Select n as ID from Getnums(1,66) as n
where not exists(Select * from test as d where n.n=d.id)

در این کد ما با استفاده از تابع فوق اعداد 1 تا 66 رو تولید کردم و بایک Not exists داریم چک میکنیم که رکورد وجود دارد یا نه. به این طریق نوشتن Select در Where Clause که سبب ارتباط با Select بالایی هست Correlated subquery می گویند.

این کد را با not in هم می توانید بنویسید ولی performance ضعیفتری نسبت به این کد دارد. در اینجا اعداد 1 تا 66 ایجاد شده و براساس شرط داخلی سلکت در Where چک می شود که آیا رکورد فوق True هست یا False . به همین خاطر از سرعت پردازش بالایی برخوردار است.

روش دوم:

Select n as ID from Getnums(1,66) as n
except
Select Id from test

در این روش یک مرحله اضافه وجود دارد و این هم بحث Sort و Distinct هست که خود عملیات Except سبب آن خواهد شد و باعث میشود نسبت به کد بالایی از Performance پایین تری برخوردار باشد.

روش سوم:

Select n as ID from Getnums(1,66) as n
Left outer Join test as d
on n.n=d.id
where d.id is null

این روش هم نسبت به روش اول تقریبا کندتر هست.

الان خیلی نمیخواهیم وارد جزئیات مسائل Performance بشویم.

باز هم تاکید میکنم مساله رو هم ساده انتخاب کردم هم روشهای ساده ای نوشتیم که با طرز تفکر Set Based در مسائل مختلف آشنا بشوید. جلوتر که برویم مسائل پیچیده تر و منطق های پیچیده تری را حتما بررسی می کنیم و هم اینکه نکات Performance را عرض می کنیم.

با روشهای Cursor Based هم بخواهید بنویسید میشه با یک حلقه چک کرد ببینیم آیا رکورد فوق موجود هست یاخیر که نسبت به این روشها کندتر خواهد شد.