نوشتن کوئری طوری که از Daynamic Query استفاده نکنیم

[Salah Sanjabian]

سلام خدمت همه . میخواستم ببینم تو این کوئری آیا میشه جوری نوشتش که دیگه از Dynamic Query استفاده نکر؟.یه پارامتر ورودی داریم که اگه کاربر اونو تعیین کرد باید کالاهای اون گروه رو بیاره در عیر این صورت کل کالا ها رو بیاره

CREATE Procedure [dbo].[ReportStock]
@Sub int =null
as
BEGIN
declare @Sql nvarchar (max)
if @Sub=null
set @Sub=-1
set @Sql='
select P.ID, P.Name,Units.Name as Unit,P.Value ,P.LastPurchasePrice, P.Value * P.LastPurchasePrice as SumPurchase ,P.PettySalePrice , P.Value * P.PettySalePrice as SumSale
from Products as P inner join ProductUnits on P.ID=ProductUnits.ProductRef
inner join Units on ProductUnits.UnitRef=Units.ID
where Units.Ratio=
(select MIN(ratio) from ProductUnits inner join Units
on ProductUnits.Unitref=Units.id where ProductUnits.ProductRef=P.ID
group by ProductUnits.ProductRef )
'
if @Sub<>-1
set @Sql=@Sql+ ' and P.SubRef= ' + CAST( @Sub as nvarchar(15))
exec (@Sql)
END

[Felony]

از تابع ISNull استفاده کنید ، مثلا در کد زیر اگر هر کدوم از پارامترهای @FirstName یا @LastName مقداری داشته باشه بر اساس اون فیلتر اعمال میشه و اگر مقداری نداشته باشه تمام رکوردها سلکت میشه:

SELECT
*
FROM Patients
WHERE
FName LIKE ISNULL(@FirstName, FName)
AND
LName LIKE ISNULL(@LastName, LName)

[Salah Sanjabian]

ممنون از پاسختون
کدتون یه چیزی شبیه کد زیر درسته؟
select * from Patients where FName=FName
جالب بود کدتون . تاحالا یه فیلد رو با خودش مقایسه نکرده بودم:چشمک:
ولی خوب یه جاش واسه من گنگه اگه پارامترمون NULL باشه که فیلد رو با خودش مقایسه میکنه و همه رکورد ها رو منطقا باید برگردونه ولی اگه NULL نباشه چطور مقدار فیلد رو با پارامتر مقایسه میکنه؟ چون ISNULL همونطور که میدونید میگه اگه NULL بود با این جایگزین کن ولی فک کنم دیگه Else نداره
Dynamic Query رو پیشنهاد میدین یا این روشی که ذکر کردین؟

[Felony]

ولی خوب یه جاش واسه من گنگه اگه پارامترمون NULL باشه که فیلد رو با خودش مقایسه میکنه و همه رکورد ها رو منطقا باید برگردونه ولی اگه NULL نباشه چطور مقدار فیلد رو با پارامتر مقایسه میکنه؟
پارامتر اول در تابع expression مقایسه هست ، طبق گفته خود مایکروسافت :


The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.


Dynamic Query رو پیشنهاد میدین یا این روشی که ذکر کردین؟
شک نکن که این روش ؛ با Dynamic Query اون Stored Procedure ی که میسازی فقط به درد لایه بندی برنامه میخوره نه Performance چون عملا داری یک کوئری رو میسازی و بهش میدی تا اجرا کنه ولی با کد بالا خیلی راحت SQL Server یک Plan از SP بعد از اجرای اول میسازه و در اجراهای بعدی فقط پارامتر رو جایگزین میکنه .

موفق باشید .

[ASKaffash]

سلام
یک سربار اضافه برای کنترل شرط وجود دارد where FName=FName بنظرم یا با Dynamic یا به شکل طولانی مثل ذیل :


