ما همیشه به یک دیتابیس سرور اختصاصی نیاز نداریم. اگر شما یک برنامه نویس یا مهندس بانکهای اطلاعاتی هستید در این مقاله سعی خواهیم نمود، راهکارها و دستورالعملهایی کاربردی و آزمایش شده و مطمئن را در اختیارتان قرار دهیم.
ترفند ۱ – پرهیز از استفاده اشاره گرها و تریگرها
چرا؟
از آنجا که اشاره گرها و تریگرها در آن واحد فقط در یک ردیف کار می کنند در قیاس با دیگر عملیات TSQL (که set based هستند) ذاتا کندتر می باشند. زمانی که قصد استفاده از آنها را دارید از خود بپرسید : ” آیا راه حل بهتری هست؟ ”
راهکار چیست؟
هر زمان که یک اشاره گر دیدید تلاش نمایید راهی برای تبدیل آن به یک عملیات Set Based بیابید.
هنگامی که به یک تریگر برخورد نمودید، بجویید که چطور می توان آن را با یک کد سریعتر و ساده تر در در قالب یک stored procedure جایگزین نمود.به شما پیشنهاد میشود برای درک stored procedure چیست مقاله تخصصی ما را مطالعه نمایید.
مزیت :
افزایش چشمگیر سرعت اجرای کوئری ها
پیشرفت مهارتهای TSQL برنامه نویس
استفاده بهینه از قدرت سخت افزار سرور
در پی چه هستید ؟ فقط ۱ درصد موارد شما مجبور به استفاده از اشاره گرها و تریگرها هستید!!
حتی اگر فکر میکنید استفاده آنها در این مورد ضروری است ، با یک مدیر دیتابیس کارکشته مشورت کنید، شاید راهکار بهتری وجود داشته باشد.
هزینه :
صفر
کافیست به جای cursor (اشاره گر) از عملیات های set based وبه جای تریگرها از stored procedure استفاده کنید.
ترفند ۲ – فشرده سازی بکاپ
چرا؟
سود این عمل فقط کاهش استفاده از فضای دیسک نیست، اگر شما مرتبا بکاپ و ریستور انجام می دهید ، کافیست بدانید که بکاپ و ریستور به این روش ۳۰ تا ۵۰ درصد صرفه جویی در وقت را به ارمغان خواهد آورد.
راهکار چیست؟
بروز رسانی به Sql Server 2008 و یا استفاده از ابزارهای جانبی Third Party همچون SQL Backup و SQL LiteSpeed.
مزیت بارز:
صرفه جویی در زمان مورد نیاز برنامه نویس برای بکاپ و ریستور
مزیت نه چندان آشکار:
از آنجا که دیسک I/O محدود است لذا با گزفتن بکاپ بصورت فشرده زمان به مراتب کمتری دیسک مشغول میشود و کوئری های کمتری تحت تاثیر بار لحظه ای گرفتن بکاپ قزار می گیرند.
معایب :
تنها در نسخه های Developer و Enterprise بانک Sql Server 2008 بصورت ذاتی از آن پشتیبانی میشود ، برای نسخه های دیگر می بایست ورژن های Third Party تهیه شود.
میزان مصرف پردازنده بالاتری را تجربه خواهید نمود ، حتی در CPU های تک هسته ای ممکن است پردازش خارج از توان CPU گردد.بنابر این زمانی از آن استفاده کنید که از قدرت سرور و عدم احتمال از دسترس خارج شدن آن به هنگام بکاپ و ریستور مطمئن باشید.
هزینه :
۱۴۰ تا ۲۰۰ یورو برای Sql Backup جهت استفاده سرورهای اختصاصی
در صورت استفاده از SQL Server 2008 هزینه ی افزوده ای ندارد.
مطالعه بیشتر :
Database backup compression
ترفند ۳ – بهره گیری از فشرده سازی دیتابیس
یک ویژگی جدید در Sql Server 2008 می باشد ،پس آن را با فشرده سازی بکاپ اشتباه نگیرید!
چرا ؟
به این خاطز که سایز دیتابیس شما را با فشرده سازی صفحات آن کاهش میدهد.
این بدان معنی خواهد بود که سرور حجم کمتری از اطلاعات را لازم است که بین دیسک و حافظه منتقل نماید. و این خیلی خوب است!
راهکار چیست؟
ارتقا به Sql Server 2008
از آنجا که این قابلیت بخشی از موتور ذخیره سازی داخلی آن است.
مزیت :
فایلهای دیتابیس کم حجم تر و I/O دیسک کمتر ، به معنی دسترسی سریعتر به داده ها خواهد بود.
معمولا کاهش ۵۰ تا ۷۰ درصدی در حجم دیتابیس قابل مشاهده خواهد بود.
معایب :
میزان مصرف پردازنده بالاتر ، حتی در CPU های تک هسته ای ممکن است پردازش خارج از توان CPU گردد.بنابر این در سرورهای قدرمتند از آن استفاده نمایید.
تنها در نسخه های Developer و Enterprise بانک SQL Server 2008 از آن پشتیبانی میشود.
هزینه :
ارتقا به SQL Server 2008
ترفند ۴ – سایز دهی دیتابیس
چگونه؟
تنظیم نرخ رشد دیتابیس در بخش ویژگیهای دیتابیس(database properties) نرم افزار Management Studio.
چرا؟
هنگامی که دیتابیس در حال رشد است ، نیاز به گسترش فضا برای جای دادن ردیفهای جدید دارد. این امر به عملکرد مناسب کوئری ها لطمه زده و ممکن است در برخی شرایط باعث پراکنده و تکه تکه شدن فایلهایی اساسی دیتابیس شود.
چطور میتوان این مشکل را برطرف نمود؟
با از ابتدا سایز دهی کردن دیتابیس به میزان کافی برای آینده، شما خواهید توانست رشد مداوم دیتابیس را کاهش دهید.
همچنین این مورد تکه تکه شدن بیش از حد فایل دیتابیس – که علت اصلی آن رشد مداوم میباشد- جلوگیری می کند. (البته این موضوع علت دیگری به نام بهینه نبودن عملکردها در بانک نیز دارد.)
مراقب باشید …
در بکارگیری این ترفند در دیتابیس های بسیار بزرگ و یا با نرخ بالای درج اطلاعات:
نرخ رشد خیلی پایین : ممکن است باعث تکه تکه شدن شدید دیتابیس شود! بخصوص هنگامی که قرار است دیتابیس را با IIS Server به اشتراک بگذارید.
نرخ رشد خیلی بالا : ممکن است به سادگی باعث اشغال تمام فضای اختصاص یافته شود ، همچنین گسترش حجم دیتابیس ممکن است موجب Time Out شدن کوئری ها گردد.
هزینه ؟
صفر!
ترفند ۵ – پرهیز از SARGs!
چرا؟
چون هر طور هم که منطق جستجوی مربوطه را نوشته باشید ، شدیدا” روی زمان کوئری های شما تاثیر خواهد گذاشت ( با و بدون اندیس)
راهکار چیست؟
نمونه ۱ :
select Supplier
from Accounts
where Postcode like ‘%BS1%’ — XXX WRONG !!!
یک نمونه بد! است. زیرا که وایلد کارد % در ابتدای شرط Where قرار گرفته است.
نمونه ۲ :
select Supplier
from Accounts
where left(Postcode,3) = ‘BS1’ — XXX ALSO WRONG !!!
این نمونه هم همچنان نامناسب است ، چون سمت چپ آرگومان جستجو می بایست بررسی گردد.(به جای آنکه آن با نام ستون بیان شود) که ممکن است باعث چند برابر شدن دفعات جستجو شود.
نمونه ۳ :
select Supplier
from Accounts
where Postcode like ‘BS1%’ — MUCH BETTER :- )
این نمونه خوب و مناسب است.
مزایا :
در خصوص عبارات ثابت جستجو بسیار سریعتر انجام خواهد شد. برای نمونه قرار گرفتن یک مقدار و یا نام ستون در سمت چپ تساوی،
معایب :
شاید لازم باشد برخی از عبارات شرطی where مجددا نوشته شوند، البته کار چندان سختی نیست ، اگر کدهای TSQL را داخل کدهای برنامه خود دارید کافیست که عبارت “ ‘%“ را در آنها جستجو نمایید.
هزینه :
صفر!
ترفند ۶ – بهره گیری از تراکنش های کوتاه
در صورتی که نیاز به استفاده از transaction دارید ، آنها را برای حداقل زمان ممکن باز نگه دارید.
چرا؟
باز نگاه داشتن تراکنش ها ممکن است باعث تایم اوت شدن سایر کابران شود.
این مشکل به ندرت قابل رفع است.
راهکار چیست؟
بسیار ساده است. ویرایش های انجام شده را در ساختار داده ای کدهای برنامه خود نگه دارید و در یک یا چند بلاک تراکنشی فقط در انتهای زمانی که عملیات ویرایش کامل شد آنها را به دیتابیس گسیل کنید.
مزیت :
زمان پاسخدهی کمتر برنامه
شکایت کمتر کاربران ( از تایم اوت شدن جلسه خود)
مراقب باشید …
از این حالت که اتصال به دیتابیس را گشوده و منتظر ورود داده از طرف کاربر برای بستن آن باشید اجتناب نمایید. ممکن است مثلا برای صرف غذا از پشت سیستم برای مدتی کنار رفته باشد. این امر موجب ایجاد مشکل برای سایر کاربران خواهد شد.
هزینه :
هیچ- تنها کافی است شیوه کد نویسی را در این خصوص تغییر دهید.
مطالعه بیشتر : Pessimistic and optimistic locking
ترفند ۷ – دیتابیس فقط خواندنی !
غیر منطقی به نظر می رسد؟
خیر ! ، اگر دیتابیس نوعی کپی از یک دیتابیس live بوده و تنها برای ریپورت دهی به کار میرود.
چرا؟
به خاطر به کار نبردن ردیف ها صفحات و جداول قفل شده ، در هنگام اجرای کوئری های طولانی سر ریز ( Over Head ) به میزان قابل توجهی کاهش می یابد.
راهکار چیست؟
دیتابیس خود را بصورت معمول Restore کنید.
بر روی دیتابیس مربوطه کلیک نمایید. ویژگی read only آن را برابر True قرار دهید.
و یا در داخل یک scheduled job پس از ریستور دیتابیس فرمان زیر را بکار بگیرید:
ALTER DATABASE AccountsReports SET READ_ONLY
مزیت :
۱۵ تا ۲۵ درصد بهبود در عملکرد دیتابیس که رسیدن به آن اغلب در سایر روشها آسان نیست.
هشدار!
تنها برای دیتابیسهایی این کار را انجام دهید که نیازی به نوشتن در آنه نیست.
هزینه :
هیچ- تنها چند دقیقه کوتاه از وقت شما
مطالعه بیشتر :
locking, read only database performance
ترفند ۸ – اجرای توسعه بر روی نسخه کپی دیتابیس
چرا؟
خواهید دید که نسبت به داده های اصلی ، چقدر کوئری ها سریعتر انجام میشوند.
داده های آزمایشی همواره بی ارزش تر و کم حجم تر از داده های واقعی هستند.
چگونه ؟
از ادمین دیتابیس مربوطه بخواهید یک بکاپ از دیتابیس مربوطه گرفته و در پوشه های dev و test شما ریستور کند.
اگر انجام مداوم این کار بار زیادی را بر روی شبکه شما وارد می نماید ، می توان آن را شبانه و یا در آخر هفته در قالب یک schedule job انجام داد.
مزیت :
می توانید بدون اینکه یک مشکل در دیتابیس در حال کار مشاهده شود، آن را ابتدا در نسخه آزمایشی تست و رفع اشکال نمایید.
اما !!
در دیتابیس های بسیار بزرگ ممکن است باعث کند شدن روند توسعه و برنامه نویسی شود ، چون ممکن است اجرا شدن کوئری های زمان زیادی طول بکشد. البته بهترین وسیله برای تشخیص زمان اجرا شدن کوئری ها در دیتابیس اصلی هم هست.
مشکلات احتمالی
برخی ادمین های دیتابیس از انجام این کار به خاطر ماهیت مالی و یا محرمانه اطلاعات دیتابیس سر باز میزنند. همچون اطلاعات درمانی و یا کارت اعتباری افراد. البته در این حالت می توان از روشهایی همچون کد گذاری داده های مهمدر هنگام ریستور کردن بانک استفاده نمود تا به آسانی قابل سوء استفاده نباشد.
هزینه :
صفر!
ترفند ۹ – اطمینان از داشتن ایندکس برای انجام join ، جستجو و ترتیب
اگر در حال مرتب نمودن اطلاعات خاصی هستید ، ردیف های خاصی را برای یک مقدار مشخص در یک ستون جستجو میکنید یا قصد join کردن داده ها در یک یا چند ستون می باشید ، دقت کتید که که ستون ها ایندکس دهی شده باشند.
راهکار چیست؟
دستور نمونه :
CREATE INDEX idxAccountNumber on tblAccount (intAccountNumber)
مزیت :
مرتب سازی و join نمودن کوئری ها ، در صورتی که ستون های مربوطه ایندکس دهی شده باشند همواره سریع تر می باشد. خصوصا در مورد دیتابیس های بزرگ این کار ضروری است.
ضرورت آن چیست؟
نه ! شاید دلتان بخواهد دیسک های سرور را با دیسکهای Solid State که ۲۰ برابر گران تر هستند تعویض نمایید!
اما احتمالا این کار نمیتواند گزینه مناسبی در اوضاع اقتصادی کنونی باشد.
هشدار!
مواظب باشید دچار Over Indexing نشوید ، ممکن است باعث کاهش سرعت عملیات ورود دیتا به بانک و بزرگ تر شدن حجم ایندکس ها نسبت به داده های اصلی شود.
هزینه :
صفر. تنها کافی است هنگامی که در حال نوشتن یک کوئری که شامل join و Order می باشد هستید ، به این موضوع که آیا ایندکسی در آن ستون موجود است یا خیر؟ اگر نه ، حتما یک ایندکس نیاز دارید.
نکته مفید :
اگر میدانید که یک ستون همیشه شامل مقادیر منحصر بفردی خواهد بود ، برای حصول کارایی و عملکرد بهتر از CREATE UNIQUE
INDEX استفاده نمایید.
در انتها به شما پیشنهاد می شود مقاله یکپارچگی داده ها در دیتا بیس SQL Server را مطالعه نمایید.