افزایش Performance در SP

[حمیدرضاصادقیان]

سلام دوستان.
چند روز پیش یک مقاله خیلی جالب در مورد بهینه سازی SP به دستم رسید که گفتم چند نکته از اون رو در اینجا ذکر کنم تا هرکس که نیازمنده استفاده کنه.البته نسبت به اساتید فن بی احترامی نمیکنم.چون در اینجا همه استاد بنده هستن.

مورد 1- در نامهای SP از "_sp" استفاده نکنید. زیرا این علامت مخصوص sp های سیستمی موجود در جدول master میباشد و هنگامی که از این اختصار استفاده میکنید سیستم ابتدا دنبال این نام در جداول سیستمی میگردد. پس از اون اگه پیدا نکرد با ownerDBO به دنبال اون میگرده که همین باعث میشه کلی از سرعت اجرای sp کاهش پیدا کنه.


2-در داخل یک SP بهتر است به جای اینکه داخل آن از دو دستور Select استفاده کرد، هرکدام را در داخل یک SP قرار داده و آنرا به هنگام نیاز اجرا کنیم. به مثال زیر توجه نمایید:



create Stored procedure dbo.SPTest @query bit
as
if @query=0
select * from authors
else
select * from publishers
go


بهتر است از نمونه زیر استفاده شود.


create Stored procedure dbo.SPTest @query bit
as
if @query=0
Exec sptestFromauthors
else
Exec spTestfrompublishers
go
//-----------------//
create Procedure dbo.spTestfromAuthors as
select * from Authors
go
//-----------------//
Create Procedure dbo.spTestFromPublishers as
Select * from Publishers
go


دلیل استفاده از کد زیر چیست و نسبت به کد بالا چه مزیتی دارد؟
در داخل هر sp فقط یک Query میتواند در داخل cache SQL قرار میگیرد . و چون در داخل SP اول دو query هستند هر دفعه که این SP اجرا شود مجدد SP کامپایل خواهد شد و همین سرعت آنرا خواهد گرفت.

امیدوارم که این چند نکته بدردتون خورده باشه.حالا ادامه مطالب رو هم خواهم نوشت.

[Kamyar.Kimiyabeigi]

ممنون لطفا" ادامه بدین. ترجیحا" در آخر اگر کل مطالب رو به صورت pdf در اختیار ما قرار بدین خیلی خوبه

[AminSobati]

حمید رضا جان خیلی ممنون از زحمتی که کشیدین. این کار رو حتما ادامه بدین...
من هم سعی میکنم اگر مطلبی به ذهنم میرسه اضافه کنم تا کامل تر بشه.
در خصوص این پست، همونطور که اشاره کردین هر SP در اولین مرتبه اجرا Compile میشه و براش Execution Plan بدست میاد. این Plan در دفعات بعدی اجرا، SQL Server رو راهنمایی میکنه تا با استفاده از چه الگوریتم هایی و با کمک کدوم Indexها به اجرای Query بپردازه و نیازی نیست مجددا اینها تحلیل بشن (کاری که در مرحله Compile انجام میشه).

[حمیدرضاصادقیان]

با سلام.

نکته 1: در SP هایی که نیاز نیست کاربر متوجه بشه چه تعداد ردیف تحت تاثیر قرار گرفته است،
حتماً در اول SP دستور Set NoCount On را بنویسید. زیرا اگر این دستور را ننویسید هربار که عملیاتی صورت گرفته ،SQL تعداد ردیفهای تحت تاثیر قرار گرفته را برای کاربر ارسال میکند و همین باعث یک ترافیک الکی روی client , server میشود.

نکته 2: تاجاییکه امکان داره دستورات داخل SP را کوچک نگه دارید. این کمک میکنه که تعداد Lock ها کم بشه و سرعت کلی برنامه شما بالا بره. دو راه برای کاهش طول دستورات SQL موجود است.
1) تفکیک کردن کارهای یکپارچه به مراحل کوچکتر که هر مرحله در حد امکان به سرعت Commit شود.
2) سو استفاده از SQL Server Statement Batches ، که رفت و برگشت بین client و Server را کم میکند.
(من شخصا وقتی دنبال این موضوع توی Books Online گشتم به یک نکته برخورد کردم و اونم این بود که نوشته بود مثلاً برای اجرای یک SP بهتر از {CAll} استفاده بشه. زیرا این باعث میشه که از طریق ODBc و با استفاده از پروتکل RPC دستورات مورد نظر اجرا شود که باعث سریعتر اجرا شدن SP نسبت به دستور Exec میشود.من شخصا امتحان کردم و با Profiler هم مشاهده کردم دقیقا همینطوره. Starting با RPC انجام میشه دستورات بوسیله SP اجرا میشه و Commit اون باز بوسیله RPC میباشد)

