تنظیم عملکرد کوئری ها (1)

پیش از اینکه درخصوص بینه سازی کوئری ها در SQL  صحبت کنیم بهتر است با یک اصطلاح آشنا شویم. آرگومان های جستجوی یا SARG عبارت هایی هستند که برای محدود کردن تعداد رکوردهای بازگشتی از یک کوئری استفاده میشوند. معمولا زمانی که یک ستون از جداول در داخل یک Function و یا عبارتی دیگر بکار گرفته میشه مانند LEFT(Name,1)=’A’  به عنوان یک محدود کننده یا SARG شناخته نمیشن. اگر یک کوئری شامل SARG نباشد برای برگشت نتایج آن کوئری ، جدول مربوطه به روش Scan  پیمایش میشود.

در SQL  دو نوع پیمایش جداول وجود دارد

1.Scan که تمام جدول پیمایش میشود

2. Seek  که فقط قسمت مورد نظر را پیمایش میکنیم

همونطور که مشخص هست عملیات Seek  بهینه تر است.

برای مثال کوئری زیر را در نظر بگیرید

User AdvantureWorks

Create NONCLUSTERED INDEX OrderDataIndex

               On Sales.SalesOrderHeader(OrderDate);

Select Count(*) from sales.salesOrderHeader

               Where YEAR(OrderDate)= 2004

از انجایی که در این کوئری هیچ SARG ی استفاده نشده است پس از روش Scan برای پیمایش جداول استفاده میشود.

حال میتوان کوئری بالا را بدین شکل بهینه تر کرد

User AdvantureWorks

Create NONCLUSTERED INDEX OrderDataIndex

               On Sales.SalesOrderHeader(OrderDate);

Select Count(*) from sales.salesOrderHeader

               Where OrderDate>= ‘20040101’ and OrderDate<= ‘20050101’

با استفاده از کلیدهای ترکیبی Cntrl+M متوانید  Execution Plane مربوط به اجرای هر دو کوئری را مشاهده کنید و خواهید دید که هزینه اجرای کوئری دوم به مراتب بهتر از کوئری اول است.

استفاده از JOIN

برای بهینه سازی کوئری ها یکی از اولین استراتژی هایی که بکار گرفته میشه کم کردن تعداد JOIN ها است. نکته دیگری که باید به آن توجه کرد این است که از میان Inner Join و Outer join ، Outer Join ها هزینه بیشتری نسبت به Inner Join  دارند چون فعالت بیشتری برای پیدا کردن سطرهای نابرابر در SQL  صرف میشود.

اگر در کوئری شما تنها از inner join  استفاده شده است بهتر است بدانید که  تفاوتی وجود ندارد در اینکه شرط inner join  را در On بنویسید و یا آنرا با Where  پیاده سازی کنید هردو از لحاظ زمان عملکرد یکسان هستند. بنابراین دو کوئری زیر از لحاظ بهینگی در شرایط یکسانی می باشند

--Query 1

Select p.productID, c.CategoryId

               From products inner join category on p.productid=c.productidref

               Where p.color=’blue’

--Query 2

Select p.productID, c.CategoryId

               From products inner join category on p.productid=c.productidref

               And  p.color=’blue’

کوئری های فرعی غیرمرتبط

Subquery های غیر مرتبط با کوئری اصلی تنهای یک بار اجرا میشوند  این کئوری ها سربار بسیار کمی هم دارند. برای مثال کوئری زیر تنها یکبار میانگین قیمت های را محاسبه میکند سپس مقدار محاسبه شده بعنوان پرارمتر در کوئری اصلی استفاده میشود.

SELECT

       p.ProductID,

       P.Name

             FROM products AS p

                    WHERE p.price>=(

                    SELECT AVG(p2.price) FROM products AS p2

                    )

کوئری های فرعی وابسته

 Subquery هایی که به نوعی به کوئری اصلی وابسته میشوند از لحاظ عملکرد همانند کوئری هایی هستند که با JOIN  و علمگر EXIST پیاده سازی شده باشند. برای نمونه به کوئری زیر دقت کنید

select p.productid,

       p.name from production.product as p

       where exists (

                           select * from sales.saleorderDetail as sod

                                 where sod.productid=p.productid)

این نوع از کوئری ها معمولا بخاطر کوئری داخلی که با کوئری اصلی در ارتباط است، نسبت به JOIN عملکرد ضعیفتری دارند. اگر تعداد رکوردها  زیاد باشد کاملا این موضوع مشخص میشود چرا که به ازای هر رکورد، کوئری داخلی اجرا خواهد شد.

بایید یک کوئری دیگر را بررسی کنیم. این کوئری 6244 رکورد بر میگرداند و شامل دو ساب کوئری مرتبط با ک.وری اصلی نیز هست که به ازای هر رکورد اجرا میشوند که در مجموعا 12448 میشود.

select soh.saleOrderId,

             soh.OrderDate,

             (select top(1) sod1.UnitPrice

                    from sales.SalesOrderDetail as sod1

                    where sod1.SaleOrderId=soh.SaleOrderId

                    order by sod1.OrderQTY DESC) as UnitPrice),

 

                    (select top(1) sod1.OrderQTY

                    from sales.SalesOrderDetail as sod1

                    where sod1.SaleOrderId=soh.SaleOrderId

                    order by sod1.OrderQTY DESC) as OrderQTY)

             from sales.salesOrderHeader as soh

             where soh.TerrritoryID=4

البته این کوئری دارای اشکالاتی نیز هست.مثل اینکه ممکن است یکی از دو ساب کوئری ها مقداری را برنگرداند درحالی کوئری دیگر مقدار داشته باشد. هزینه اجرای QUERY COST این کوئری حدودا 151 میشه. که البته میخواهیم با بهینه سازی آنرا کمتر کنیم

برای بهینه سازی این کوئری چند راه وجود دارد. یکی از راه ها استفاده از  APPLY هست و برای اینکه خروجی کوئری مانند کوئری قبل باشه باید از OUTER APPLY  استفاده کنیم.

select soh.saleOrderId,

             soh.OrderDate,

             a.*

            

             from sales.salesOrderHeader as soh

             outer apply(

             select top(1) sod.UnitPrice,

                                   sod.OrderQTY

                    from sod.saleOrderID=soh.SaleOrderID as sod

                    order by sod.OrderQTY  desc) as a

             where soh.TerrritoryID=4

حالا اگر هزینه کوئری را محاسبه کنیم میبنیم که هزینه کوئری تقریبا نصف شده است و به 76 رسیده است.

حالا میخواهیم پا را فراتر بزاریم و با استفاده از ROW_NUMBER کوئری را بهینه تر کنیم.  از CTE کمک میگیریم و کوئری را بصورت زیر بازنویسی میکنیم

with a as(

 

select

       soh.SaleORderID,

       soh.OrderDate,

       sod.UnitPrice,

       sod.OrderQTY,

       Row_Number() over(

                                        partition by soh.SaleOrderID

                                        order by sod.OrderQTY desc

                                        ) as RowNo

             from sale.SaleOrderDetail as sod

             inner join sales.SalesOrderHeader as soh

                    on sod.SaleOrderID=soh.SaleOrderID

                    where soh.TerritoryID=4

)

select a.SaleORderID,

             a.OrderDate,

             a.UnitPrice,

             a.OrderQTY

              from a where a.RowNo=1

با این روشی که بکار بردیم خواهیم دید که هزینه کوئری از 76 به 3.6 رسید و این شگفت انگیزه!