بحث در مورد سرعت دستورات SQL

[JavanSoft]

2 نکته را در مورد سرعت در اطلاعات بالا می خواهم مورد بررسی قرار دهم

1) در حجم بالای اطلاعات دستوراتی شبیه به این

Select X,(Select Y From Z) As Ex1 From T1
سرعتی بسیار پایین تر از

Select Z.Y,T1.X From Z .... Join On
دارد

این مساله را در Estimated Execution Plan براحتی می توان مشاهده نمود

آیا این مساله عمومیت دارد و یا به ساختار و ارتباط ها بستگی دارد

2) در ارتباط بین جداول بنظر می رسد که Database هایی که به بصورت سطحی ( 1 Level Related) طراحی شده اند سرعت بیشتر نسبت به Multi Level ها دارند آیا این قضیه صحیح است و یا به پارامترهای دیگری بستگی دارد

[AminSobati]

دوست عزیزم،
در خصوص سوال اول،Subquery که نوشتین بسیار تعیین کننده هست. اگر این یک Correlated Subquery باشه که به ازاء هر حرکت Query اصلی (بیرونی) روی جدول، این Subquery باید مجددا ارزیابی بشه، احتمالا در Exec Plan شما میبینید که Optimizer اون رو به شکل Join انجام داده. در شرایطی ممکنه Optimizer تشخیص نده که باید از منطق Join استفاده کنه لذا اگر خودتون به شکل Join بنویسید، کمک موثری در بهینه بودن Query انجام دادین.
در مورد سوال دوم لطفا بیشتر توضیح بدین

[JavanSoft]

دوست عزیزم،
در خصوص سوال اول،Subquery که نوشتین بسیار تعیین کننده هست. اگر این یک Correlated Subquery باشه که به ازاء هر حرکت Query اصلی (بیرونی) روی جدول، این Subquery باید مجددا ارزیابی بشه، احتمالا در Exec Plan شما میبینید که Optimizer اون رو به شکل Join انجام داده. در شرایطی ممکنه Optimizer تشخیص نده که باید از منطق Join استفاده کنه لذا اگر خودتون به شکل Join بنویسید، کمک موثری در بهینه بودن Query انجام دادین.
در مورد سوال دوم لطفا بیشتر توضیح بدین

استاد گرامی
در یکی از جوابهای شما دیدم که به این شکل پاسخ داده بودید ... به همین دلیل بود که سوال کردم

سوال دوم را به این صورت مطرح می کنم
فرض کنید شما یک Master و چند Detail داشته باشید
الف می توان تک تک Detailها را به Master متصل نمود (تک سطحی)
ب می توان برای برخی از Detail ها مجددا Detail تعریف نمود

کدام روش دارای سرعت بیشتری است

اگر نیاز است باشکل مساله را توضیح دهم

[AminSobati]

داشتن Master و Detail به سناریوی شما برمیگرده. مثلا در دیتابیس Northwind، یک جدول Orders داریم و یک جدول OrderDetails. جدول دوم حاوی جزئیات جدول اول هستش. حالا اگر پروژه شما نیاز داره که Detail برای OrderDetails داشته باشید (سطح سوم)، خوب مشکلی نیست!
ضمن اینکه Denormalize کردن تاثیر قابل توجهی در افزایش Performance داره و بعضی از جاها ترجیح میدیم با افزونگی اطلاعات و کد نویسی بیشتر کنار بیایم به خاطر بهینه سازی.

[JavanSoft]

ممنون از لطفتون
من برخی نکات پیشنهادی را از سایت developercenter.ir به اینجا انتقال دادم ...نکات جالب و خواندنی است


-تا جائیکه امکان دارد سعی کنید از عبارتWHERE در دستورات SELECT خود استفاده کنید.

2- از Inner Join استفاده نکنید.

3-تا حد امکان از بکارگیری Cursor اجتناب کنید.

4-در مورد اینکه آیا SELECT شما واقعا به DISTINCT نیاز دارد یا نه توجه کنید . در جایی که نیاز نیست از آن به هیچ عنوان استفاده نکنید.

5-در عبارت SELECT خود ، فقط اسامی فیلدهایی را ذکر کنید که استفاده می کنید. لذا از عبارت SELECT * تا حد امکان خودداری کنید.

6-دستور SET ROWCOUNT همان کاری را انجام می دهد که گزینه TOP در دستور SELECT .اما گزینه TOP به مراتب کاراتر است.

