هر ردیف از جدول نمایانگر یک فایل یا پوشه است، بنابراین به ستونی نیاز داریم که بتوانیم این موضوع را نشان دهیم. بر این پایه از ستون is_directory بهره میبریم که 1 بودن آن نشاندهندهی این است که این ردیف از جدول به یک پوشه ارجاع دارد.
نام فایل یا پوشه در ستونی به نام name نگهداری میشود که رشتهای از نوع (nvarchar(255 است. افزون بر این ستون، ستونهای دیگری نیز در این جدول وجود دارد که ویژگیهای یک فایل مانند پنهانبودن، فقطخواندنی و ... توسط آن توسط آن به دست میآید. ستون پسین file_stream نام دارد که برای پوشهها، محتوای آن Null است. علت آن این است که محتوای واقعی فایل در این ستون نگهداری میشود. در واقع یک (varbinary(max با ویژگیهای fileStream است که محتوای باینری آن در سیستم فایل NTFS ذخیره میشود. مدیریت پشت صحنهی این ستون برعهدهی SQL Server است.
افزون بر این 14 ستون، هر FileTable شامل سه ستون محاسباتی به شرح زیر است:
ستون parent_path_locator نتیجهی فراخوانی تابع (GetAncestor(1 در ستون path_locator است که جهت به دست آوردن پوشهی پدر یک فایل و پوشه استفاده میشود. ستون file_type که از مقدار رشتهای ستون name تجزیه شده است، پسوند فایل را برمیگرداند. و ستون cached_file_size اندازهی بایت ذخیرهشده ستون file_stream را برمیگرداند. با این ساختار ثابت در اینجا، هر FileTable هر آنچه از File System نیاز دارید در یک پوشهی اشتراکی به شما میدهد.
این یعنی نمایش بیواسطه FileTable به هر کاربر یا برنامه. به طوری که برای نمایش یا بهروزرسانی جدول میتوانید از روش استاندارد I/O مانند کشیدن و رهاکردن با Windows Explorer یا برنامهنویسی با System.IO.FileStream و APIهای ویندوز استفاده کنید. اینچنین:
- ایجاد یک فایل یا پوشه در سیستم فایل -> افزودن یک ردیف به جدول
- افزودن یک ردیف به جدول -> ایجاد یک فایل یا پوشه در سیستم فایل
با کپی فایلها در مسیر بالا، به صورت خودکار رکوردهای زیر در جدول PhotoTable در پایگاهدادهها افزوده میشود:
به طور خلاصه پیش از این برای افزودن به FileStream دو راه کار پیش رو داشتید. یکی استفاده از T-SQL و دیگر sqlFileStream اکنون SQL Server 2012 راه کار سوم را پیشنهاد میکند. استفاده از File System در این روش FileStream به طور خودکار پر میشود.
پیش از ساخت یک FileTable بیان این نکته دارای اهمیت است که با کپی فایلها و پوشهها هیچ چیز جدیدی به NTFS افزوده نمیشود بلکه محتوای فایل به FileStream افزوده میشود و SQL Server با بررسی همزمان FileStream و FileTable نمایشی از ردیفهای FileTable به صورت یک پوشهی اشتراکی نشان میدهد. این نکته پاسخی به این پرسش خواهد بود که آیا با استفاده از FileTable حجم پایگاهدادهها دو برابر خواهد شد و در نتیجه دشواریها و چالشهای نگهداری و پشتیبانی را پیش رو خواهیم داشت!؟ که پاسخ "خیر" خواهد بود.
ایجاد یک FileTable
پیش از این در همین تارنما، روش فعال کردن FileStream در SQL Server را آموزش دیده اید. اگر درست به خاطر داشته باشید، چیزی شبیه به دستورهای زیر بود:
CREATE DATABASE MyFileArchive ON PRIMARY (NAME = MyFileArchive_data, FILENAME = 'C:\Demo\MyFileArchive_data.mdf'), FILEGROUP FileStreamGroup CONTAINS FILESTREAM (NAME = PhotoFileLibrary_blobs, FILENAME = 'C:\Demo\MyFiles') LOG ON (NAME = PhotoFileLibrary_log, FILENAME = 'C:\Demo\MyFileArchive_log.ldf')
FileTable به FileStream متکی است؛ بر این پایه پیش از ایجاد یک FileTable باید FileStream را روی پایگاهدادهها فعال کنیم. این کار با یک تعریف درست توسط بند FILEGROUP…CONTAINS FILESTREAM انجام میشود.
برای ایجاد FileTable تنها کافی است که بند WITH FILESTREAM را به دستور CREATE DATABASE بیفزایید. (یا برای فعالکردن FileTable روی یک پایگاهدادهی ساخته شده بند SET FILESTREAM را در دستور ALTER DATABASE بنویسید.) در این بند، از DIRECTORY_NAME برای نامگذاری یک پوشه برای پایگاهدادهها استفاده میکنیم. این پوشه در یک پوشه ریشه به نام SQL Server instance نمایش داده خواهد شد. بخش دوم بند NON_TRANSACTED_ACCESS=FULL است که دسترسی غیرتراکنشی را فعال میکند. با این کار برای هر FileTable در پایگاه داده یک زیرپوشه درون پوشهای که به نام DIRECTORY_NAME نامگذاری شده است؛ ساخته میشود.
با توجه به آنچه گفته شد برای ایجاد یک پایگاهداده با امکان ساخت FileTable دستورهای زیر را اجرا کنید:
CREATE DATABASE MyFileArchive ON PRIMARY (NAME = MyFileArchive_data, FILENAME = 'C:\Demo\MyFileArchive_data.mdf'), FILEGROUP FileStreamGroup CONTAINS FILESTREAM (NAME = PhotoFileLibrary_blobs, FILENAME = 'C:\Demo\MyFiles') LOG ON (NAME = PhotoFileLibrary_log, FILENAME = 'C:\Demo\MyFileArchive_log.ldf') WITH FILESTREAM (DIRECTORY_NAME='FilesLibrary', NON_TRANSACTED_ACCESS=FULL)
USE MyFileArchive GO CREATE TABLE PhotoTable AS FileTable GO
دنباله دارد ...
طراحی روابط و ارجاعات در RavenDB
یک سوال در خصوص طراحی روابط یک بلاگ دارم:
فرض کنید میخواهیم بخش تایم لاین را به برنامه اضافه کنیم بدین شرح: هر کاربری بتواند مشترک بلاگهای مورد علاقه اش شود و هر بار که به صفحه اول برنامه مراجعه میکند جدیدترین پستهای بلاگ هایی که مشترک آنها بوده ببیند.
شاید یک نوع طراحی اینگونه باشد که جدولی داشته باشیم به نام «اشتراک» که در آن فیلد «نام کاربری نویسنده و نام کاربری مشترک» مورد نظر درج شود. سپس یک جدول هم داشته باشیم مثلاً به نام Timeline با فیلدهای «نام کاربری نویسنده، نام کاربری گیرنده، متن کامل مطلب و تاریخ ارسال».
هر زمان مطلب جدیدی منتشر شد، به ازای هر مشترک در جدول اشتراک، یک رکورد در جدول Timeline درج شود، در این حالت کار خواندن مطالب جدیدی که باید به کاربر نشان دهیم ساده میشود اما اگر یک کاربر مثلاً 10000 تا مشترک داشته باشد پس به ازای هر مطلب جدیدی که مینویسد باید 10000 رکورد در جدول Timeline درج شود، و اگر 100 نفر بخواهند مطلب بنویسند فکر کنم سیستم از کار بی افتد.
ممکن است من را راهنمایی کنید.
تولید پویای ستونها در PdfReport
Column Store Index یکی از ویژگیهای جدید SQL Server 2012 می باشد، که کارایی Query های قایل اجرا روی دیتابیسهای با حجم داده ای بسیار بالا را (که اصطلاحا به آنها Data Warehouse یا انبار داده گویند)، چندین برابر بهبود بخشیده است.
قبل از توضیح در مورد Column Store مختصری در مورد نحوه ذخیره سازی دادهها در SQL Server می پردازیم. میتوان گفت در SQL Server دو روش ذخیره سازی وجود دارد،یکی بصورت ردیفی که اصطلاحا به آن Row Storeیا Row-Wise گویند، و دیگری بصورت ستونی که اصطلاحا به آن Column Store گویند.
در روش ذخیره سازی Row Store، مقادیر ستونها در یک سطر بصورت متوالی ذخیره میشوند، در این روش ذخیره سازی از ساختار B-Tree یا Heap استفاده میشود.
یادآوری: در ساختار B-Tree، یک گره Root وجود دارد، و گره بعد از Root گره ای است که آدرس گره راست بعدی و آدرس گره چپ بعدی را در خود نگه میدارد.
شکل زیر نمای یک درخت B-Tree میباشد:
جهت کسب اطلاعات بیشتر درمورد ساختار B-Tree
یادآوری: وقتی در یک جدول، ایندکسی از نوع Clustered ایجاد نماییم، SQL Server، در ابتدا یک کپی از جدول ایجاد و دادههای جدول را از نو مرتب مینماید، و ساختار صفحه ریشه و دیگر صفحات را ایجاد میکند و سپس جدول اصلی را حذف مینماید. به جدولی که Clustered Index ندارد، اصطلاحا Heap گویند.
برخلاف ذخیره سازی Row Store، در ذخیره سازی Column Store، دادهها بصورت ستونی ذخیره میشوند،در این روش داده ها، فشرده سازی میشوند و اینکار باعث میشود،در زمان درخواست یک Query، نیاز به Disk I/o به حداقل برسد، در نتیجه، زمان و سرعت پاسخگویی به پرس و جوها بسیار افزایش مییابد.
شکل زیر نحوه ذخیره سازی داده ها،بصورت Row Store را نمایش میدهد:
شکل بالا ذخیره سازی داده ها، در ساختار B-Tree یا Heap را نمایش میدهد، در شکل فوق یک جدول چهار ستونی با N سطر (Row) در نظر گرفته شده است.بطوریکه ستونهای هر Row بطور متوالی در یک صفحه (Page) یکسان ذخیره میشوند.
شکل زیر نحوه ذخیره سازی داده ها،بصورت Column Store را نمایش میدهد:
مطابق شکل،ستونهای مربوط به هر Row،همگی در یک صفحه (Page) یکسان ذخیره شده اند. به عنوان مثال ستون C1 که مربوط به سطر اول (Row1) میباشد، با ستون C1 که مربوط به سطر دوم (Row2) میباشد، در یک ستون و در یک صفحه (Page1) ذخیره شده اند، و الی آخر ...
سئوال: یکبار دیگر به هردو شکل با دقت نگاهی بیاندازید، عمده تفاوت آنها در چیست؟
جواب: درست حدس زدید، تفاوت بارز بین دو روش Column Store و Row Store در نحوه ذخیره سازی دادهها میباشد. بطور مثال، فرض کنید،در روش ذخیره سازی Row Store، به دنبال مقادیری از ستون C2 میباشید، SQL Server میبایست کل رکوردهای جدول (منظور همه Rowها در همه Page ها)را Scan نماید، تا مقادیر مربوط به ستون C2 را بدست آورد.درحالیکه در روش ذخیره سازی Column Store، جهت یافتن مقادیر ستون C2، نیازی به Scan نمودن کل جدول نیست،بلکه SQL Server فقط به Scan نمودن ستون دوم (C2) یا Page2 بسنده مینماید.همین امر باعث افزایش چندین برابری، زمان پاسخگویی به هر Query میشود.
سئوال: در روش ذخیره سازی Column Store، چگونه مصرف حافظه بهینه میشود؟
جواب: واضح است، که در روش SQL Server، Row Store مجبور است، برای بدست آوردن دادههای مورد نظرتان،کل اطلاعات جدول را وارد حافظه نماید(اطلاعات اضافه ای که به هیچ وجه بدرد، نتیجه پرس و جوی شما نمیخورد)، و شروع به Scan دادههای مد نظر شما مینماید.بطوریکه در روش SQL Server، Column Store، فقط ستون دادههای مورد پرس و جو را در حافظه قرار میدهد.(در واقع فقط داده هایی را در حافظه قرار میدهد، که شما به آن نیاز دارید)،بنابراین،طبیعی است که در روش Column Store مقدار حافظه کمتری نسبت به روش Row Store در هنگام اجرای Query استفاده میشود. به عبارت دیگر میتوان گفت که در روش Column Store به دلیل، به حداقل رساندن استفاده از Disk I/o سرعت و زمان پاسخگویی به پرس و جوها چندین برابر میشود.
برای درک بیشتر Row Store و Column Store مثالی میزنیم:
فرض کنید،قصد بدست آوردن ستونهای C1 و C2 از جدول A را داریم، بنابراین خواهیم داشت:
Select C1, C2 from A
روش Row Store:
در این روش همه صفحات دیسک (مربوط به جدول A) درون حافظه قرار داده میشود، یعنی علاوه بر ستونهای C1 و C2، اطلاعات مربوط به ستونهای C3 و C4 نیز درون حافظه قرار میگیرد،بطوریکه مقادیر ستونهای C3 و C4 به هیچ وجه مورد قبول ما نیست، و در خروجی پرس و جوی ما تاثیری ندارد، و فقط بی جهت حافظه اشغال مینماید.
روش Column Store:
در این روش فقط صفحات مروبط به ستون C1 و C2 در حافظه قرار میگیرد.(منظور Page1 و Page2 میباشد) بنابراین فقط اطلاعات مورد نیاز در خروجی، در حافظه قرار میگیرد.
- از دیگر مزایای استفاده از روش Column Store، فشرده سازی داده میباشد،برای درک بیشتر توضیح میدهم:
چه موقع میتوانیم از Column Store استفاده نماییم:
در تعریف Column Store گفته بودم، روش فوق، جهت بهبود بخشیدن به زمان و سرعت پاسخگویی به Queryهای اجرا شده روی دیتابیسهای با حجم داده ای بسیار بالا(Data Warehouse ) میباشد، به بیان سادهتر Column Store را روی دیتابیسهای offline یا دیتابیسهایی که صرفا جهت گزارش گیری مورد استفاده قرار میگیرند، تنظیم مینمایند.در واقع با تنظیم Column Store Index روی Databaseهای بزرگ مانند Databaseهای بانکها که حجم داده ای میلیونی در جداول آنها وجود دارد، سرعت پاسخگویی Query ها، چندین برابر افزایش مییابد.
- در یک جدول میتوانید، هم Column Store Index داشته باشید و هم یک Row Store Index (منظور یک Clustered Index می باشد)
- Syntax برای ایجاد Column Store Index به شرح ذیل میباشد:
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name ON <object> ( column [ ,...n ] ) [ WITH ( <column_index_option> [ ,...n ] ) ] [ ON { { partition_scheme_name ( column_name ) } | filegroup_name | "default" } ] [ ; ] <object> ::= { [database_name. [schema_name ] . | schema_name . ] table_name { <column_index_option> ::= { DROP_EXISTING = { ON | OFF } | MAXDOP = max_degree_of_parallelism }
- یک Column Store Index میبایست از نوع NONCLUSTERED باشد.
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MyFirstName_ColumnStore] ON [Test] (Firstname)
- زمانی که در یک جدول، یک Column Store Index ایجاد نماییم، جدول ما در حالت Read-only قرار میگیرد، بطوریکه از آن پس اختیار Delete،Update و Insert روی جدول فوق را نخواهیم داشت. برای اینکه بتوانید عملیات Insert، Update یا Delete را انجام دهید، میبایست Column Store Index جدول مربوطه را Disable نمایید، و برای فعال نمودن Column Store Index، میبایست آن را Rebuild نمایید، با کلیک راست روی ایندکس ایجاد شده در SQL Server2012 موارد Disable و Rebuild قابل مشاهده میباشد.
ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] DISABLE ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] Rebuild
- بیشتر از یک Column Store Index نمیتوانید روی یک جدول ایجاد نمایید.
- در صورتی که تمایل داشته باشید بوسیله Alter ، نوع فیلدی (Type)، را که Column Store Index روی آنها اعمال گردیده است، تغییر دهید، در ابتدا میبایست Column Store Index، خود را Drop یا حذف نمایید، سپس عملیات Alter را اعمال کنید، در غیر اینصورت با خطای SQL Server مواجه میشوید.
- یک Column Store Index میتواند روی 1024 ستون در یک جدول اعمال گردد.
- یک Column Store Index نمی توانند، Unique باشد و نمیتوان از آن به عنوان Primary Key یا Foreign Key استفاده نمود.
https://github.com/MehdiSaeedifar/IrisStore
همچنین نمونهی آنلاین آنرا میتوانید در فروشگاه آیریس مشاهده کنید.
در ادامه برخی از قابلیتهای این سیستم را مشاهده میکنید:
جست و جو با قابلیت دسته بندی نتایج
به هنگام جست و جو، لیستی از موارد پیشنهادی به صورت دسته بندی شده نمایش داده میشود.
جست و جوی پیشرفته کالاها
جست و جو بر اساس قیمت، گروه، کلمات کلیدی و مرتب سازی نتایج انجام میگیرد. همچنین نتایج جست و جو بدون رفرش شدن صفحه و به صورت AJAX ای به همراه تغییر URL صفحه صورت میگیرد.
نمایش نمودار تغییرات قیمت
امکان نمایش نمودار تغییرات قیمت کالا در بازهی زمانی نیز پیش بینی شده است.
ویرایش اطلاعات به صورت inline
امکان ویرایش قیمت و تاریخ به صورت inline وجود دارد.
مدیریت تصاویر کالا
در این قسمت امکان آپلود همزمان چندین فایل به همراه پیش نمایش آنها وجود دارد. همچنین امکان کشیدن و رها کردن برای تغییر ترتیب چیدمان عکسها نیز مهیا است.( تصویر اول به عنوان کاور کالا در نظر گرفته میشود.)
قابلیتهای دیگر:
- مدیریت تصاویر اسلایدشو و تغییر ترتیب آنها از طریق کشیدن و رها کردن (drag & drop)
- تعریف برگه و تغییر ترتیب نمایش آنها از طریق کشیدن و رها کردن
- امکان ارسال پست
- تعریف دسته بندی
- مدیریت کاربران
- تعریف تنظیمات سایت
- نمایش کالا و پستهای مشابه
کارهایی که باید انجام شود:
- پیاده سازی سبد خرید و خرید آنلاین
تصویر پنل مدیریت
تصویر صفحهی اصلی:
همچنین به راحتی میتوان با طراحی قالب جدیدی، از این سیستم برای کاری غیر از فروشگاه اینترنتی استفاده کرد؛ سایتهای زیر نمونههای آنلاین دیگری از این سیستم هستند:
- http://www.petrapars.ir
- http://www.ava-tarh.ir
در نهایت فهرستی از کتاب خانهها و فناوریهای استفاده شده و همچنین مقالات مرتبط با این پروژه را قرار دادهام.
کتابخانهها و فریم ورکهای سمت سرور:
فناوری یا کتابخانه | توضیحات | مقالات مرتبط |
Bootstrap 3.x | فریم ورک پایه ای css سایت | - Bootstrap 3 RTL Theme - Twitter Bootstrap -سازگارسازی کلاسهای اعتبارسنجی Twitter Bootstrap 3 با فرمهای ASP.NET MVC -ساخت قالبهای نمایشی و ادیتور دکمه سه وضعیتی سازگار با Twitter bootstrap در ASP.NET MVC -نمایش اخطارها و پیامهای بوت استرپ به کمک TempData در ASP.NET MVC |
AdminLTE | قالب مدیریت سایت | - نسخه راستچین شده AdminLTE 2.2.1 |
Animate.css | انیمیشنهای css3 سایت | |
Font Awesome | پک آیکونهای برداری | |
Awesome Bootstrap Checkbox | زیبا سازی چک باکس ها | |
فونت فارسی وزیر | قلم فارسی | |
لطفا برای طرح سؤالات و پیشنهادات خود و جهت مدیریت بهتر آنها، از قسمت اختصاصی این پروژه در سایت استفاده نمائید.
استفاده یا عدم استفاده از یک تکنولوژی یا ابزار خاص، به پارامترهای مختلفی از جمله ابعاد پروژه، مهارت و دانش اعضای تیم، ماهیت پروژه، پلتفرم اجرا، بودجهی پروژه، مهلت تکمیل پروژه و تعداد نفرات تیم بستگی دارد. بنابراین واضح است پیچیدن یک نسخهی خاص، برای همهی سناریوها امکان پذیر نیست؛ اما شرایطی وجود دارد که استفاده یا عدم استفاده از این ابزارهای تکنولوژیک منطقیتر مینمایند.
Stored Procedure (که از این به بعد برای ایجاز، SP نوشته خواهد شد) هم از قاعده فوق مستثنی نیست و در صورت انتخاب صحیح میتواند به ارائهی محصول نهایی با کیفیتتری در زمان کوتاهتری کمک کند و در صورت انتخاب ناآگاهانه ممکن است باعث شکست یک پروژه (بخصوص در بلند مدت) شود.
تاریخچه
SQL توسط شرکت IBM در اوایل دهه 70 میلادی ایجاد شد. با اوج گرفتن زبانهای رویهای، SQL هم چندان از این قافله عقب نماند که منجر به پذیرش SP به عنوان یک استاندارد، در دهه 90 میلادی و پیاده سازی تدریجی آن توسط غولهای سازنده دیتابیس شد (رجوع فرمایید به ^ و ^). این فاصله 20 ساله باعث غنیتر شدن SQL شد و وجود SP - به معنی انتقال مدل برنامه نویسی رویهای به SQL - بخشی از مشکلات قبلی کار با کوئریهای پشت سر هم و خام را حل کرد. از سال 2000 میلادی به بعد، ORMهای قدرتمندی از جمله Hibernate و پیاده سازیهای مختلفی از Active Record و Entity Framework متولد شدند. بنابر این تقدم و تاخّرهای زمانی، بدیهی است اغلب مزایای SP نسبت به Raw SQL Query و اغلب معایب آن نسبت به ORMها باشد.
بنظر میرسد برای پاسخ به سوال اصلی این مطلب، ناگزیر به مقایسه SP با رقبای دیرینهاش هستیم. با برشمردن معایب و مزایای SP میتوان به نتیجهی منطقیتری رسید. البته باید در نظر داشت صرف استفاده از SP به معنای بهرهمند شدن از مزایای آن و صرف استفاده نکردن از آن هم بهرهمندی از رقبای آن نیست. چگونگی استفاده یک ابزار، مهمتر از خود ابزار است.
معایب SP
- دستورات Alter Table ، Add Column و Drop Column به این سادگیها هم نیستند؛ ممکن است به یکی از جداول دیتابیس دو ستون اضافه یا از آن حذف شوند. مجبوریم تمامی SPها را بخصوص Insert و Update متناظر با جدول را تغییر دهیم که این تغییرات ممکن است بصورت زنجیرهوار به سایر SPها هم سرایت کند. حال شرایطی را در نظر بگیرید که تعداد SPهای شما به چند ده و یا حتی به چند صد عدد و بیشتر، رسیده باشد که این به معنی زحمت بیشتر و تغییرات پر هزینهتر است.
- احتمال کند شدن ماشین سرویس دهنده در اثر اجرای تعداد
زیادی SP ؛ چناچه بخش زیادی از منطق برنامه از طریق SP اجرا شود، سرور دیتابیس موظف به اجرای آنهاست. اما در صورتیکه منطق،
در کد برنامه قرار داشته باشد، امکان توزیع آن بر روی سرورهای مجزا و یا حتی ماشین
کلاینت وجود خواهد داشت. امروزه اکثر کلاینتها به دیتابیسهای سبک و سریعی مجهز شدهاند. بنابراین در صورت امکان چرا بار پردازشی را به عهده آنها نگذاریم؟!
- یکپارچگی کمتر؛ تقریبا همه اپلیکیشنها نیازمند
ارتباط با سایر سیستمها هستند. اگر بخشهای زیادی از منطق برنامه درون SP مخفی شده باشند، این نقطه تلاقی بین سیستمی، احتمالا
درون خود دیتابیس قرار میگیرد و این به معنی ایجاد SP های بیشتر، افزودن
پارامترهای بیشتر، توسعه SPهای قبلی و بطور
خلاصه اعمال تغییرات بیشتر، که منتج به قابلیت نگهداری کمترخواهد شد.
- انعطاف پذیری کمتر؛ در یک شرایط ایده آل، عملکرد اپلیکیشن، مستقل از دیتابیس است. اگر نیاز به تغییر دیتابیس، مثلا از اوراکل به Microsoft SQL Server وجود داشته باشد، نیاز به بازنویسی و انتقال فانکشنها و SP ها محتمل است و از آنجائیکه که با وجود استانداردها، دیتابیسهای مختلف، معمولا در Syntax دستورات، تفاوتهای فاحشی دارند، هر چه کد بیشتری در SP ها باشد، نیاز به انتقال و تبدیل بیشتری وجود دارد.
- عدم وجود بازخورد مناسب؛ بسیاری از اوقات در صورت بروز اشکالی در حین اجرای یک SP، فقط با یک متن ساده بصورت Table has no rows و یا error مواجه میشویم. چنین خطاهایی هنگام دیباگ اصلا خوشایند نیستند. MS SQL در این بین بازخوردهای مناسبی را ارائه میکند. اگر تجربه کار با سایر دیتابیسها را داشته باشید، اهمیت بازخوردهای مناسب، ملموستر خواهد بود.
- کد نویسی سختتر؛ نوشتن کد SQL معمولا در همان IDE اپلیکیشن انجام نمیشود. جابجایی مداوم بین دو IDE ، دیباگ و کد نویسی از طریق دو اینترفیس مجزا، اصلا ایدهال نیست.
- SP منطق را بیش از حد پنهان میکند؛ حتی با دانستن نام صحیح یک SP، باز هم تصویری از پارامترهای ارسالی به آن و نتیجه برگشتی نخواهیم داشت. نمیدانیم نتیجه حاصل از اجرای SP ما مقداری را برمیگرداند یا خیر؟ در صورت وجود برگشتی، یک Cursor است یا یک مقدار؟ اگر Cursor است شامل چه ستونهایی است؟
- SP نمیتواند یک شیء را به عنوان آرگومان بپذیرد؛ بنابراین احتمال کثیف شدن کد به مرور افزایش پیدا میکند و بدتراز آن، در صورت ارسال اشتباه یک پارامتر، یا عدم تطابق تعداد پارامترها، مجبور به بررسی تمام آنها بصورت دستی هستیم. برای مثال دو قطعه کد زیر را با هم مقایسه کنید:
INSERT INTO User_Table(Id,Username,Password,FirstName,SureName,PhoneNumber,x,Email) VALUES (1,'VahidN','123456','Vahid','Nasiri','09120000000','vahid_xxx@example.com')
و معادل آن در یک ORM فرضی:
public void Insert(User user) { _users.Insert(user); db.Save(); }
بهوضوح قطعه کد sql، قبل از خوب یا بد بودن، زشت است. همچنین پارامتر x آن که فرضاً به تازگی اضافه شده، مقداری را دریافت نکرده و باعث بروز خطا خواهد شد.
- نبود Query Chaining؛ یکی از ویژگیهای جذاب ORMهای امروزی، امکان تشکیل یک کوئری با قابلیت خوانایی بالا و افزودن شرطهای بیشتر از طریق الگوی builder است. قطعه کد زیر یک SP برای جستجوی داینامیک نام و نام خانوادگی در یک جدول فرضی به اسم Users است:
public ICollection<User> GetUsers(string firstName,string lastName,Func<User, bool> orderBy) { var query = _users.where(u => u.LastName.StartsWith(lastName)); query = query.where(u => u.FirstName.StartsWith(firstName)); query = query.OrderBy(orderBy); return query.ToList(); }
در مقایسه با معادل SP آن:
CREATE PROCEDURE DynamicWhere @LastName varchar(50) = null, @FirstName varchar(50) = null, @Orderby varchar(50) = null AS BEGIN DECLARE @where nvarchar(max) SELECT @where = '1 = 1' IF @LastName IS NOT NULL SELECT @Where = @Where + " AND A.LastName LIKE @LastName + '%'" IF @FirstName IS NOT NULL SELECT @Where = @Where + " AND A.FirstName LIKE @FirstName + '%'" DECLARE @orderBySql nvarchar(max) SELECT @orderBySql = CASE WHEN @OrderBy = "LastName" THEN "A.LastName" ELSE @OrderBy = "FirstName" THEN "A.FirstName" END DECLARE @sql nvarchar(max) SELECT @sql = " SELECT A.Id , A.AccountNoId, A.LastName, A.FirstName, A.PostingDt, A.BillingAmount FROM Users WHERE " + @where + " ORDER BY " + @orderBySql exec sp_executesql @sql, N'@LastName varchar(50), @FirstName varchar(50) @LastName, @FirstName END
حاجت به گفتن نیست که قطعه کد اول چقدر خواناتر، انعطاف پذیرتر، خلاصهتر و قابل نگهداریتر است.
- نداشتن امکانات زبانهای مدرن؛ زبانها و IDEهای مدرن، امکانات قابل توجهی را برای نگهداری بهتر، انعطاف پذیری بیشتر، مقیاس پذیری بالاتر، تست پذیری دقیقتر و... ارائه میکنند. به عنوان مثال:
- شیءگرایی و امکانات آن که در SP موجود نیست و در مورد قبلی معایب، به آن مختصرا اشاره شد. در نظر بگیرید اگر SQL زبانی شیء گرا بود و مجهز به ارث بری و کپسوله سازی بود، چقدر قابلیت نگهداری آن بالاتر میرفت و حجم کدهای نوشته شده میتوانست کمتر باشند.
- نداشتن Lazy Loading که باعث مصرف زیاد حافظه میشود.
- نداشتن intellisense حین فراخوانیها.
- نداشتن Navigation Property که باعث join نویسیهای زیاد خواهد شد.
- SQL در مقایسه با یک زبان مدرن ناقص بنظر میرسد و این نوشتن کد آن را سختتر میکند.
- نداشتن امکان تغییر منطقی نام جداول و ستون ها
- مدیریت تراکنشها بصورت دستی، حال آنکه با الگوی Unit Of Work این مشکل در یک ORM قدرتمند مثل EF حل شده است.
- زمان بر بودن نوشتن SP؛ گاهی نوشتن یک تابع در یک ORM یا بعضا نوشتن یک کوئری SQL کوتاه در یک رشته متنی، سادهتر از نوشتن کد SP است. آیا برای هر وظیفه کوچک در دیتابیس، نوشتن یک SP ضروری است؟
مزایای SP :
- کمتر کردن Round Trips در شبکه و متعاقبا کاهش ترافیک شبکه؛ اگر از یک فراخوانی استفاده کنیم، کاهش Round Tripها تاثیر چندانی نخواهد داشت. همچنین ارسال یک کوئری کامل، نسبت به ارسال فقط اسم SP و پارامترهای آن، پهنای باند بیشتری اِشغال میکند. البته در یک شبکه با سرعت قابل قبول، بعید است این دو مزیت محسوس باشند؛ اما به هر حال برای موارد خاص، دو مزیت محسوب میشوند. نکته دیگر آنکه بدلیل Pre-Compiled بودن SPها و همچنین کَش شدن Execution Plan آنها، اندکی با سرعت بالاتری اجرا میشوند.
- امکان چک کردن سینتکس قبل از اجرای آن؛ در مقایسه با Raw Query مزیت محسوب میشود.
- امکان به اشتراک گذاری کد؛ برای پروژههایی که چندین اپلیکیشن با چندین زبان برنامه نویسی مختلف در حال تهیه هستند و نیازمند دسترسی مستقیم به دادهها با سرعت به نسبت بالاتری هستند، SP میتواند یک راه حل ایده آل محسوب شود. بجای پیاده سازی منطق برنامه در هر اپلیکیشن بصورت جداگانه و زحمت کدنویسی هرکدام، میتوان از SP استفاده کرد. هرچند امروزه معمولا برای حل این مشکل، API های مشترک معماری Restful ارجحیت دارد.
- کمک به ایجاد یک پَک؛ در یک زیر سیستم با نیازمندی مشخص که اعمال تغییرات در آن محتمل نمیباشد نیز SP میتواند یک گزینه مناسب به حساب آید. مثلا یک سیستم Membership را در نظر بگیرید که در پروژههای مختلف شما مورد استفاده قرار خواهد گرفت. برای مثال میشود یک سیستم Membership سفارشی را با امکان Hash پسورد و رمز کردن دادههای حساس، به کمک SP و Function های مناسب فراهم کرد و در واقع بین Application Login و Data Logic تمایز قائل شد. شخصا معماری Restful را به این روش هم ترجیح میدهم.
- بهرمند شدن از امکانات بومی SQL ؛ به عنوان نمونه برای ترانهاده کردن خروجی یک کوئری میتوان از فانکشن Pivot استفاده کرد. یا فانکشنهای تحلیلی Lead و Lag (لینک مستندات اوراکل این دو فانکشن به ترتیب در ^ و ^ ) که بنظر نمیرسد هنوز معادل مستقیمی درORM ها داشته باشند.
- تسلط و کنترل بیشتر و دقیقتر بر کوئری نهایی؛ گفته میشود SP و عبارات SQL در دیتابیس، حکم assembly را در سایر زبانها دارند. بنابراین با SP میتوان عبارات SQL و نحوه اجرای آن را در دیتابیس، بطور کامل تحت فرمان داشت. این در حالی است که هر یک از ORMها دستورات زبان برنامه نویسی مبداء را به یک عبارت SQL ترجمه میکنند که این عبارت چندان تحت کنترل برنامه نویس نیست و بیشتر به مدل کاری ORM بستگی دارد.
- امکان join بین دو یا چند دیتابیس مجزا؛ حال آنکه امکان join بین دو Context در ORM ها وجود ندارد. بعلاوه اگر دو دیتابیس مدنظر ما روی دو سرور مجزا باشند، با SP و کانفیگ Linked Server کماکان میشود کوئری join دار نوشت.
- برای عملیاتهای Batch مناسبتر است؛ در مقام مقایسه با ORM ها که با تکنیکهای مختلفی سعی در افزایش سرعت عملیات Batch، بخصوص Insert و Update را دارند، SP با سرعت قابل قبولتری اجرا میشود.
- عدم نیاز به یادگیری سینتکس و ابزاری جدید؛ موارد
بسیاری وجود دارند که فرصت یادگیری تکنولوژی جدیدی مثل یک ORM و یا SQL Bulk و حتی کتابخانههای ثالث مبتنی بر این ابزارها وجود ندارند و ممکن است مجبور شوید برای باقی ماندن در بازار رقابتی، از
دانستههای قبلی خود استفاده کنید .
- تخصصیتر کردن وظایف؛ برنامه نویسهای دیتابیس به صورت تخصصی اقدام به تحلیل روابط و ایندکسها میکنند، دیتابیس را ایجاد و نرمال سازی مینمایند، SP های متناسب را میسازند و به بهترین شکل Optimize و در آخر تست میکنند.
- امنیت به نسبت بالاتر؛ میتوان مجوز اجرای SP را به یک کاربر اعطا کرد، بدون آنکه مجوز دسترسی به جداول مورد استفاده در آن SP را داد. همچنین نسبت به کوئریهای پارامتری نشده، SQL ارجیحت دارند چون احتمال آسیب پذیری در مقابل SQL Injection را کمتر میکنند.
نتیجهگیری
اگرچه SP ها برای پردازش دادهها آنقدر هم که در وبلاگها میخوانیم بد نیستند، اما سوء استفاده از آن، مشکلات عدیدهای را ایجاد خواهد کرد. با توجه به روند تغییرات تکنولوژیهای دسترسی به دادهها و معماریهای مدرن بنظر میرسد SP در بهترین حالت، ابزار مناسبی برای انجام عملیات CRUD است و نه بیشتر؛ مگر در مواردی خاص که به تشخیص شما نیاز به استفاده بیشتر از آن وجود داشته باشد.