|
بهینه سازی stored procedure
ها در SQL Server
مورد 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 کامپایل خواهد شد و همین
سرعت آنرا خواهد گرفت.
دلیل استفاده از کد زیر چیست و نسبت به کد بالا چه مزیتی دارد؟
در داخل هر sp فقط یک Query میتواند در داخل cache SQL قرار میگیرد . و چون در داخل
SP اول دو query هستند هر دفعه که این SP اجرا شود مجدد SP کامپایل خواهد شد و همین
سرعت آنرا خواهد گرفت.
نکته 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 شما هر زمان که اجرا میشود کارآمد و موثر هست.
نویسنده :
حميد رضا صادقيان
منبع:
http://barnamenevis.org
| 
