مقایسه inline value function و Multi statement function|کندو

صورت مسئله: یک جدول بزرگ داریم که نمیتوان با توجه به کویری ها ارسالی فیلدی پیدا کرد که با پارتیشن کردن دیتا بر اساس آن موجب ارتقا پرفورمنس شویم، پس دیتای جدول را به دو جدول Current و Archive تقسیم میکنیم، و حالا می خواهیم از سه ابزار مورد اشاره استاده کنیم تا برسیم به یک ساختار یکپارچه طوری که با موجب ارتقاء پرفورمنس چشم گیری باشیم. پس یک union داریم در زمان استفاده از هر سه ساختار، که شرط Date به عنوان seperator در select جداول آن استفاده شده است، (نکته مهم: اگر این شرط را نذاریم، ولو اینکه مطمئنی شرط برقرار است، داستان متفاوت است، و همواره هر دو جدول Current و Archive مورد پردازش SQL قرار میگیرد)، Query مورد نظر داخل ساختار مشابه Query زیر میشود.

SELECT * FROM dbo.tb WHERE date >=’2016-10-27 00:00:08.000′

UNION

SELECT * FROM dbo.ar WHERE date <‘2016-10-27 00:00:08.000’

البته برای MultiStatement می‌توان از دستور if هم در بادی استفاده کرد، که مورد بررسی قرار خواهد گرفت.

تا اینجای کار با احتساب هارد‌ کد وارد کردن Date جداکننده با فرض داشتن سه بازه داریم:

بازه‌ها: بازه خارج seperator، حالا بعد یا قبل آن- بازه وسط seperator

هردو view و Inline Value Function یک مدل برای سه بازه عمل کرده اند.

خارج:

مقایسه view و Inline Value Function و Multi Statement Function 1

داخل:

مقایسه view و Inline Value Function و Multi Statement Function 3

Multi Statement Function در دو حالت بررسی شد:
- مشابه View و inline فقط با تاریخ بصورت هارد کد
- استفاده از پارامتر ورودی و گذاشتن if

مقایسه view و Inline Value Function و Multi Statement Function 9

نتیجه: درهردو حالت بالا، دو مشکل اساسی دارد:

1.ابتدا فانکشن را محاسبه وسپس مورد پردازش Query ما قرار میدهد، یعنی اکزکیوشن پلن جدا و بعد در قالب یک جدول اقدام میکند.

2.همه حالات را پلن میسازد، ولو اینکه در داخل if قرار نگیرد.

بررسی function و View
برای شروع View بهتر از function است، چرا که برنامه نویس های ما بلدن با View کار کنند، پس اگر هردو به لحاظ پرفورمنس یکی شوند، View انتخاب خواهد شد.
برای مشخص کردن Date جدا کننده، بجای هارد کد، یا باید view را در هربار آرشیو Alter کنیم، یا برای فانکشن مقدار ورودی داخل function پاس دهیم (پس برای فانکشن پیشنهاد می‌شود در کد برنامه از یکجا مرز جداکننده را پیدا کنند مثلا top بگیریم یا تو یک جدول نگه داریم تا کجا آرشیو شده است، در غیر اینصورت و محاسبه و بدست آوردن این تایم قبل سرچ خودش یک هزینه میباشد، پس بهتر است اگر روال پیشنهادی صورت نمی‌گیرد هارد کد زده شود، یا اینکه مثلا اول فرم مربوطه مقدار کشف شود، موقع جستجو از آن استفاده شود).
ظاهرا هر دو View و inline Function به لحاظ پرفرومنس مشابه هم رفتار میکنند اما بحث ورود اطلاعات به عنوان جدا کننده بازه اطلاعات مطرح است، بدین ترتیب که function مزیت گرفتن تاریخ را دارد،ولی View اگر قرار باشد تاریخ بگیرد، باید Alter شود.
برای بحث وارد کردن سپریتور تاریخ:

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

