我被困在改善我的SQL查询的性能
问题描述:
我一直在改善我的查询性能。我的查询以单行形式返回每个产品的所有54周基准数据,如下图所示。我被困在改善我的SQL查询的性能
SELECT BusinessPlanMain.Id, BusinessPlanMain.SalesOrg, ProductMain.MetaType, ProductMain.Id [Product ID], BplText.Text, BusinessPlanData.Week1, BusinessPlanData.Week2
.
.
.
, BusinessPlanData.Week54, SystemPeriod.Year
FROM BusinessPlanMain INNER JOIN SystemPeriod ON BusinessPlanMain.SystemPeriodPKey = SystemPeriod.PKey INNER JOIN BusinessPlanData ON BusinessPlanMain.PKey = BusinessPlanData.BusinessPlanMainPKey INNER JOIN BusinessPlanDataTemplate ON BusinessPlanDataTemplate.PKey = BusinessPlanData.BusinessPlanDataTemplatePKey INNER JOIN BplText ON BplText.ReferencePKey=BusinessPlanTemplate.PKey INNER JOIN ProductMain ON BusinessPlanData.ProductMainPKey=ProductMain.PKey WHERE SystemPeriod.Year IN ('2016') AND SystemPeriod.PeriodType = 'Year' AND SystemPeriod.SalesOrg = 'OrgID1' AND BusinessPlanMain.SalesOrg='OrgID1' AND BusinessPlanData.OrgID ='OrgID1' AND BusinessPlanTemplate.Sheet='Baseline' and ProductMain.MetaType IN ('PrdGroup','Product')
The Result of the above Query is
我在我的项目来创建一个数据视图,其中i有以调换所有54周列数据到单个列作为如下所示的我已经通过使用所取得的图像中的有一个要求上述查询的'Union All'关键字54次。
我在寻找更好的解决方案来提高我的查询性能并忽略了54次联合。在此先感谢,任何更好的解决方案,可以赞赏。
SELECT BusinessPlanMain.Id
, BusinessPlanMain.SalesOrg
, ProductMain.MetaType
, ProductMain.Id [Product ID]
, BplText.Text
, BusinessPlanData.Week1
, SystemPeriod.Year
FROM BusinessPlanMain INNER JOIN SystemPeriod ON BusinessPlanMain.SystemPeriodPKey = SystemPeriod.PKey INNER JOIN BusinessPlanData ON BusinessPlanMain.PKey = BusinessPlanData.BusinessPlanMainPKey INNER JOIN BusinessPlanDataTemplate ON BusinessPlanDataTemplate.PKey = BusinessPlanData.BusinessPlanDataTemplatePKey INNER JOIN BplText ON BplText.ReferencePKey=BusinessPlanTemplate.PKey INNER JOIN ProductMain ON BusinessPlanData.ProductMainPKey=ProductMain.PKey WHERE SystemPeriod.Year IN ('2016') AND SystemPeriod.PeriodType = 'Year' AND SystemPeriod.SalesOrg = 'OrgID1' AND BusinessPlanMain.SalesOrg='OrgID1' AND BusinessPlanData.OrgID ='OrgID1' AND BusinessPlanTemplate.Sheet='Baseline' and ProductMain.MetaType IN ('PrdGroup','Product') Union ALL SELECT BusinessPlanMain.Id
, BusinessPlanMain.SalesOrg
, ProductMain.MetaType
, ProductMain.Id [Product ID]
, BplText.Text
, BusinessPlanData.Week2
, SystemPeriod.Year
FROM BusinessPlanMain INNER JOIN SystemPeriod ON BusinessPlanMain.SystemPeriodPKey = SystemPeriod.PKey INNER JOIN BusinessPlanData ON BusinessPlanMain.PKey = BusinessPlanData.BusinessPlanMainPKey INNER JOIN BusinessPlanDataTemplate ON BusinessPlanDataTemplate.PKey = BusinessPlanData.BusinessPlanDataTemplatePKey INNER JOIN BplText ON BplText.ReferencePKey=BusinessPlanTemplate.PKey INNER JOIN ProductMain ON BusinessPlanData.ProductMainPKey=ProductMain.PKey WHERE SystemPeriod.Year IN ('2016') AND SystemPeriod.PeriodType = 'Year' AND
SystemPeriod.SalesOrg = 'OrgID1' AND BusinessPlanMain.SalesOrg='OrgID1' AND BusinessPlanData.OrgID ='OrgID1' AND BusinessPlanTemplate.Sheet='Baseline' and ProductMain.MetaType IN ('PrdGroup','Product')
.
.
.
继续54个工会
The Result of the above Query is
enter code here
答
你将要使用,而不是所有的UNION子查询被称为UNPIVOT的概念。以下是使用原始查询的粗略示例 - 您需要研究该主题并更正以下代码:
WITH OriginalDataSet as (
[your first query here]
)
SELECT ods.Id
, ods.SalesOrg
, ods.MetaType
, ods.[Product ID]
, ods.Text
, ods.SystemPeriod
, BusinessPlanWeek
, BusinessPlanWeekData
FROM OriginalDataSet ods
UNPIVOT (BusinessPlanWeekData FOR BusinessPlanWeek In
([Week 1], [Week 2], [Week 3], [Week 4], [Week 5], ...)) AS unpvt
“我的查询返回所有54周” - 一年中有54周? –
感谢米奇小麦的快速回复。请考虑一周中的总周数为52。 – user2515599