چرا Dynamic Query ؟ چرا IsNull نه ؟!

[Felony]

بارها شده تو این بخش و سایت های دیگه و انجمن های خیلی مطرح غیر ایرانی دیگه سوالاتی در مورد نحوه نوشتن جستجو روی چند فیلد ، آپدیت چند فیلد و ... دیدم که اصولا جواب های عجیب و غریبی براش داده میشه یا در آخر Dynamic Query به طرف پیشنهاد میشه ، خیلی وقت بود میخواستم یه مقاله کوتاه در این مورد بنویسم ولی متاسفانه مشغله کاری اجازه این کار رو نمیداد تا به امروز ...

کد زیر رو از یکی از تاپیک های انجمن برداشتم ، کد خوبی هست و کار راه انداز :

CREATE PROCEDURE SampleSP
@A nvarchar(10)= null
,@B nvarchar(10)= null
AS
BEGIN
DECLARE @Str nvarchar(1000)
Set @Str = N'Update TableName Set'
If @A IS Null
SET @Str = @Str + N' B=@B'
Else
SET @Str = @Str + N' A=@A'
Execute Sp_executeSQl @STR
END

Dynamic Query روش کار راه اندازی هست ولی زمان بر و کمی پر هزینه و گاهی بعضی از اصول و هدف های نوشتن Stored Procedure ها و ... رو زیر سوال میبره ، راه حل های دیگه ای هم که در این رابطه پیشنهاد میشه از پایه و اساس مشکل داره ، مثلا بعضی ها یک رشته رو به صورت Dynamic با استفاده از شرط های متعدد و ... تو برنامه تولید میکنن و بعد به موتور بانک اطلاعاتی پاس میدن یا ...

SQL Server تابعی داره با نام IsNull ، دقت کنید گفتم IsNull نه Is[blank]Null ، این تابع 2 پارامتر به عناون ورودی میگیره و بررسی میکنه پارامتر اول Null هست یا نه ، اگر Null بود پارامتر دوم رو به عنوان خروجی برمیگردونه ، اگر Null نبود همون پارامتر اول رو بر میگردونه .

خوب به نظرم همه چیز واضح و گویا هست ، ولی برای روشنتر شدن موضوع ؛

فرض کنید میخوایم SP بنویسم که نام ( FName ) و نام خانوادگی ( LName ) رو به وسیله پارامترهای ورودی ( FName@ , @LName ) به روز رسانی کنه ، حالا ممکنه کاربر فقط یکی از پارامتر ها رو مقدار دهی کنه در این صورت پیشنهاد اکثر دوستان Dynamic Query هست ولی من این روش رو ترجیح میدم :

CREATE PROCEDURE Test
@FName nvarchar(30) = null,
@LName nvarchar(30) = null
AS
BEGIN
UPDATE
[Test].[dbo].[Table_1]
SET
[FName] = ISNULL(@FName, FName)
,[LName] = ISNULL(@LName, LName);
END

حالا وقتي موقع اجرا SP فقط يکي از پارامترها رو مقدار دهي کنيد ( مثلا FName@ رو با مقدار 'Hassan' ) ، تابع IsNull بررسي ميکنه ميبينه پارامتر FName@ مقدار داره پس خودش رو برميگردونه ( 'Hassan' ) ، حالا تابع IsNull بررسي ميکنه ميبينه پارامتر LName@ پوچ هست ( Null ) ، پس مقدار خود فيلد LName در رکورد جاري رو برميگردونه و اين فيلد رکورد جاری با مقدار قبلي خودش مقداري دهي ميشه و در آخر نتيجه چيزي هست که ما نياز داشتيم .

حالا فرض کنیم میخوایم یک SP برای جست و جوی یک فرد بر اساس " نام یا نام خانوادگی یا شماره تلفن " بنویسیم دقت کنید که گفتن " یا " یعنی هر کدوم از این پارامترها میتونه مقدار داشته باشه یا میتونه پوچ باشه ، باز اکثر دوستان Dynamic Query رو پیشنهاد میدن ولی من باز روش خودم رو ترجیح میدم :


CREATE PROCEDURE Test
@FirstName nvarchar(30) = null,
@LastName nvarchar(30) = null,
@Telephone nchar(30) = null
AS
BEGIN
SELECT
FName,
LName,
Age,
Mobile,
Tel,
[Address]
FROM Table_1
WHERE
(FName LIKE ISNULL(@FirstName, FName) + '%')
AND
(LName LIKE ISNULL(@LastName, LName) + '%')
AND
(Tel LIKE ISNULL(@Telephone, Tel) + '%');
END

خوب ؟ نظرتون چیه ؟ تو کد بالا خیلی راحت برای جست و جو هر فیلد یک تابع IsNull گذاشته شده که پارامتر ورودی مربوط به جستجو اون فیلد رو با مقدار خود اون فیلد در رکورد جاری بررسی میکنه ، اگر پارامتر ورودی null بود مقدار همون فیلد رکورد جاری رو میزاره تو جست و جو یعنی اگر پارامتر FName@ ما null بود و مقدار فیلد FName رکورد جاری که Cursor روش قرار داره 'Ali' بود شرط ما میشه 'Ali'='Ali' بنابراین جست و جو درست انجام میشه و مشکلی به وجود نمیاد .