7-تا حد امکان از EXISTS و IN به جای EXISTS NOT و NOT IN استفاده کنید زیرا Performance سیستم را افزایش می دهند.

8-از Constraint ها استفاده کنید.مانند گزینه های Constraint و یا Default ها.

9-از چند Constraint برای انجام یک کنترل استفاده نکنید. مثلا اگر از محدودیتهای Primary Key و Foreign Key برای کنترل جامعیت ارجاعیRefrentional Integrity)) استفاده می کنید، کنترل این مطلب در Trigger نیز تنها یک بار اضافی به سیستم تحمیل می کند.

10-زمانی که برای انجام یک درخواست هم می توان از Join استفاده کرد هم از SubQuery ، استفاده از Join توصیه می شود چون سریعتر است.

11-اگر در عبارت خود هم می توانید از IN استفاده کنید هم از EXISTS ، ترجیحا از EXISTS استفاده کنید ؤ چون کارا تر و سریتر عمل می کند.

12-وقتی هم امکان اینرا دارید که از IN استفاده کنید ، هم از BETWEEN ، از BETWEEN استفاده کنید.

13-تا جائیکه امکان دارد ، سعی کنید از SUBSTRING( ) در عبارت WHERE خود استفاده نکنید.زیرا باعث می شود که جدول Scan شود به جای اینکه از Index استفاده کند.

14-تا جائیکه امکان دارد از توابع تبدیلی در شرط WHERE استفاده نکنید.

15-با اینکه استفاده از View ها آسان است ، اما کارایی سیستم را کم می کنند.به جای استفاده از View از Stored Procedureها استفاده کنید.

16-از View های تودر تو استفاده نکنید.(در صورتیکه به توصیه 16 عمل نمی کنید !!! )

17-تا زمانی که واقعا نیازی ندارید از DISTINCT یا ORDER BY استفاده نکنید.

18-اگر در برنامه تان از جستجوی متنی wildCardی روی CHAR یا VarCHARزیاد استفاده می شود (Like % ) ، از امکانات Full Text Search استفاده کنید.

19-شما می توانید از GROUP BY با / بدون توابع Aggregation استفاده کنید.اما اگر می خواهید بالاترین کارایی را داشته باشید ، از GROUP BY بدون توابع Aggregation استفاده نکنید.

20- تا آنجا که امکان دارد از Derived Table ها به جای Temporary Table ها استفاده کنید.

21-اگر در شرط WHERE از توابعی روی فیلد ها ، استفاده شود که Non-Sargable باشند ،باعث پایین آمدن کارایی می شود.اگر بتوانید به شکلی شرط WHERE را طوری بازنگری کنید که فیلد و تابع جدا گانه باشند،در این صورت Query می تواند از Index موجود استفاده کرده و کارایی را افزایش دهید.

I)


کد:


SELECT ID,First_name,LastNameFrom MembersWHERE DATEDIFF(yy,DateOfBirth,GetDate())>21


II)



کد:


SELECT ID,First_Name,Last-NameFrom MembersWHERE DateofBirth<DATEADD(yy,-21,GetDate())



22-ایندکس باید روی تمام فیلدهایی که مرتب در WHERE ، ORDER BY ، GROUP BY ، TOP و DISTINCT استفاده می شوند ، زده شود.

23-طبق قانون Thumb ،تمام جداول حداقل یک Clustered Index داشته باشند.عموما ، نه همیشه ، Clustered Index باید روی فیلدهایی زده شوند که مقادیرش به صورت یکنواخت افزایش پیدا می کنند ، مانند فیلدهای Identity و یا فیلدهایی که مقادیرشان افزایش می یابند و Unique هستند.در بسیاری از شرایط Primary Key بهترین انتخاب برای Clustered Index است.

24-روی جداول OLTP ، ایندکس نزنید.چون هر ایندکس زمان اجرای دستورات DML را افزایش می دهد.

25-دقت کنید که به طور تصادفی ، ایندکس مشابه روی جداول نزنید . این اتفاق ممکن است به سادگی اتفاق بیافتد.برای مثال ، شما یک Unique یا Primary Key روی یک فیلد تعریف می کنید، در اینصورت اتوماتیک ایندکس هایی روی این فیلد زده می شود .اما اگر شما به این مسئله توجه نکنید و جداگانه روی این فیلد اینکدس بزنید، دچار مشکل ایندکس های تکراری می شوید.