2.می توان برای view زمان آرشیو اطلاعات،ویو را Alter کرد، و همانجا ان تاریخ را وارد کرد و View را اصلاح نمود(بررسی:آیا Alter تاثیر منفی ندارد؟ میشه به جای هر روز هر ماه یا حتی هر سال آرشیو کنیم)جواب: Alter View منتظر می ماند تا کویری روش تمام شود، اگر وسط پروسه آرشیو هم کویری بگیریم،چون مرزها اشتباه هست،ممکن است غیرقابل پیش بینی رفتار کند.

3.میشود هردوی Function و View از روش 2 پیش رود و اصلا Function پارامتر ورودی نداشته باشد،پس طبق فرضیه اول، کار کردن با View برای دولوپر راحت تر بوده پس اگر به این بند برسیم View بهر است.

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

5.باید بررسی کنیم اگر بازه را به عنوان ورودی پاس دهیم آیا فانکش بهتر عمل میکند: در بررسی به عمل آمده،هیچ تفاوتی ایجاد نشد، و پرفورمنس مشابه View می باشد

تست های نهایی و مشابه پروداکشن

ترکیب با بقیه کویری ها و نتیجه

کویری Count : در این کویری زمانی که بازه ها خارج مرز جداکننده هست، شدیدا وضعیت بهتر است، اما در باز تداخل با مرز جدا کننده مطابق انتطار، کمی افزاریش زمان داریم،اما در مقایسه حالت اول(بازه خارج سپریتور) بیش از 4 برابر سریعتر شدیم،در حالت دوم 25 درصد تایم بیشتر شد.اما در مقایسه سه مدل فانکشن، فانکش سه پارامتره عملکردش فرق داشت و هزینه بیشتری به دلیل یک مرج اضافه داشت (سپریتور و بازه به عنوان پارامتز ورودی)

کویری بدون Count: در هرسه حالت Function و همچنین View شرایط یکسان هست. برای بازه های خارج سپریتور نیز همه حالات فانکشن و ویو،یکجور بودند.

مقایسه view با اصلی

درنهایت استفاده از ساختارهای پیشنهادی نسبت به جدول یکپارچه در بازه های خارج سپریتور شدیدا بهتر و در بازه شامل سپریتور2 درصد پرهزینه تر بود که معاد 25 صدم درصد طولانی تر بودن،که هیچی نیست.

نتیجه نهایی،مقایسه:

Multi Statement Table Value Function که هیچ، مشکل پرفورمنسی داشت و به درد ما نخورد و منظور از فانکشن در تحلیل ها، inline Table Value است.
View و Function با احتساب همه مدل فانکشنی،با ورودی یا بدون ورودی، به لحاظ پرفورمنس یکسان هست،و Query داخل آن را بسته شروط تغییر داده و به بیرون می فرستد و در جوین های دیگر مجدد Execution Plane می سازد،البته یک کیس خاص داشتیم که در کویری Count ، فانکشن با ورودی بازه ، یک Merge اضافه داشت و درصدی کند تر شد
View را باید در هربار بعد از اتمام فرآیند آرشیو اطلاعات Alter کنیم، که Alter گرچه هزینه ندارد،اما منتظر میشود کسی روی View نباشد،که عموما شب بوده و لودی نداریم
Function میتواند شرط تاریخ جداکننده را به عنوان ورودی بگیرد و نیاز به آلتر نباشد،به شرط آنکه نکته بالا در کد در نظر گرفته شود.
درنهایت استفاده از ساختارهای پیشنهادی نسبت به جدول یکپارچه در بازه های خارج سپریتور شدیدا بهتر و در بازه شامل سپریتور2 درصد پرهزینه تر بود که معاد 25 صدم درصد طولانی تر بودن،که هیچی نیست.

تفاوت زاتی view و inline table value Function و multi statement function

View:کویری داخل View با کویری های اصلی ترکیب و مجدد execution Plane ساخته میشود

