رویه های ذخیره سازی - Stored Procedure

Sp یا به زبان فارسی " رویه های ذخیره شده "  اشیایی اجرا پذیر در بانک اطلاعاتی   SQL Server هستند که شامل یک یا چندین دستور SQL می شود ، این رویه ها میتوانند پارامتر های ورودی و خروجی داشته باشند .
همچنین میتوان داخل این رویه ها به زبان SQL برنامه نویسی کرد .
مهم ترین کاربر این رویه ها ذخیره کردن دستورات Select , Insert , Update , Delete هست یا ترکیبی از اینها .

نحوه ساخت این رویه ها به صورت زیر می باشد :

وارد بانک اطلاعاتی SQL Server شده ، پس از باز کردن بانک مورد نظر در قسمت Programmability وارد بخش Stored Procedure شوید .

بر روی Stored Procedure  کلیک راست کرده و New Stored Procedure را انتخاب نمایید .

احتمالا کد های پیشفرضی را در این صفحه مشاهده می کنید ، همه این کد ها را پاک کرده و به صورت زیر کد نویسی کنید.

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

Create Procedure YourProcedureName

(...,@FirstParamete DataType,@SecondParameter DataType)

 As

Begin

 SQLStatement

End

به جای YourProcedureName یک نام برای برای رویه انتخاب کنید . سعی کنید نامی که انتخاب می نمایید مفهوم رویه شما را برساند مثلا برای درج یک رکورد جدید در جدول Members من نام AddNewMember را انتخاب می کنم و یا برای دریافت تمام رکورد های این جدول نام GetAllMembers نام با مفهومی است .

در پرانتز مقابل نام رویه پارامتر های رویه به همراه نوع پارامتر تعیین میشود ، اگر تعداد پارامتر ها بیشتر از یکی بود با ویرگول آنها را جدا می کنیم .

به جای SQLStatement  دستورات  SQl  را که بین Begin   و End  نوشته می شوند تایپ کنید .

دستوراتی مانند Select , Insert , Update , Delete   و....

به عنوان مثال Sp زیر اطلاعات کاربر را با دریافت پارامتر نام کاربری (@ID)  از جدول TBL_Members دریافت می کند :

create procedure GetMemberbyID(@ID nvarchar(100))

as

begin

 select * from TBL_Members where ID=@ID

end

سپس بر روی دکمه Execute کلیک کرده تا رویه شما ساخته و ذخیره گردد .

برخی از قابلیت های رویه های ذخیره شده یا Stored Procedure  عبارت است از :

• Precompiled بودن آنها به این معنی که به زبان ماشین بسیار نزدیک شده و به طور کامل از اول ترجمه نمیشود ، این امر باعث افزایش سرعت بارگزاری می گردد .
• قابلیت ذخیره داشته و میتوان از یک رویه چندین بار در کد نویسی استفاده کرد .
• دارای ویرایشگر Query می باشد (میتوانید به سادگی یک Query حرفه ای ایجاد کنید)
• دارای Syntax Parser هست و از خطاهای SQL جلوگیری می کند .
• دارای امنیت بالایی است و از هک شدن سایت شما توسط SQL Injection در بسیاری موارد جلوگیری می کند .
• قابلیت درج چندین دستور SQL را پشت سرهم داراست .
• قابلیت ارسال ورودی و خروجی دارد .
• قابلیت برنامه نویسی ( شرط ، حلقه و...) به زبان TransactSQL دارد .
• و.....

حال که با قابلیت ها ونحوه ساخت Stored Procedure آشنا شدید بد نیست که به نحوه استفاده از این ابزار جالب در Asp.NET بپردازیم .

استفاده از این ابزار بسیار ساده هست .  اگر از SQL Datasource  یا ObjectDatasource استفاده می کنید یک ویزارد برای ساخت و استفاده از Stored Procedure ها قرار داده شده که به راحتی آن را مدیریت کنید .

اگر از کد نویسی برای کار با بانک های اطلاعاتی استفاده می کنید کافیه بعد از تعریف SQLCommand به جای دستورات SQL نام SP را وارد نموده و بعد از آن کد زیر را اضافه نمایید :

Cmd.CommandType = CommandType.StoredProcedure;

این کد به برنامه می گوید که دستورات نوشته شده از نوع Stored Procedure هستند و نیازی به ترجمه آن ها نیست .

در نهایت کد های اتصال ما به شکل زیر خواهد بود :

SqlConnection Con=new SqlConnection("YourConnectionString");

SqlCommand Cmd = new SqlCommand("YourProcedureName", Con);

Cmd.CommandType = CommandType.StoredProcedure;

Con.Open();

// Some Code...

Con.Close();

بهینه سازی 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 کامپایل خواهد شد و همین سرعت آنرا خواهد گرفت.

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

نکته 2 : تاجاییکه امکان داره دستورات داخل SP را کوچک نگه دارید. این کمک میکنه که تعداد Lock ها کم بشه و سرعت کلی برنامه شما بالا بره. دو راه برای کاهش طول دستورات SQL موجود است.

تفکیک کردن کارهای یکپارچه به مراحل کوچکتر که هر مرحله در حد امکان به سرعت Commit شود.

 سو استفاده از SQL Server Statement Batches ، که رفت و برگشت بین client و Server را کم میکند

نکته 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 شما هر زمان که اجرا میشود کارآمد و موثر هست.