محمد سلیم آبادی
دوشنبه 30 فروردین 1389, 20:20 عصر
با سلام،
یک query نوشتم که می خواهم بهترین، موثر ترین و مفید ترین index ها را برایش طراحی کنم. Query در واقع کار CrossTab را انجام می دهد و با کمک Outer Joinsو T-SQL inline table-valued UDF پیاده سازی شده است.
توابعی که جدول برمی گرداند دقیقا مثل View ها توسط Optimizer بهینه می شوند. از این جهت نگرانی وجود ندارد.
ایندکس هایی که به نظرم مفید واقع می شوند اینها هستند:
CREATE CLUSTERED INDEX IX_0 ON Sales (Product_name)
CREATE NONCLUSTERED INDEX IX_1 ON Sales (Sales_year) INCLUDE (product_name, qty, product_price)
CREATE NONCLUSTERED INDEX IX_2 ON Sales (product_name) INCLUDE (qty, product_price)
از نقشه (Plan) پیداست که IX_1 برای UDF ها بسیار موثر عمل کرده است. هم Cover داده و هم در فیلتر داده ها استفاده شده.
سوال اینجاست که آیا ایندکس های بهتری هم وجود دارند که سرعت اجرای این Query را به حداکثر برسانند یا خیر؟
CREATE FUNCTION CrossTab (@sales_year INTEGER)
RETURNS TABLE
AS
RETURN
(SELECT product_name,
SUM(qty * product_price) AS year
FROM Sales
WHERE sales_year = @sales_year
GROUP BY product_name
);
GO
SELECT P1.product_name,
COALESCE(C1.[year], 0) AS year1,
COALESCE(C2.[year], 0) AS year2,
COALESCE(C3.[year], 0) AS year3,
COALESCE(C4.[year], 0) AS year4,
COALESCE(C5.[year], 0) AS year5,
COALESCE(totals, 0) AS totals
FROM Products P1
LEFT OUTER JOIN dbo.Crosstab(1990) AS C1
ON P1.product_name = C1.product_name
LEFT OUTER JOIN dbo.Crosstab(1991) AS C2
ON P1.product_name = C2.product_name
LEFT OUTER JOIN dbo.Crosstab(1992) AS C3
ON P1.product_name = C3.product_name
LEFT OUTER JOIN dbo.Crosstab(1993) AS C4
ON P1.product_name = C4.product_name
LEFT OUTER JOIN dbo.Crosstab(1994) AS C5
ON P1.product_name = C5.product_name
LEFT OUTER JOIN (SELECT product_name,
SUM(qty * product_price) AS totals
FROM Sales
GROUP BY product_name) totals
ON P1.product_name = totals.product_name;
هر دو plan تخمین زده شده و واقعی را ضمیمه کرده ام.
در مورد ساختار جداول هم می توانید به این لینک مراجعه کنید:
http://www.30sharp.com/ShowArticle.aspx?nid=13&did=207&AuthorID=11
یک query نوشتم که می خواهم بهترین، موثر ترین و مفید ترین index ها را برایش طراحی کنم. Query در واقع کار CrossTab را انجام می دهد و با کمک Outer Joinsو T-SQL inline table-valued UDF پیاده سازی شده است.
توابعی که جدول برمی گرداند دقیقا مثل View ها توسط Optimizer بهینه می شوند. از این جهت نگرانی وجود ندارد.
ایندکس هایی که به نظرم مفید واقع می شوند اینها هستند:
CREATE CLUSTERED INDEX IX_0 ON Sales (Product_name)
CREATE NONCLUSTERED INDEX IX_1 ON Sales (Sales_year) INCLUDE (product_name, qty, product_price)
CREATE NONCLUSTERED INDEX IX_2 ON Sales (product_name) INCLUDE (qty, product_price)
از نقشه (Plan) پیداست که IX_1 برای UDF ها بسیار موثر عمل کرده است. هم Cover داده و هم در فیلتر داده ها استفاده شده.
سوال اینجاست که آیا ایندکس های بهتری هم وجود دارند که سرعت اجرای این Query را به حداکثر برسانند یا خیر؟
CREATE FUNCTION CrossTab (@sales_year INTEGER)
RETURNS TABLE
AS
RETURN
(SELECT product_name,
SUM(qty * product_price) AS year
FROM Sales
WHERE sales_year = @sales_year
GROUP BY product_name
);
GO
SELECT P1.product_name,
COALESCE(C1.[year], 0) AS year1,
COALESCE(C2.[year], 0) AS year2,
COALESCE(C3.[year], 0) AS year3,
COALESCE(C4.[year], 0) AS year4,
COALESCE(C5.[year], 0) AS year5,
COALESCE(totals, 0) AS totals
FROM Products P1
LEFT OUTER JOIN dbo.Crosstab(1990) AS C1
ON P1.product_name = C1.product_name
LEFT OUTER JOIN dbo.Crosstab(1991) AS C2
ON P1.product_name = C2.product_name
LEFT OUTER JOIN dbo.Crosstab(1992) AS C3
ON P1.product_name = C3.product_name
LEFT OUTER JOIN dbo.Crosstab(1993) AS C4
ON P1.product_name = C4.product_name
LEFT OUTER JOIN dbo.Crosstab(1994) AS C5
ON P1.product_name = C5.product_name
LEFT OUTER JOIN (SELECT product_name,
SUM(qty * product_price) AS totals
FROM Sales
GROUP BY product_name) totals
ON P1.product_name = totals.product_name;
هر دو plan تخمین زده شده و واقعی را ضمیمه کرده ام.
در مورد ساختار جداول هم می توانید به این لینک مراجعه کنید:
http://www.30sharp.com/ShowArticle.aspx?nid=13&did=207&AuthorID=11