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

۵ نکته برای بهبود سرعت 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 شما تاثیر خواهد گذاشت.

چه رتبه ای می‌دهید؟

میانگین ۴.۸ / ۵. از مجموع ۴

اولین نفر باش

title sign
معرفی نویسنده
میلاد فیروزی
مقالات
8 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
میلاد فیروزی
پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • سلام

      ساده‌نویسی و پیاده‌ نکردن منطق‌های پیچیده هم تاثیر زیادی در سریع اجرا شدن کوئری‌ها دارد.

      با تشکر

    •     با تشکر از آقای فیروزی؛ با اینکه من هنوز این کوئری ها رو خودم تست نکردم اما فکر میکنم نکات خیلی خوبی بیان کردید. توضیحات شما خیلی خوب بود اما بخاطر اینکه قرار است از این ترفندها در پایگاههای بزرگ استفاده کنیم کاش بیشتر وارد ریز مسائل میشدید.

    •     با سلام و خسته نباشید عالی بود خیلی خوب توضیح داده بودید

    •     با سلام

      این query بهتر جواب میده
      select o.SalesOrderID, od.ProductID
      from Sales.SalesOrderHeader o
          outer apply (
              select top 1 SalesOrderID, ProductID
              from Sales.SalesOrderDetail od
      where o.SalesOrderID = od.SalesOrderID 
          ) od 
      order by 1
    •    سلام
      دوستان الان مقالاتی که بصورت ترفند و چک لیست است بیشتر در دنیای اطلاعات طالب دارند.
      آقای فیروزی از مقاله شما سپاسگزارم.

    • سلام

      دوست عزیز، همانطوریکه اشاره کردید و مهندس طاهری عزیز هم مطلب رو تکمیل تر کردند، شما می تونید روی این مبحث متمرکز شده و در بخش بندی های متفاوتی در این زمینه، این مبحث را به اتفاق سایر کاربران سایت نیک آموز به چالش بکشید و مطمئناً با نظرات اساتید نیک آموزی، شاهد ایده،نظرات و راهکارهای بسیار عالی در این زمینه خواهیم بود که منجربه پُربارتر شدن مباحث مورد نظر خواهد شد.
      درهر صورت از استارتی که روی این موضوع زدید تشکر میکنم و منتظر سایر نکات کاربردی در بخش بندی های مختلف در این زمینه هستیم.
      موفق باشید.
      •     حتما

        برای شروع یک مقاله فقط در زمینه Cover Index آماده کرده م
        🙂
    •   با سلام بسیار عالی و خوب بود وممنون از مهندس طاهری عزیز که که بابت هر مقاله نکات تکمیلی رو ارائه میدن.با تشکر

    •    سلام دوستان

      به نظر من باید روی Foreign Key و Cover Index کمی حساسیت به خرج داد.
      لزومی به گذاشتن ایندکس روی تمامی  Foreign Key نیست. هزینه نگهداری و… را هم در نظر بگیرید
      از Cover Index به شکل حریصانه در پروژه ها استفاده نباید کرد باید دفعات اجرای کوئری و… را در نظر بگیرید و…
      در کل این نکته را قبول دارم که قانون کلی برای ایندکس وجود ندارد. و باید بسته به شرایط و… ایندکس گذاری بر روی کوئری ها را انجام داد.
      متشکرم از شما میلاد عزیز 
      موفق باشید
      •     ممنون از راهنمایی به جای شما

هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link