Inline Table: همانند View ، کویری داخل View با کویری های اصلی ترکیب و مجدد execution Plane ساخته میشود

Multi Statement: تمامی کویری ها داخل ان جداگانه پردازش شده،حتی اگر If داشته باشیم که صادق نباشد،داخل If را مورد پردازش قرارداده، نتیجه را محاسبه و در قالب تیبل وریبل به بیرون پاس کرد و لذا Execution Plane جدیدی در صورت جویبن و … با دیگر جداول برای آن ساخته میشود.

View در مقابل Inline Table، گرچه در سطح Execution Plane یکسان است، اما به منظور اجرای این دو،در View باید به تمامی آبجکت های مورد استفاده در آن پرمِیژن داد(شماهای متفاوت) ولی فاکنشن همین که یوزر پرمیژن اجرای آن را داشته باشد کافی هست.پس می تواند به منظور Column Base Permission مورد استفاده قرار گیرد

کویری های تست:

SELECT * FROM dbo.Test_VwCalls WHERE CallDate BETWEEN ‘2020-10-01 00:00:00.000’ AND ‘2020-10-05 00:00:00.000’ –Feat

SELECT * FROM dbo.Test_VwCalls WHERE CallDate BETWEEN ‘2016-10-25 00:00:00.000’ AND ‘2016-10-30 00:00:00.000’ –Midel

SELECT * FROM dbo.Test_VwCalls WHERE CallDate BETWEEN ‘2010-10-01 00:00:00.000’ AND ‘2010-10-30 00:00:00.000’ –Last

SELECT * FROM dbo.Test_FNCalls() WHERE CallDate BETWEEN ‘2020-10-01 00:00:00.000’ AND ‘2020-10-05 00:00:00.000’ –Feat

SELECT * FROM dbo.Test_FNCalls() WHERE CallDate BETWEEN ‘2016-10-25 00:00:00.000’ AND ‘2016-10-30 00:00:00.000’ –Midel

SELECT * FROM dbo.Test_FNCalls() WHERE CallDate BETWEEN ‘2010-10-01 00:00:00.000’ AND ‘2010-10-30 00:00:00.000’ –Last

SELECT * FROM dbo.Test_FNmCalls() WHERE CallDate BETWEEN ‘2020-10-01 00:00:00.000’ AND ‘2020-10-05 00:00:00.000’ –Feat

SELECT * FROM dbo.Test_FNmCalls() WHERE CallDate BETWEEN ‘2016-10-25 00:00:00.000’ AND ‘2016-10-30 00:00:00.000’ –Midel

SELECT * FROM dbo.Test_FNmCalls() WHERE CallDate BETWEEN ‘2010-10-01 00:00:00.000’ AND ‘2010-10-30 00:00:00.000’ –Last

SELECT * FROM dbo.Test_FNmCalls(‘2020-10-01 00:00:00.000’ , ‘2020-10-05 00:00:00.000’)

SELECT * FROM dbo.Test_FNmCalls(‘2016-10-25 00:00:00.000’ , ‘2016-10-30 00:00:00.000’)

SELECT * FROM dbo.Test_FNmCalls(‘2010-10-01 00:00:00.000’ , ‘2010-10-30 00:00:00.000’)

—————————————–

SELECT * FROM dbo.Test_VwCalls WHERE CallDate BETWEEN ‘2010-10-01 00:00:00.000’ AND ‘2010-10-30 00:00:00.000’

SELECT * FROM dbo.Test_FNCalls2(‘2016-10-27 00:00:07.000’)WHERE CallDate BETWEEN ‘2010-10-01 00:00:00.000’ AND ‘2010-10-30 00:00:00.000’

SELECT * FROM dbo.Test_FNCalls3(‘2016-10-27 00:00:07.000′,’2010-10-01 00:00:00.000’ , ‘2010-10-30 00:00:00.000’)

SELECT cast(count(*) as INT) as col_0_0_

from dbo.Test_fnCalls() call0_

