بالا بردن performance در queryها
موارد اول و دوم ربطی به Performance نداره و سلیقه ای است !!!
بعضی از موارد هم ممکن است خیلی پیش پا افتاده و فدیمی باشه ,به هر حال اونا رو 5 سال پیش آماده کردم و امیدوارم تا حدودی به دردتون بخورن.منابع آن چندین کتاب و سایت است که در صورت نیاز آنها رو هم معرفی می کنم .
1-انین اسم گزاری Object های SQL Server :
اسامی جداول نباید S جمع داشته باشد.
اسامی کلیه Object ها حداقل 2 سیلابی و از قوانین بند 2 کدنویسی پیروی می کنند.
Table => tbl
Trigger => trg
View => vw
Stored Procedure => sp
Foreign Key => FK_
Primary Key => PK_
Default => udf
Index => IDX_
2-تمام Stored Procedure ها باید دارای Headerی به فرمت زیر باشند:
‘*********************************************** ********
‘Name:
‘Creator:
‘Date of Create:
‘Last Modifier:
‘Date of Last Modify:
‘Description:
‘*********************************************** *********
3-تا جائیکه امکان دارد سعی کنید از عبارتWHERE در دستورات SELECT خود استفاده کنید.
4-از Inner Join استفاده نکنید.
5-تا حد امکان از بکارگیری Cursor اجتناب کنید.
6-در مورد اینکه آیا SELECT شما واقعا به DISTINCT نیاز دارد یا نه توجه کنید . در جایی که نیاز نیست از آن به هیچ عنوان استفاده نکنید.
7-در عبارت SELECT خود ، فقط اسامی فیلدهایی را ذکر کنید که استفاده می کنید. لذا از عبارت SELECT * تا حد امکان خودداری کنید.
8-دستور SET ROWCOUNT همان کاری را انجام می دهد که گزینه TOP در دستور SELECT .اما گزینه TOP به مراتب کاراتر است.
9-تا حد امکان از EXISTS و IN به جای EXISTS NOT و NOT IN استفاده کنید زیرا Performance سیستم را افزایش می دهند.
10-از Constraint ها استفاده کنید.مانند گزینه های Constraint و یا Default ها.
11-از چند Constraint برای انجام یک کنترل استفاده نکنید. مثلا اگر از محدودیتهای Primary Key و Foreign Key برای کنترل جامعیت ارجاعیRefrentional Integrity)) استفاده می کنید، کنترل این مطلب در Trigger نیز تنها یک بار اضافی به سیستم تحمیل می کند.
12-زمانی که برای انجام یک درخواست هم می توان از Join استفاده کرد هم از SubQuery ، استفاده از Join توصیه می شود چون سریعتر است.
13-اگر در عبارت خود هم می توانید از IN استفاده کنید هم از EXISTS ، ترجیحا از EXISTS استفاده کنید ؤ چون کارا تر و سریتر عمل می کند.
14-وقتی هم امکان اینرا دارید که از IN استفاده کنید ، هم از BETWEEN ، از BETWEEN استفاده کنید.
15-تا جائیکه امکان دارد ، سعی کنید از SUBSTRING( ) در عبارت WHERE خود استفاده نکنید.زیرا باعث می شود که جدول Scan شود به جای اینکه از Index استفاده کند.
16-تا جائیکه امکان دارد از توابع تبدیلی در شرط WHERE استفاده نکنید.
17-با اینکه استفاده از View ها آسان است ، اما کارایی سیستم را کم می کنند.به جای استفاده از View از Stored Procedureها استفاده کنید.
18-از View های تودر تو استفاده نکنید.(در صورتیکه به توصیه 16 عمل نمی کنید !!! )
19-تا زمانی که واقعا نیازی ندارید از DISTINCT یا ORDER BY استفاده نکنید.
20-اگر در برنامه تان از جستجوی متنی wildCardی روی CHAR یا VarCHARزیاد استفاده می شود (Like % ) ، از امکانات Full Text Search استفاده کنید.
21-شما می توانید از GROUP BY با / بدون توابع Aggregation استفاده کنید.اما اگر می خواهید بالاترین کارایی را داشته باشید ، از GROUP BY بدون توابع Aggregation استفاده نکنید.
22- تا آنجا که امکان دارد از Derived Table ها به جای Temporary Table ها استفاده کنید.
23-اگر در شرط WHERE از توابعی روی فیلد ها ، استفاده شود که Non-Sargable باشند ،باعث پایین آمدن کارایی می شود.اگر بتوانید به شکلی شرط WHERE را طوری بازنگری کنید که فیلد و تابع جدا گانه باشند،در این صورت Query می تواند از Index موجود استفاده کرده و کارایی را افزایش دهید.
I)
SELECT ID,First_name,LastName
From Members
WHERE DATEDIFF(yy,DateOfBirth,GetDate())>21
II)
SELECT I
D,First_Name,Last-Name
From Members
WHERE
DateofBirth<DATEADD(yy,-21,GetDate())
24-ایندکس باید روی تمام فیلدهایی که مرتب در WHERE ، ORDER BY ، GROUP BY ، TOP و DISTINCT استفاده می شوند ، زده شود.
25-طبق قانون Thumb ،تمام جداول حداقل یک Clustered Index داشته باشند.عموما ، نه همیشه ، Clustered Index باید روی فیلدهایی زده شوند که مقادیرش به صورت یکنواخت افزایش پیدا می کنند ، مانند فیلدهای Identity و یا فیلدهایی که مقادیرشان افزایش می یابند و Unique هستند.در بسیاری از شرایط Primary Key بهترین انتخاب برای Clustered Index است.
26-روی جداول OLTP ، ایندکس نزنید.چون هر ایندکس زمان اجرای دستورات DML را افزایش می دهد.
27-دقت کنید که به طور تصادفی ، ایندکس مشابه روی جداول نزنید . این اتفاق ممکن است به سادگی اتفاق بیافتد.برای مثال ، شما یک Unique یا Primary Key روی یک فیلد تعریف می کنید، در اینصورت اتوماتیک ایندکس هایی روی این فیلد زده می شود .اما اگر شما به این مسئله توجه نکنید و جداگانه روی این فیلد اینکدس بزنید، دچار مشکل ایندکس های تکراری می شوید.
28-عموما در موارد زیر ایندکس زده نمی شود:
• اگر Query Optimizer از ایندکس استفاده نکند.مثلا اگر جدول کوچک باشد، اکثرا از ایندکس استفاده نمی شود.
• فیلد یا فیلدهایی که قرار است در ایندکس باشند ،عریض باشند.
• اگر فیلدها از نوع Text یا Ntext یا Image باشند.
• اگر از جدول به ندرت استفاده شود.
29-گاهی اوقات ایده خوبی است که یک ایندکس مرکب را به چندین ایندکس تک فیلدی تجزیه کنید.چون عملا فیلد اول توسط Query Optimizer استفاده می شود.البته این بدین معنا نیست که همیشه Single Index از Composite Index ها بهتر عمل می کنند.فقط با تست کردن می توانید بفهمید که کدامیک برای جدول شما کارایی بیشتری دارد.
30-اگر دو یا چند جدول دارید که مرتبا آنها را به یکدیگر Join می کنید ، بهتر است روی فیلدهایی که در Join شرکت دارند ایندکس بزنید.
31-تا جائیکه امکان دارد ایندکس Unique ایجاد کنید. زیرا SQL Server روی ایندکسهای Unique سریعتر از ایندسهای غیر Unique می تواند جستجو کند.
32-از فیلدهای Float و Real برای Primary Key استفاده نکنید.زیرا یک OverHead غیر ضروری به سیستم تحمیل می کند که کارایی سیستم را می کاهد.
33-هیچگاه روی فیلدهایی که روی آنها Non-Clustered Index زده شده است ، Clustered Index نزنید.
34-از Clustered Index زدن روی فیلدهایی که مرتب Update می شود خودداری کنید.زیرا هروقت فیلدی که در یک Clustered Index استفاده شده تغییر می کند، تمام Non-Clustered Index ها هم باید Update شوند.
35- فیلد یا فیلدهایی را برای Clustered Index انتخاب می کنید که شامل اطلاعاتی هست که در Query ها بیشتر Search می شوند.
Primary Key -36 ی که شما روی جداولتان استفاده می کنید ، حتما نباید همیشه Clustered Index باشند. زمانی Primary Key را Clustered Index کنید که مرتبا ٌ Range Query روی Primary Key انجام می دهید یا می خواهید خروجیتان بر اساس Primary Key مرتب شود.
37-تا جاییکه امکان دارد از ایندکس زدن روی فیلد GUID خودداری کنید.
38-دراول تمام Stored Procedure های خود از دستور SET NOCOUNT ON استفاده کنید.
39-اگر Stored Procedure شما به صورت دینامیک باشد و یا شرایط WHERE آن در هر بار اجرا تغییر می کند، از With Recompile در Stored Procedure خود استفاده کنید.
40-اگر می خواهید اطلاعات را به صورت رشته ای در جدول ذخیره کنید ، و طول آن کمتر از 8000 است ، از نوع Char یا VarChar به جای Text استفاده کنید.
41-اگر در برنامه تان از Temporary Table زیاد استفاده می کنید، به جای آن سعی کنید از متغیرهایی از جنس Table استفاده کنید.
DateTime -42 را هیچگاه به عنوان Primary Key در نظر نگیرید.
43-اگر این انتخاب را دارید که برای ملزم کردن Rules و Default ها از Trigger یا CHECK Constarin استفاده کنید.ترجیحا از CHECK Constarin استفاده کنید.
44- برای کاهش Overhead ، کمترین کد ممکن را در Trigger بنویسید.
45- تا جاییکه ممکن است از Roll Back کردن تا حد امکان در Trigger خودداری کنید.سعی کنید قبل از اینکه Trigger اجرا شود ، مشکل را برطرف کنید.
46- برای ایجـاد جـداول موقت ( در صورتیکه چاره ای جز استفـاده از آنها ندارید ) ، از SELECT INTO استفاده نکنید.
