طرق لتسريع عمل SQL

طرق لتسريع عمل SQLالكاتب: طارق إبراهيم و Walcom
عادة ما يرتبط تسريع SQL Server بتقليل عمليات IO= Input/ Output لأقل حد ممكن.
بالنسبة لتصميم الجداول:
1- حاول تبسيط Normalize الجداول لمرتبة التبسيط الثالثة 3NF ما أمكن، وبالتالي ستتجنب تكرار البيانات وعمليات التحديث الشاذة update anomalies وتقليل البيانات المسترجعة بواسطة الاستعلامات )بمعنى آخر تسريعها(.
التبسيط سيؤدي لتقليل الأعمدة في الجدول، وبالتالي "حشر" سجلات أكثر في صفحة البيانات الواحدة، مما يعني تقليل عمليات IO المطلوية للوصول للبيانات.
ولكن التبسيط لمرتبة أعلى من الثالثة تكثر عمليات الربط joining بين الجداول )بالذات عند الاستعلام( وبالتالي تقليل الأداء.
ملاحظة: إذا كانت قاعدة البيانات مخصصة للتحليل أكثر من التحديث، فقلل التبسيط، لأن الربط في حالة التبسيط قد يقلل من كفاءة وسرعة تحليل البيانات.
2- إذا كانت قاعدة البيانات تحوي الكثير من البيانات، فقسم البيانات أفقيا partition data horizontally ، مثلا قسم بيانات الجدول لجدولين أحدهما يحوي بيانات السنة الحالية والآخر يحوي البيانات القديمة، مما يحصر البيانات المطلوبة غالبا في السنة الحالية ويقلل كمية البيانات المسترجعة، مما يعني تقليل عمليات IO.
3- حاول تقليل البيانات المسترجعة في الاستعلامات، أو بكلمة أخرى لاتفتح recordsets ضخمة، وذلك بوضع مزيد من الشروط في جزء where من جملة select.
4- إذا كان ممكنا استعمل القيود العلائقية بين البيانات relational constraints بدلا من استعمال الTriggers و الrules و الdefaults، لقابلية الخطأ الكبيرة فيها عندما تستعمل لمحاكاة القيود العلائقية.
أيضا إذا أمكن استعمل القيود العلائقية المتتالية cascading relational constraints بدلا من الtriggers، فعمل حذف متتالي cascading delete أو تحديث متتالي cascading update لايتطلب أكثر من فقرة on delete أو فقرة on update في جزء references من جملة create table أو alter table بدلا من كتابة Trigger مخصص لذلك وقضاء وقت آخر في عمل debugging له.
5- حاول التقليل بقدر الإمكان من وجود الnull values ، لإنها تعني إما القيمة غير موجودة أو غير معروفة، وتسبب أخطاء في حال استعمال دوال حسابية.
6- حاول تضييق أحجام حقول الجدول، ليقلل حجم الحقل، وبالتالي يقلل حجم الجدول، مما يعني تسريع الاستعلامات.
كيف أضيقها؟
- إذا كنت ستخزن أرقاما صحيحة ما بين 0-255 ، استعمل النوع tinyint )يحتاج ل1 بايت فقط(.
-إذا كنت ستخزن أرقاما صحيحة ما بين -32768 إلى 32767 ، استعمل النوع smallint)يحتاج ل2 بايت فقط(.
-إذا كنت ستخزن أرقاما صحيحة ما بين -2147483648 إلى 2147483647 ، استعمل النوع int)يحتاج ل4 بايت، قارن(
- إذا كنت ستخزن قيما نقدية مابين -214748.3648 إلى 214748.3648 استعمل النوع smallmoney )يحتاج ل4 بايت فقط(
- إذا كنت ستخزن قيما نقدية أكبر أو أصغر بكثيييير وتحتاج لدقة أعداد عشرية أكثر فاستعمل النوع money )يحتاج ل8 بايت، قارن(.
وجهة نظر: إذا كان اقتصاد بلدك يعاني من التضخم فاستعمل النوع money بدون أقل تفكير.
- إذا كنت ستخزن تواريخ ما بين 1/1/1900 إلى 6/6/2079 مع دقة لمستوع الدقيقة فاستعمل النوع smalldatetime )يحتاج ل4 بايت فقط( أما إذا كان مدى التواريخ أبعد أو كان مطلوبا دقة أكبر فاستعمل النوع datetime )يحتاج ل8 بايت، قارن(.
وجهة نظر: هل تتوقع لقاعدة بياناتك أن تظل تعمل بإذن الله بعد عام 2079 ، استعمل datetime.
- استعمل النوعين varchar و nvarchar بدلا من النوعين char و nchar بقدر الإمكان، لأن النوعين الأخيرين يبذران الحيز التخزيني في مسافات فارغة )كما شرحنا أكثر من مرة (.
- استعمل النوعين varchar و nvarchar بدلا من text/ntext بقدر الإمكان، لأن محرك قاعدة البيانات يخزن صفحات البيانات data pages المخصصة للنوعين Text/Image بعيدا عن صفحات البيانات المخصصة للأنواع الأخرى. لذا فالوصول للنوعين text/ntext سيستغرق وقتا إضافيا.
وأيضا من الافضل ان تقوم بتقسيم قاعدة البيانات إلى جزئين الاول خاص بال Data Base Schema و الاخر تحتوى على الملفات من خلال انشاء ملف MDF )Primary Data File ( يحتوى على ال Schema فقط و انشاء ملف NDF )Secondary Data File ( يحتوى على الداتا ساقوم بالتوضيح عبر الكود
Create database Arabteam2000
On Primary
)
NAME = Arabteam_dat,
FILENAME = 'c:\Arabteam2000.mdf',
SIZE = 4,--Initial Size 4 MB
MAXSIZE = 10,--Maximum Size 10 MB
FILEGROWTH = 10 %
(
Log On
)
NAME = Arabteam_Log,
FILENAME = 'c:\Arabteam2000.ldf',
SIZE = 5,--Initial Size 4 MB
MAXSIZE = 10,--Maximum Size 10 MB
FILEGROWTH = 10 %
(
هنا قمت بانشاء Primary Data file يحوى ال Schema فقط بدون بيانات و الان سنقوم بانشاء Secondary Data File ليحتوى على البيانات سنقوم اولاً بانشاء FileGroup و هى تجميع منطقى يحتوى على ملفات البيانات
--Create Second FileGroup
Alter DataBase Arabteam2000
add filegroup ]SecondFG[
و من ثم سنقوم باضافة ملفات البيانات لل FileGroup
--Add Files To File Group
Alter DataBase Arabteam2000
Add file
)
Name =Arabteam2000_Data2,
FileName = 'D:\Arabteam2000_Data.ndf',
Size=2,
MaxSize=10,
FileGrowth=1MB
(
To FileGroup ]SecondFG[
لاحظ اننى حددت ان هذا الملف سيكون لل FileGroup التى سبق و انشئتها .... يمكنك اضافة اكثر من ملف لل FileGroup التى ترغب فى كتابة البيانات عليها
الان سنأتى للخطوة الاخيرة الا و هى تحديد اى ال FileGroup التى ستعتبر ال Default FileGroup لكتابة البيانات عليها
--Make The Default FileGroup
Alter DataBase Arabteam2000
Modify FileGroup ]SecondFG[ Default
الفائدة هنا هى تنظيم و تقليل ال IO لقراءة البيانات فتقسيم ملفات البيانات عبر ال FileGroup بالترتيب سيمكن ال SQL Server من الكتابة و القراءة على الملفات فى نفس الوقت بدلاً من قراءة او الكتابة على كل ملف على حدة مما يقلل من ال IO
فى حال لاحظت ستجد ان هذا الاسلوب مشابه لاسلوب عمل ال Raid5 مع الهاردات مما يعطى كفائة عند العمل مع ملفات البيانات ذات الاحجام الكبيرة و يسهل عليك صيانة ال DataBase Schema
فى نفس الوقت او نقلها للاستفادة بها فى مشروع اخر