where call0_.ContractId=41678894

and call0_.CallDate>= N’2016-01-27 00:00:07.000′

and call0_.CallDate< N’2016-12-27 00:00:07.000′

SELECT cast(count(*) as INT) as col_0_0_

from dbo.Test_fnCalls2(‘2016-10-27 00:00:07.000’) call0_

where call0_.ContractId=41678894

and call0_.CallDate>= N’2016-01-27 00:00:07.000′

and call0_.CallDate<= N’2016-12-27 00:00:07.000′

SELECT cast(count(*) as INT) as col_0_0_

from dbo.Test_fnCalls3(‘2016-10-27 00:00:07.000′,N’2016-01-27 00:00:07.000′, N’2016-12-27 00:00:07.000’) call0_

where call0_.ContractId=41678894

–صفحه تماس های یک مشترک

select contactcha1_.Description as col_0_0_, calltype2_.Description as col_1_0_,/* operator3_.FarsiName as col_2_0_,*/

call0_.OperatorId as col_3_0_, call0_.RespondentCityId as col_4_0_, city4_.Name as col_5_0_,

calldescri5_.Description as col_6_0_, callresult6_.Description as col_7_0_, call0_.CallDate as col_8_0_,

call0_.ID as col_9_0_, call0_.ProblemDescription as col_10_0_, call0_.ResultDescription as col_11_0_,

call0_.CallerName as col_12_0_, call0_.ContactPhone as col_13_0_, call0_.Completed as col_14_0_,

call0_.ModifyStamp as col_15_0_, call0_.IsACSActive as col_16_0_

from tbCalls call0_

left outer join lkContactChannel contactcha1_ on call0_.ContactChannelId=contactcha1_.ID

left outer join lkCallType calltype2_ on call0_.CallType=calltype2_.ID

/*left outer join vwAspnetUsers operator3_ on call0_.OperatorId=operator3_.OperatorId */

left outer join tbCity city4_ on call0_.RespondentCityId=city4_.ID

left outer join lkCallDescription calldescri5_ on call0_.ProblemID=calldescri5_.ID

left outer join lkCallResult callresult6_ on call0_.ResultID=callresult6_.Id

where call0_.ContractId=41678894 and call0_.CallDate>=N’2020-01-27 00:00:07.000′ and call0_.CallDate<N’2020-12-27 00:00:07.000′

order by call0_.ID desc OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY

select contactcha1_.Description as col_0_0_, calltype2_.Description as col_1_0_,/* operator3_.FarsiName as col_2_0_,*/

call0_.OperatorId as col_3_0_, call0_.RespondentCityId as col_4_0_, city4_.Name as col_5_0_,

calldescri5_.Description as col_6_0_, callresult6_.Description as col_7_0_, call0_.CallDate as col_8_0_,

call0_.ID as col_9_0_, call0_.ProblemDescription as col_10_0_, call0_.ResultDescription as col_11_0_,

call0_.CallerName as col_12_0_, call0_.ContactPhone as col_13_0_, call0_.Completed as col_14_0_,

call0_.ModifyStamp as col_15_0_, call0_.IsACSActive as col_16_0_

from dbo.Test_VwCalls call0_

left outer join lkContactChannel contactcha1_ on call0_.ContactChannelId=contactcha1_.ID

left outer join lkCallType calltype2_ on call0_.CallType=calltype2_.ID

/*left outer join vwAspnetUsers operator3_ on call0_.OperatorId=operator3_.OperatorId */

left outer join tbCity city4_ on call0_.RespondentCityId=city4_.ID

left outer join lkCallDescription calldescri5_ on call0_.ProblemID=calldescri5_.ID

left outer join lkCallResult callresult6_ on call0_.ResultID=callresult6_.Id

where call0_.ContractId=41678894 and call0_.CallDate>=N’2016-01-27 00:00:07.000′ and call0_.CallDate<N’2016-12-27 00:00:07.000′