26-عموما در موارد زیر ایندکس زده نمی شود:
• اگر Query Optimizer از ایندکس استفاده نکند.مثلا اگر جدول کوچک باشد، اکثرا از ایندکس استفاده نمی شود.
• فیلد یا فیلدهایی که قرار است در ایندکس باشند ،عریض باشند.
• اگر فیلدها از نوع Text یا Ntext یا Image باشند.
• اگر از جدول به ندرت استفاده شود.

27-گاهی اوقات ایده خوبی است که یک ایندکس مرکب را به چندین ایندکس تک فیلدی تجزیه کنید.چون عملا فیلد اول توسط Query Optimizer استفاده می شود.البته این بدین معنا نیست که همیشه Single Index از Composite Index ها بهتر عمل می کنند.فقط با تست کردن می توانید بفهمید که کدامیک برای جدول شما کارایی بیشتری دارد.

28-اگر دو یا چند جدول دارید که مرتبا آنها را به یکدیگر Join می کنید ، بهتر است روی فیلدهایی که در Join شرکت دارند ایندکس بزنید.

29-تا جائیکه امکان دارد ایندکس Unique ایجاد کنید. زیرا SQL Server روی ایندکسهای Unique سریعتر از ایندسهای غیر Unique می تواند جستجو کند.

30-از فیلدهای Float و Real برای Primary Key استفاده نکنید.زیرا یک OverHead غیر ضروری به سیستم تحمیل می کند که کارایی سیستم را می کاهد.

31-هیچگاه روی فیلدهایی که روی آنها Non-Clustered Index زده شده است ، Clustered Index نزنید.

32-از Clustered Index زدن روی فیلدهایی که مرتب Update می شود خودداری کنید.زیرا هروقت فیلدی که در یک Clustered Index استفاده شده تغییر می کند، تمام Non-Clustered Index ها هم باید Update شوند.

33- فیلد یا فیلدهایی را برای Clustered Index انتخاب می کنید که شامل اطلاعاتی هست که در Query ها بیشتر Search می شوند.

Primary Key -34 ی که شما روی جداولتان استفاده می کنید ، حتما نباید همیشه Clustered Index باشند. زمانی Primary Key را Clustered Index کنید که مرتبا ٌ Range Query روی Primary Key انجام می دهید یا می خواهید خروجیتان بر اساس Primary Key مرتب شود.

35-تا جاییکه امکان دارد از ایندکس زدن روی فیلد GUID خودداری کنید.

36-دراول تمام Stored Procedure های خود از دستور SET NOCOUNT ON استفاده کنید.

37-اگر Stored Procedure شما به صورت دینامیک باشد و یا شرایط WHERE آن در هر بار اجرا تغییر می کند، از With Recompile در Stored Procedure خود استفاده کنید.

38-اگر می خواهید اطلاعات را به صورت رشته ای در جدول ذخیره کنید ، و طول آن کمتر از 8000 است ، از نوع Char یا VarChar به جای Text استفاده کنید.

39-اگر در برنامه تان از Temporary Table زیاد استفاده می کنید، به جای آن سعی کنید از متغیرهایی از جنس Table استفاده کنید.

DateTime -40 را هیچگاه به عنوان Primary Key در نظر نگیرید.

41-اگر این انتخاب را دارید که برای ملزم کردن Rules و Default ها از Trigger یا CHECK Constarin استفاده کنید.ترجیحا از CHECK Constarin استفاده کنید.

42- برای کاهش Overhead ، کمترین کد ممکن را در Trigger بنویسید.

43- تا جاییکه ممکن است از Roll Back کردن تا حد امکان در Trigger خودداری کنید.سعی کنید قبل از اینکه ‏Trigger اجرا شود ، مشکل را برطرف کنید.

44- برای ایجـاد جـداول موقت ( در صورتیکه چاره ای جز استفـاده از آنها ندارید ) ، از SELECT INTO استفاده نکنید.



منبع http://www.developercenter.ir/Forum/showthread.php?t=1894

[ali_kolahdoozan]

من چند روز پیش صحبتم سر همین ها بود هر کس اومد یک خط توهین برام نوشت رفت . حالا دیگه وقت جواب دقیق است .

استاد ثباتی لطف بفرمائید . معلوم کنید این مطالب بالا چقدر صحت دارن

[AminSobati]

این مطالب از سایت خودمون اقتباس شده و مجددا به همینجا برگشته! توضیحات بنده هم هست:
http://www.barnamenevis.org/forum/showthread.php?t=37831

[JavanSoft]

بنده در جریان این قضیه نبودم ... اما امیدوارم این تاپیک بتواند کمک بیشتری را به کاربران بنماید