۵ نکته برای بهبود سرعت Queryهای شما

نوشته شده توسط: میلاد فیروزی

۱۵ دی ۱۳۹۴

زمان مطالعه: 10 دقیقه

مقدمه

خیلی ها شاید فکر کنند بهبود بخشیدن Queryهای SQL Server کار چندان جذابی نباشد ، ولی فکر کنید یک Query دارید که ۱۵ دقیقه طول می کشد تا اجرا شود و شما با تخصص خودتان و تغییراتی که روی آن انجام می دهید این عدد را تا ۲ الی ۳ ثانیه کاهش می دهید ، آیا این جذاب نیست؟

در این جا می خواهیم چند نکته کاربردی را در این زمینه بررسی کنیم ، این ها همه ی راه حل ها نیستند و تنها ۵ عدد از هزاران کاری می باشند که شما در زمینه Query Optimization می توانید بیاموزید.

۱- از Sub-Queryها تا جایی که ممکن است بپرهیزید.

استفاده از Sub-Queryها در Select های شما عملکرد Query شما را کند خواهد کرد علی الخصوص زمانی که با حجم زیادی از اطلاعات دارید کار می کنید. سعی کنید این Sub-Queryها را با جداولی که از یک Select دیگر گرفته شده اند جایگزین کنید.

به مثال زیر دقت کنید،

 select OrderID,
(select top 1 Quantity
from [Order Details]
where OrderID = o.OrderID
order by UnitPrice desc)
from Orders o
order by 1

حال همان Query را بدون استفاده از Sub-Query می بینیم ، به نحوه استفاده از Row_number() برای یافتن آخرین رکورد دقت کنید ،

select o.OrderID, od.Quantity
from Orders o
inner join (
select OrderID, Quantity, ROW_NUMBER() over (partition by OrderID order by UnitPrice desc) r
from [Order Details]
) od on o.OrderID = od.OrderID and r = 1
order by 1

وقتی هر دو Query را با هم اجرا کنیم متوجه می شویم که سرعت اجرای دومی تقریبا دو برار اولی می باشد.

۲- از استفاده OR در شرط JOINها بپرهیزید.

این یکی نتایجی باور نکردنی دارد ، می توانم به جرعت بگویم که هر دفعه در شرط JOIN از OR استفاده می کنید سرعتتان حدقل نصف خواهد شد ،به این دو Query دقت کنید ،

select *
from Orders o
inner join [Order Details] od on o.OrderID = od.FirstOrderID or o.OrderID = od.SecondOrderID

select *
from Orders o
inner join [Order Details] od on o.OrderID = od.FirstOrderID
union all
select *
from Orders o
inner join [Order Details] od on o.OrderID = od.SecondOrderID

Query اول در حدود یک دقیقه اجرا شد در حالی که Query دوم فقط ۳۷ ثانیه طول کشید.

۳- جداول خود را به صورت مناسب Indexگذاری کنید.

این نکته را قطعا همه ی شما شنیده اید ، ولی باز هم وب سایت های زیادی می بینیم که در جداول دیتابیس خود از یک Index هم استفاده نکرده اند ، این مساله در مرحله تولید خود را نشان نمی دهد ولی وقتی حجم دیتا زیاد شد و کاربران شما افزایش یافت روی عملکرد Application شما تاثیر خواهد گذاشت.

شاید برای Indexگذاری نشود قانون کلی گذاشت ولی سعی کنید روی تمامی Foreign Keyهایتان Index بگذارید ، با این شیوه حداقل مطمئن هستید که تمامی JOIN های شما از Index استفاده می کنند. نکته دوم این می تواند باشد که با نگاهی کلی به ستون هایی که بیشتر در شرط Where شما آمده اند می فهمید که Indexگذاری روی این ستون ها هم کمکتان خواهد کرد.

۴- یاد بگیرید که از Included Culomns در Indexهایتان استفاده کنید.

یک نکته دیگر در زمینه Index گذاری استفاده از همین Included Culomns می باشد ، این نکته به قدری مهم بود که ترجیح دادم جدا از مبحث Index در موردش بحث کنیم.همانطور که می دانید در Nunclustered Index کاری که SQL Server انجام می دهد این است که ستون هایی که شما در Index قرار داده اید را در یک فضای دیگر مرتب می کند در نتیجه وقتی روی این ستون ها SELECT می زنید SQL از این INDEX استفاده کرده و نتیجه را به شما بر می گرداند. حال فرض کنید که در SELECT شما ستونی وجود دارد که در Index وجود ندارد ، SQL Server می بایست برای یافتن آن ستون به صورت فیزیکی دیتا را از درون جدول بخواند (Lookup) که این عمل از سرعت شما می کاهد. به مثال زیر دقت کنید ،

SELECT Username, Firstname, Lastname
FROM WebUser
WHERE Username like 'Bob'

در این مثال با فرض اینکه روی Username تنها گذاشته باشیم ، SQL ابتدا Index مرتبط با Bob را یافته و برای برگرداندن Firstname و Lastname باید به صورت فیزیکی اطلاعات را از روی دیسک بخواند.حال به Index زیر دقت کنید ،

CREATE NONCLUSTERED INDEX IX_WebUser_Username
ON dbo.WebUser(Username)
INCLUDE (Firstname, Lastname);

با این روش به SQL Server گفته ایم تا اطلاعات را بر اساس Username مرتب کرده و جلوی هر Username اطلاعات دیگر یعنی Firsname و Lastname را نیز درج نماید ، حال SQL با یافتن Index برای دریافت مابقی اطلاعات می تواند از اطلاعات Index استفاده کند.

۵- بیشتر از آنچه به آن نیاز دارید اطلاعات برنگردانید.

سعی کنید در Queryهایتان از SELECT * بپرهیزید ، اگر ستونی را بیشتر از آنچه بدان نیاز دارید برگردانید یعنی اینکه SQL باید اطلاعات بیشتری را از دیسک بخواند و این روی عملکرد Query شما تاثیر خواهد گذاشت.