ما همیشه به یک دیتابیس سرور اختصاصی نیاز نداریم. اگر شما یک برنامه نویس یا مهندس بانکهای اطلاعاتی هستید در این مقاله سعی خواهیم نمود، راهکارها و دستورالعملهایی کاربردی و آزمایش شده و مطمئن را در اختیارتان قرار دهیم.
ترفند 1 – پرهیز از استفاده اشاره گرها و تریگرها
چرا؟
از آنجا که اشاره گرها و تریگرها در آن واحد فقط در یک ردیف کار می کنند در قیاس با دیگر عملیات TSQL (که set based هستند) ذاتا کندتر می باشند. زمانی که قصد استفاده از آنها را دارید از خود بپرسید : ” آیا راه حل بهتری هست؟ ”
راهکار چیست؟
هر زمان که یک اشاره گر دیدید تلاش نمایید راهی برای تبدیل آن به یک عملیات Set Based بیابید.
هنگامی که به یک تریگر برخورد نمودید، بجویید که چطور می توان آن را با یک کد سریعتر و ساده تر در در قالب یک stored procedure جایگزین نمود.به شما پیشنهاد میشود برای درک stored procedure چیست مقاله تخصصی ما را مطالعه نمایید.
مزیت :
افزایش چشمگیر سرعت اجرای کوئری ها
پیشرفت مهارتهای TSQL برنامه نویس
استفاده بهینه از قدرت سخت افزار سرور
در پی چه هستید ؟ فقط ۱ درصد موارد شما مجبور به استفاده از اشاره گرها و تریگرها هستید!!
حتی اگر فکر میکنید استفاده آنها در این مورد ضروری است ، با یک مدیر دیتابیس کارکشته مشورت کنید، شاید راهکار بهتری وجود داشته باشد.
هزینه :
صفر
کافیست به جای cursor (اشاره گر) از عملیات های set based وبه جای تریگرها از stored procedure استفاده کنید.
ترفند 2 – فشرده سازی بکاپ
چرا؟
سود این عمل فقط کاهش استفاده از فضای دیسک نیست، اگر شما مرتبا بکاپ و ریستور انجام می دهید ، کافیست بدانید که بکاپ و ریستور به این روش ۳۰ تا ۵۰ درصد صرفه جویی در وقت را به ارمغان خواهد آورد.
راهکار چیست؟
بروز رسانی به 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
ترفند 3 – بهره گیری از فشرده سازی دیتابیس
یک ویژگی جدید در Sql Server 2008 می باشد ،پس آن را با فشرده سازی بکاپ اشتباه نگیرید!
چرا ؟
به این خاطز که سایز دیتابیس شما را با فشرده سازی صفحات آن کاهش میدهد.
این بدان معنی خواهد بود که سرور حجم کمتری از اطلاعات را لازم است که بین دیسک و حافظه منتقل نماید. و این خیلی خوب است!
راهکار چیست؟
ارتقا به Sql Server 2008
از آنجا که این قابلیت بخشی از موتور ذخیره سازی داخلی آن است.
مزیت :
فایلهای دیتابیس کم حجم تر و I/O دیسک کمتر ، به معنی دسترسی سریعتر به داده ها خواهد بود.
معمولا کاهش ۵۰ تا ۷۰ درصدی در حجم دیتابیس قابل مشاهده خواهد بود.
معایب :
میزان مصرف پردازنده بالاتر ، حتی در CPU های تک هسته ای ممکن است پردازش خارج از توان CPU گردد.بنابر این در سرورهای قدرمتند از آن استفاده نمایید.
تنها در نسخه های Developer و Enterprise بانک SQL Server 2008 از آن پشتیبانی میشود.
هزینه :
ارتقا به SQL Server 2008
ترفند 4 – سایز دهی دیتابیس
چگونه؟
تنظیم نرخ رشد دیتابیس در بخش ویژگیهای دیتابیس(database properties) نرم افزار Management Studio.
چرا؟
هنگامی که دیتابیس در حال رشد است ، نیاز به گسترش فضا برای جای دادن ردیفهای جدید دارد. این امر به عملکرد مناسب کوئری ها لطمه زده و ممکن است در برخی شرایط باعث پراکنده و تکه تکه شدن فایلهایی اساسی دیتابیس شود.
چطور میتوان این مشکل را برطرف نمود؟
با از ابتدا سایز دهی کردن دیتابیس به میزان کافی برای آینده، شما خواهید توانست رشد مداوم دیتابیس را کاهش دهید.
همچنین این مورد تکه تکه شدن بیش از حد فایل دیتابیس – که علت اصلی آن رشد مداوم میباشد- جلوگیری می کند. (البته این موضوع علت دیگری به نام بهینه نبودن عملکردها در بانک نیز دارد.)
مراقب باشید …
در بکارگیری این ترفند در دیتابیس های بسیار بزرگ و یا با نرخ بالای درج اطلاعات:
نرخ رشد خیلی پایین : ممکن است باعث تکه تکه شدن شدید دیتابیس شود! بخصوص هنگامی که قرار است دیتابیس را با IIS Server به اشتراک بگذارید.
نرخ رشد خیلی بالا : ممکن است به سادگی باعث اشغال تمام فضای اختصاص یافته شود ، همچنین گسترش حجم دیتابیس ممکن است موجب Time Out شدن کوئری ها گردد.
هزینه ؟
صفر!
ترفند 5 – پرهیز از 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 را داخل کدهای برنامه خود دارید کافیست که عبارت “ ‘%“ را در آنها جستجو نمایید.
هزینه :
صفر!
ترفند 6 – بهره گيري از تراکنش هاي کوتاه
در صورتي که نياز به استفاده از transaction داريد ، آنها را براي حداقل زمان ممکن باز نگه داريد.
چرا؟
باز نگاه داشتن تراکنش ها ممکن است باعث تايم اوت شدن ساير کابران شود.
اين مشکل به ندرت قابل رفع است.
راهکار چيست؟
بسيار ساده است. ويرايش هاي انجام شده را در ساختار داده اي کدهاي برنامه خود نگه داريد و در يک يا چند بلاک تراکنشي فقط در انتهاي زماني که عمليات ويرايش کامل شد آنها را به ديتابيس گسيل کنيد.
مزيت :
زمان پاسخدهي کمتر برنامه
شکايت کمتر کاربران ( از تايم اوت شدن جلسه خود)
مراقب باشيد …
از اين حالت که اتصال به ديتابيس را گشوده و منتظر ورود داده از طرف کاربر براي بستن آن باشيد اجتناب نماييد. ممکن است مثلا براي صرف غذا از پشت سيستم براي مدتي کنار رفته باشد. اين امر موجب ايجاد مشکل براي ساير کاربران خواهد شد.
هزينه :
هيچ- تنها کافي است شيوه کد نويسي را در اين خصوص تغيير دهيد.
مطالعه بيشتر : Pessimistic and optimistic locking
ترفند 7 – ديتابيس فقط خواندني !
غير منطقي به نظر مي رسد؟
خير ! ، اگر ديتابيس نوعي کپي از يک ديتابيس live بوده و تنها براي ريپورت دهي به کار ميرود.
چرا؟
به خاطر به کار نبردن رديف ها صفحات و جداول قفل شده ، در هنگام اجراي کوئري هاي طولاني سر ريز ( Over Head ) به ميزان قابل توجهي کاهش مي يابد.
راهکار چيست؟
ديتابيس خود را بصورت معمول Restore کنيد.
بر روي ديتابيس مربوطه کليک نماييد. ويژگي read only آن را برابر True قرار دهيد.
و يا در داخل يک scheduled job پس از ريستور ديتابيس فرمان زير را بکار بگيريد:
ALTER DATABASE AccountsReports SET READ_ONLY
مزيت :
15 تا 25 درصد بهبود در عملکرد ديتابيس که رسيدن به آن اغلب در ساير روشها آسان نيست.
هشدار!
تنها براي ديتابيسهايي اين کار را انجام دهيد که نيازي به نوشتن در آنه نيست.
هزينه :
هيچ- تنها چند دقيقه کوتاه از وقت شما
مطالعه بيشتر :
locking, read only database performance
ترفند 8 – اجراي توسعه بر روي نسخه کپي ديتابيس
چرا؟
خواهيد ديد که نسبت به داده هاي اصلي ، چقدر کوئري ها سريعتر انجام ميشوند.
داده هاي آزمايشي همواره بي ارزش تر و کم حجم تر از داده هاي واقعي هستند.
چگونه ؟
از ادمين ديتابيس مربوطه بخواهيد يک بکاپ از ديتابيس مربوطه گرفته و در پوشه هاي dev و test شما ريستور کند.
اگر انجام مداوم اين کار بار زيادي را بر روي شبکه شما وارد مي نمايد ، مي توان آن را شبانه و يا در آخر هفته در قالب يک schedule job انجام داد.
مزيت :
مي توانيد بدون اينکه يک مشکل در ديتابيس در حال کار مشاهده شود، آن را ابتدا در نسخه آزمايشي تست و رفع اشکال نماييد.
اما !!
در ديتابيس هاي بسيار بزرگ ممکن است باعث کند شدن روند توسعه و برنامه نويسي شود ، چون ممکن است اجرا شدن کوئري هاي زمان زيادي طول بکشد. البته بهترين وسيله براي تشخيص زمان اجرا شدن کوئري ها در ديتابيس اصلي هم هست.
مشکلات احتمالي
برخي ادمين هاي ديتابيس از انجام اين کار به خاطر ماهيت مالي و يا محرمانه اطلاعات ديتابيس سر باز ميزنند. همچون اطلاعات درماني و يا کارت اعتباري افراد. البته در اين حالت مي توان از روشهايي همچون کد گذاري داده هاي مهمدر هنگام ريستور کردن بانک استفاده نمود تا به آساني قابل سوء استفاده نباشد.
هزينه :
صفر!
ترفند 9 – اطمينان از داشتن ايندکس براي انجام join ، جستجو و ترتيب
اگر در حال مرتب نمودن اطلاعات خاصي هستيد ، رديف هاي خاصي را براي يک مقدار مشخص در يک ستون جستجو ميکنيد يا قصد join کردن داده ها در يک يا چند ستون مي باشيد ، دقت کتيد که که ستون ها ايندکس دهي شده باشند.
راهکار چيست؟
دستور نمونه :
CREATE INDEX idxAccountNumber on tblAccount (intAccountNumber)
مزيت :
مرتب سازي و join نمودن کوئري ها ، در صورتي که ستون هاي مربوطه ايندکس دهي شده باشند همواره سريع تر مي باشد. خصوصا در مورد ديتابيس هاي بزرگ اين کار ضروري است.
ضرورت آن چيست؟
نه ! شايد دلتان بخواهد ديسک هاي سرور را با ديسکهاي Solid State که 20 برابر گران تر هستند تعويض نماييد!
اما احتمالا اين کار نميتواند گزينه مناسبي در اوضاع اقتصادي کنوني باشد.
هشدار!
مواظب باشيد دچار Over Indexing نشويد ، ممکن است باعث کاهش سرعت عمليات ورود ديتا به بانک و بزرگ تر شدن حجم ايندکس ها نسبت به داده هاي اصلي شود.
هزينه :
صفر. تنها کافي است هنگامي که در حال نوشتن يک کوئري که شامل join و Order مي باشد هستيد ، به اين موضوع که آيا ايندکسي در آن ستون موجود است يا خير؟ اگر نه ، حتما يک ايندکس نياز داريد.
نکته مفيد :
اگر ميدانيد که يک ستون هميشه شامل مقادير منحصر بفردي خواهد بود ، براي حصول کارايي و عملکرد بهتر از CREATE UNIQUE
INDEX استفاده نماييد.
در انتها به شما پیشنهاد می شود مقاله یکپارچگی داده ها در دیتا بیس SQL Server را مطالعه نمایید.
مطلب جالبی بود مرسی