نکته 3: اگر دستورات داخل SP همیشه ثابت هستند و بصورت دینامیک تعریف نشده اند، خیلی خوب است و این کار باعث میشود تا SQL برای آن یک Plan تشکیل دهد. ولی اگر بوسیله دستور Where دارای متغیر میباشد و هردفعه عوض میشود این یک چیز ایده آل نیست و هردفعه SP باید حتماً Compile شود و Optimize نخواهد شد.
اگر شما میدانید که هر دفعه که SP نیاز به اجرا داشته باشد دائماً متغیرهای آن تغییر خواهند کرد، بهتر است ابتدای SP دستور With Recompile را بنویسید .این SP را مجبور میکنه که حتماً باید موقع اجرا دوباره Compile شود ، در اینحال شما مطمئن شوید که هربار که SP اجرا شد خود به خود Optimize میشود.

نکته 4: برنامه خود را جوری طراحی کنید که کاربر امکان لغو یک عملیات را داشته باشد. کاری نکنید که شاید کاربر مجبور به reboot کردن سیستم شود ، که میتواند باعث تجزیه نشدن مشکلات performance شود.

نکته 5: بیشتر SP ها از تعدادی پارامترها استفاده میکنند. این به خودی خود چیز بدی نیست. ولی زمانی میتونه باعث مشکل بشه که اگر پارامترها optional باشند، و تعداد پارامترهای متغیر خیلی زیاد باشند هر زمان که sp اجرا میشود. دوراه برای هندل این مشکل هست یکی با بازده آرام و یکی با بازده سریع.
(اولیش بدرد نمیخوره نمیگم)
راه بهتر اینه که ، به کار گیری منطق If...ELSE را داخل SP هست، و ایجاد یک query مجزا برای هر
ترکیب ممکنی از متغیرها که درون SP تعریف شده اند. در این راه، شما میتوانید مطمئن باشید که query شما هر زمان که اجرا میشود کارآمد و موثر هست.

خوب امیدوارم که بدردتون بخوره و اگه تو توضیحاتم اشکالی دیدید خواهشا با دانش خودتون اصلاحش کنید.ممنون.
حالا در قسمتهای بعدبالا بردن performance در دستورات Select,Where,Join را هم توضیح خواهم داد.

[h_baqery]

خیلی عالیه ، دستتون درد نکنه.

[rabinhood_tehran]

حمید رضا جان : میشه لطفا کل مقاله را در اختیار ما قرار بدید. یا اگ EBook میشناسید معرفی کنید

[bahman.net]

دستتون درد نکنه ولی می شه
لطفا کل مقاله را در اختیار ما قرار بدید

[hpx]

اگه dynamic sp داشته باشیم قاعدتا با هر بار اجرا compile میشه
و براش execution plan ساخته میشه و تو کامپایل یه مرحله optimization داره
میشه لطف کنید توضیح بدید with recompile این وسط چه کمکی میکنه؟
فقط اینکه صریحا بگیم کامپایل بشه یا....؟

[Kamyar.Kimiyabeigi]

میشه لطف کنید توضیح بدید with recompile این وسط چه کمکی میکنه؟
فقط اینکه صریحا بگیم کامپایل بشه یا....؟
ٌWith Recompile
به این معنی هست که هر دفعه که SP مورد نظر رو EXECUTE میکنیم دوباره Compile کن و از Plan مربوط به EXECUTE قبلیش استفاده نکن. (که ترجیحا" مناسب نیست چون تولید Plan برای EXECUTE یک SP توسط SQL باعث میشه که در دفعات بعدی سرعت اجرایSP بالا بره)

[rezaei manesh]

(من شخصا وقتی دنبال این موضوع توی Books Online گشتم به یک نکته برخورد کردم و اونم این بود که نوشته بود مثلاً برای اجرای یک SP بهتر از {CAll} استفاده بشه.
من می خوام هر موقع یه sp بنویسم یه نگاهی هم به sp های خود sql میندازم که ببینم اونا چکار کردن
اما اونا هم از exec استفاده می کنن ندیدم از call استفاده کرده باشند.
می شه دلیلش رو اگه کسی می دونه به من هم بگه تا به قول یارو روووشن بشم

در ضمن از مطالب مفیدتان هم متشکرم

[AminSobati]

Call برای ODBC استفاده میشه، شما EXEC بفرمایید