بهینه سازی این View

[nafasak]

سلام،

این ویو از روی یک ویو دیگه ست، میشه اینو بهینه کرد؟ آخه timeout دارم....



SELECT (SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain
WHERE (CostItem = 10) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F10C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 12) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F12C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 14) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F14C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 15) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F15C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 17) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F17C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 20) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F20C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 22) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F22C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 24) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F24C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem IN (25, 27)) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F25C,
(SELECT ISNULL(SUM(Ampunt) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 29) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F29C,
(SELECT ISNULL(SUM(Cost) / NULLIF (dbo.GetSumSps(CRPMain.ProductID, CRPMain.CityID), 0), 0) AS Expr1
FROM dbo.Vw_PCAJ_CostReportMain AS View_1_2
WHERE (CostItem = 42) AND (CRPMain.ProductID = ProductID) AND (CRPMain.CityID = CityID)) AS F42C, ProductID, ProductName, ProductGrID, ProductGrName,
CityID, CityName, StateID, StateName, QuestDate
FROM dbo.Vw_PCAJ_CostReportMain AS CRPMain
GROUP BY ProductID, ProductName, ProductGrID, ProductGrName, CityID, CityName, StateID, StateName, QuestDate

[nafasak]

اینم viewMain
========================================

SELECT dbo.PCAJ_BCost.Cost, dbo.PCAJ_CostItems.CostItem, dbo.PCAJ_Regions.RegionID, dbo.PCAJ_Regions.Name AS RegionName, dbo.PCAJ_Products.ProductID,
dbo.PCAJ_Products.Name AS ProductName, dbo.PCAJ_Questionnaire.QuestDate, PCAJ_Products_1.ProductID AS ProductGrID,
PCAJ_Products_1.Name AS ProductGrName, PCAJ_Regions_3.RegionID AS CityID, PCAJ_Regions_3.Name AS CityName, PCAJ_Regions_4.RegionID AS StateID,
PCAJ_Regions_4.Name AS StateName, dbo.PCAJ_BCost.Ampunt, dbo.PCAJ_BCost.WPDay, dbo.PCAJ_BProducts.SP1, dbo.PCAJ_BProducts.SP2,
dbo.PCAJ_Questionnaire.QuestNum
FROM dbo.PCAJ_Products INNER JOIN
dbo.PCAJ_Regions AS PCAJ_Regions_1 INNER JOIN
dbo.PCAJ_Regions INNER JOIN
dbo.PCAJ_Questionnaire ON dbo.PCAJ_Regions.RegionID = dbo.PCAJ_Questionnaire.RegionID ON PCAJ_Regions_1.RegionID = dbo.PCAJ_Regions.RPID INNER JOIN
dbo.PCAJ_Regions AS PCAJ_Regions_2 ON PCAJ_Regions_1.RPID = PCAJ_Regions_2.RegionID INNER JOIN
dbo.PCAJ_BCost INNER JOIN
dbo.PCAJ_CostItems ON dbo.PCAJ_BCost.CostItemID = dbo.PCAJ_CostItems.CostItemID ON dbo.PCAJ_Questionnaire.QuestID = dbo.PCAJ_BCost.QuestID ON
dbo.PCAJ_Products.ProductID = dbo.PCAJ_BCost.ProductID INNER JOIN
dbo.PCAJ_Regions AS PCAJ_Regions_3 ON PCAJ_Regions_2.RPID = PCAJ_Regions_3.RegionID INNER JOIN
dbo.PCAJ_Regions AS PCAJ_Regions_4 ON PCAJ_Regions_3.RPID = PCAJ_Regions_4.RegionID INNER JOIN
dbo.PCAJ_Products AS PCAJ_Products_1 ON dbo.PCAJ_Products.ProPID = PCAJ_Products_1.ProductID INNER JOIN
dbo.PCAJ_BProducts ON dbo.PCAJ_Questionnaire.QuestID = dbo.PCAJ_BProducts.QuestID AND dbo.PCAJ_Products.ProductID = dbo.PCAJ_BProducts.ProductsID

[baktash.n81@gmail.com]

.... سلام

جدول ها با مقادیر Sample و صورت سئوال رو هم بذاری بهتره می شه روش کار کرد ...