CREATE Procedure [dbo].[ReportStock]
@Sub int =null
as
BEGIN
declare @Sql nvarchar (max)
if @Sub=null
select P.ID,P.Name,Units.Name as Unit,P.Value ,P.LastPurchasePrice,
P.Value * P.LastPurchasePrice as SumPurchase ,P.PettySalePrice ,
P.Value * P.PettySalePrice as SumSale
from Products as P
inner join ProductUnits on P.ID=ProductUnits.ProductRefinner
join Units on ProductUnits.UnitRef=Units.ID
where Units.Ratio=(select MIN(ratio)
from ProductUnits
inner join Units on ProductUnits.Unitref=Units.id
where ProductUnits.ProductRef=P.ID
group by ProductUnits.ProductRef )
else
select P.ID,P.Name,Units.Name as Unit,P.Value ,P.LastPurchasePrice,
P.Value * P.LastPurchasePrice as SumPurchase ,P.PettySalePrice ,
P.Value * P.PettySalePrice as SumSale
from Products as P
inner join ProductUnits on P.ID=ProductUnits.ProductRefinner
join Units on ProductUnits.UnitRef=Units.ID
where P.SubRef=@Sub and
Units.Ratio=(select MIN(ratio)
from ProductUnits
inner join Units on ProductUnits.Unitref=Units.id
where ProductUnits.ProductRef=P.ID
group by ProductUnits.ProductRef )
exec (@Sql)
END

در حالت دوم اگر روی P.SubRef ایندکس باشد بسیار مناسب و برای حالت اول فقط یک شرط مورد پردازش قرار می گیرد

[Salah Sanjabian]

سلام
یک سربار اضافه برای کنترل شرط وجود دارد where FName=FName
[/SIZE]
اگه رکورد ها زیاد بشه و به میلیون برسه فکرکنم اینجا هم Performance میاد پایین.
به نظرم از شرطی که گفتین استفاده کنم بهتره

[ASKaffash]

سلام
چرا ؟ فقط یکبار if اجرا می شود و ربطی به تعداد رکورد ندارد ولی در where FName=FName برای یک میلیون اجرا می شود

[Salah Sanjabian]

منظور من هم شرط Where بود که برای همه رکورد ها اجرا میشه.
واقعا نباید از Dyanamic Query ها استفاده کرد ؟تا چه حد Performance رو میاره پایین؟آیا کسی با رکوردهای بالا تست کرده؟>
من تو چند پروسیجر استفاده کردم چاره ای نداشتم یا باید پروسیجرها رو جدا میکردم یا از دینامیک کوئری استفاده کنم و نرم افزاررو هم مشتری درآینده ازش استفاده میکنه

[Felony]

ما از همون روشی که گفتم تو برنامه HIS ( مدیریت بیمارستان ) استفاده کردیم ، با دیتابیس هایی بالای چند میلیون رکورد و چند صد هزار تراکنش در روز و هیچ مشکلی هم نداشتیم ( البته بانک هایی که ازشون صحبت میکنم خیلی بهینه و با دقت طراحی شدن )

[ASKaffash]

سلام
دینامیک سرعت شما را پائین نمی آورد من روی بالای 35 میلیون رکورد تست کردم


منظور من هم شرط Where بود که برای همه رکورد ها اجرا میشه.
واقعا نباید از Dyanamic Query ها استفاده کرد ؟تا چه حد Performance رو میاره پایین؟آیا کسی با رکوردهای بالا تست کرده؟>
من تو چند پروسیجر استفاده کردم چاره ای نداشتم یا باید پروسیجرها رو جدا میکردم یا از دینامیک کوئری استفاده کنم و نرم افزاررو هم مشتری درآینده ازش استفاده میکنه

[pashna]

سلام،
اینطور که من میبینم SP شما فقط ۱ پارامتر ورودی داره، در اینصورت چه ضرورتی داره از دینامیک استفاده کنید؟ یا نال هست یا نیست دیگه، چه کاری که از دینامیک استفاده کنید؟
معمولاً از کؤری دینامیک وقتی‌ استفاده می‌کنن که چند پارامتر ورودی دارن که همشون هم ممکن نال باشه یا نباشه.
ضمنا از اونجا ا‌ی‌ که Query Planدینامیک Cacheنمی‌شه قطعاً Performanceتو affect می‌کنه

[Salah Sanjabian]

فقط بحث پروسیجر یه پارامتری نبود .