الان خیلی راحت میتونید فقط به پامتر FName@ و Tel@ مقدار بدید و پارامتر LName@ رو خالی رها کنید ، بدون هیچ دردسر و Dynamic Query جست و جو درست انجام میشه .

حالا با توجه به 2 مثال بالا میشه موارد بسیار پیچیده تر رو هم خیلی راحت در مدت زمان بسیار بسیار کمتر از Dynamic Query پیاده سازی کرد .

:لبخندساده: روز خوش .

[یوسف زالی]

حاج مجتبی عزیز،
روشی که بکار می بری خیلی هم خوبه.
در تکمیل فرمایشات شما باید بگم که در حالت مقایسه (و نه لایک) من این روش رو ترجیح می دم.
دلایل زیاده اما بهترینش درگیر نشدن با نال ، این موجود حساس هست.


select *
from TBL
where @Filter1 in (YourField, '') or @Filter2 in (YourIntField, 0))


به نظرم سریع تر میاد. برای من هم قابل فهم تره.
البته خودت استادی. اینم روشیه دیگه.

[ASKaffash]

سلام
اصولا این روش در کل درست نیست بعنوان مثال فرض کنید که شما برای Update فقط فیلد را مورد تغییر قرار میدهید ویکی قرار است در این شرایط صوری باشد یعنی در دستور ذیل متغیر @LName مقدار null دارد :


UPDATE Table_1 SET FName = ISNULL(@FName, FName),LName = ISNULL(@LName, LName);

حال در این کد سه اشتباه وجود دارد :
- برای بانک اطلاعاتی یک عمل اضافی و سربار در حجم زیاد دارد
- برای فیلد دوم که از نظر محتوای داده ای هیج تغییری نکرده Log برداری می شود
- اگر یک تریگر روی جدول وجود داشته باشد که قرار است تغییرات را متوجه خواهد شود توابع UpDate و Columns_UpDate دچار اشتباه خواهند شد

موضوع دوم :
در عبارت ذیل بخش زمانبر بودن کجاست ؟
Dynamic Query روش کار راه اندازی هست ولی زمان بر و کمی پر هزینه

[ASKaffash]

select *
from TBL
where @Filter1 in (YourField, '') or @Filter2 in (YourIntField, 0))


سلام
این روش در حجم داده های زیاد خیلی اشتباه است چون اگر ایندکس روی فیلد YourIntField وجود داشته باشد تکلیف Optimizer چیست ؟

[یوسف زالی]

جناب کفاش روش شما چیه؟
در حالتی که ایندکس روی YourIntField هست؟
یا حتی برای روش آقا مجتبی؟

[ASKaffash]

سلام
درست است که DynamicSQL در ابتدا نیاز به یک زمان اولیه جهت Compile دارد ولی این زمان کمتر از یک ثانیه است بنابراین برای موارد حجم زیاد همان DynamicSQL مناسب تراست ولی در موارد عادی کلا فرقی نمی کند من این موضوع را چند سال پیش در یک حجم متوسط از نزدیک دیدم و واقعا DynamicSQL معجزه کرد (در آن سال CPU Usage شده بود 100% و کاربران دیگر درشبکه هنگ میکردند)

[simple]

سلام
به نظرم میشه با کمی تغییرات بهترین پاسخ رو گرفت. من کدهای دوستان رو بررسی کردم و کد زیر رو پیشنهاد می دهم:



CREATE PROCEDURE Test
@Name nvarchar(50)=null,
@Family nvarchar(70)=null
AS
SET NOCOUNT ON;
SELECT InfoID,Name, Family
FROM Data Table
WHERE (Name like '%'+ COALESCE (@Name, Name)+'%')
AND (Family like '%'+ COALESCE (@Family, Family)+'%')

[majjjj]

سلام
به نظرم میشه با کمی تغییرات بهترین پاسخ رو گرفت. من کدهای دوستان رو بررسی کردم و کد زیر رو پیشنهاد می دهم:



CREATE PROCEDURE Test
@Name nvarchar(50)=null,
@Family nvarchar(70)=null
AS
SET NOCOUNT ON;
SELECT InfoID,Name, Family
FROM Data Table
WHERE (Name like '%'+ COALESCE (@Name, Name)+'%')
AND (Family like '%'+ COALESCE (@Family, Family)+'%')


دوست عزیز جناب کفاش پاسخ خوبی قبلا دادن وقتی شما like میکنی ایندکس دیگه کار نمیکنه و عمل اسکن روی جدول انجام میشه که باعث کاهش performance خواهد شد.

[simple]

استفاده از like ضرورتی نداره. مهم استفاده از تابع COALESCE به جای توابع ISNULL و IS NULL.

منتظر بررسی دوستان هستم.