优化计算年初至今和上一年的查询 [英] Optimize query that calculates year to date and previous year todate
问题描述
I have the following query that calculates year to date and previous year today. How could I optimize it?
;
WITH grouped_by_date AS （SELECT * FROM tmp.FACT_EPV_SEFPRO_DC s）， &NBSP; cumulative_sum_for_ytd AS （选择T。*， T1.QTY_UoM_YTD，t1.QTY_YTD，t1.AMOUNT_LCPNV FROM grouped_by_date T CROSS APPLY （SELECT SUM（QTY_UoM）AS QTY_UoM_YTD，SUM （QTY）AS QTY_YTD，SUM（PNV_LC）作为AMOUNT_LCPNV&NBSP;&NBSP;&NBSP; FROM grouped_by_date&NBSP;&NBSP;&NBSP; WHERE [Delivery_Year] = T [Delivery_Year]鸟;&NBSP;&NBSP;&NBSP;和转换（日期，Delivery_Year +' - 。 '+ Delivery_month +' - '+ [Invoicing_Day]）< = convert（date，T.Delivery_Year +' - '+ t.Delivery_month +' - '+ t。[Invoicing_Day]） AND [Sales_Organization] = T.Sales_Organization AND [Sales_Product_Name_N3] = T.Sales_Product_Name_N3 AND [Sales_Product_Name_N2] = T. [Sales_Product_Name_N2] AND [Sales_Product_Name_N1] = T 。[Sales_Product_Name_N1] AND [Market_Segment_Name_N2] = T。[Market_Segment_Name_N2] AND [Doc_Currency] = T.Doc_Currency AND [User_Name_N1] = T.User_Name_N1 &NBSP; &NBSP; &NBSP; AND [Engineer_Code] = T.Engineer_Code&NBSP; &NBSP; &NBSP; &NBSP; AND [User_Name_N2] = T.User_Name_N2&NBSP; &NBSP; &NBSP; &NBSP; AND [Bill_To_Customer_Code] = T.Bill_To_Customer_Code&NBSP; &NBSP; &NBSP; &NBSP; AND [User_Country_Code] = T.User_Country_Code&NBSP; &NBSP; &NBSP; &NBSP; AND [User_N3_Country_Name] = T.User_N3_Country_Name）T1）SELECT lastYear。*， （SELECT SUM（thisYear.QTY_UoM）AS QTY_UoM_YTD_prev FROM cumulative_sum_for_ytd thisYear WHERE convert（int，thisYear.Delivery_Year）= convert（int，lastYear.Delivery_Year - 1） AND（convert（int ，thisYear.Delivery_Month）LT;转换（INT，lastYear.Delivery_Month）&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;或转换（INT，thisYear.Delivery_Month）=转化（INT，lastYear.Delivery_Month）&NBSP;&NBSP;&NBSP;&NBSP ; AND convert（int，thisYear.Invoicing_Day）< - convert（int，lastYear.Invoicing_Day）） AND thisYear。[Sales_Organization] = lastYear.Sales_Organization AND thisYear。[Sales_Product_Name_N3 ] = lastYear.Sales_Product_Name_N3 AND thisYear。[Sales_Product_Name_N2] = lastYear。[Sales_Product_Name_N2] AND thisYear。[Sales_Product_Name_N1] = lastYear。[Sales_Product_Name_N1] AND thisYear。[ Market_Segment_Name_N2] = lastYear。[Market_Segment_Name_N2] AND thisYear。[Do c_Currency] = lastYear.Doc_Currency&NBSP; &NBSP; AND thisYear。[User_Name_N1] = lastYear.User_Name_N1 &NBSP; AND thisYear。[Engineer_Code] = lastYear.Engineer_Code &NBSP; AND thisYear。[User_Name_N2] = lastYear.User_Name_N2 &NBSP; AND thisYear。[Bill_To_Customer_Code] = lastYear.Bill_To_Customer_Code &NBSP; AND thisYear。[User_Country_Code] = lastYear.User_Country_Code &NBSP; AND thisYear。[User_N3_Country_Name] = lastYear.User_N3_Country_Name）AS QTY_UoM_YTD_prev， &NBSP; &NBSP;（SELECT SUM（thisYear.QTY）&NBSP;&NBSP;&NBSP; FROM cumulative_sum_for_ytd thisYear&NBSP;&NBSP; WHERE转换（INT，thisYear.Delivery_Year）=转化（INT，lastYear.Delivery_Year - 1）&NBSP;&NBSP;&NBSP;和（转换（INT，thisYear.Delivery_Month）LT;转换（INT，lastYear.Delivery_Month）&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;或转换（INT，thisYear.Delivery_Month）=转化（INT，lastYear.Delivery_Month）&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;和转换（INT，thisYear.Invoicing_Day）LT; - 转换（INT，lastYear.Invoicing_Day））&NBSP;&NBSP;&NBSP; AND thisYear [SALES_ORGANIZATION] = lastYear.Sales_Organization&NBSP;&NBSP;&NBSP; AND thisYear 。[Sales_Product_Name_N3] = lastYear.Sales_Product_Name_N3 AND thisYear。[Sales_Product_Name_N2] = lastYear。[Sales_Product_Name_N2] AND thisYear。[Sales_Product_Name_N1] = lastYear。[Sales_Product_Name_N1] AND thisYear。[Market_Segment_Name_N2] = lastYear。[Market_Segment_Name_N2] AND thisYear。[Doc_Currency] = lastYear.Doc_Currency&NBSP; &NBSP; AND thisYear。[User_Name_N1] = lastYear.User_Name_N1 &NBSP; AND thisYear。[Engineer_Code] = lastYear.Engineer_Code &NBSP; AND thisYear。[User_Name_N2] = lastYear.User_Name_N2 &NBSP; AND thisYear。[Bill_To_Customer_Code] = lastYear.Bill_To_Customer_Code &NBSP; AND thisYear。[User_Country_Code] = lastYear.User_Country_Code &NBSP; AND thisYear。[User_N3_Country_Name] = lastYear.User_N3_Country_Name）AS QTY_YTD_prev， &NBSP; （SELECT SUM（thisYear.AMOUNT_LCPNV） FROM cumulative_sum_for_ytd thisYear WHERE convert（int，thisYear.Delivery_Year）= convert（int，lastYear.Delivery_Year - 1） AND（ convert（int，thisYear.Delivery_Month）< convert（int，lastYear.Delivery_Month） OR convert（int，thisYear.Delivery_Month）= convert（int，lastYear.Delivery_Month） &NBSP;&NBSP;&NBSP;和转换（INT，thisYear.Invoicing_Day）LT; - 转换（INT，lastYear.Invoicing_Day））&NBSP;&NBSP;&NBSP; AND thisYear [SALES_ORGANIZATION] = lastYear.Sales_Organization&NBSP;&NBSP;&NBSP; AND thisYear 。[Sales_Product_Name_N3] = lastYear.Sales_Product_Name_N3 AND thisYear。[Sales_Product_Name_N2] = lastYear。[Sales_Product_Name_N2] AND thisYear。[Sales_Product_Name_N1] = lastYear。[Sales_Product_Name_N1] AND thisYear。[Market_Segment_Name_N2] = lastYear。[Market_Segment_Name_N2] AND thisYear。[Doc_C urrency] = lastYear.Doc_Currency&NBSP; &NBSP; AND thisYear。[User_Name_N1] = lastYear.User_Name_N1 &NBSP; AND thisYear。[Engineer_Code] = lastYear.Engineer_Code &NBSP; AND thisYear。[User_Name_N2] = lastYear.User_Name_N2 &NBSP; AND thisYear。[Bill_To_Customer_Code] = lastYear.Bill_To_Customer_Code &NBSP; AND thisYear。[User_Country_Code] = lastYear.User_Country_Code &NBSP; AND thisYear。[User_N3_Country_Name] = lastYear.User_N3_Country_Name）AS AMOUNT_LCPNV_prevFROM cumulative_sum_for_ytd lastYear
WITH grouped_by_date AS (SELECT * FROM tmp.FACT_EPV_SEFPRO_DC s), cumulative_sum_for_ytd AS (SELECT T.*, T1.QTY_UoM_YTD,t1.QTY_YTD,t1.AMOUNT_LCPNV FROM grouped_by_date T CROSS APPLY (SELECT SUM(QTY_UoM) AS QTY_UoM_YTD,SUM(QTY) AS QTY_YTD, SUM(PNV_LC)as AMOUNT_LCPNV FROM grouped_by_date WHERE [Delivery_Year]=T.[Delivery_Year] AND convert(date,Delivery_Year+'-'+ Delivery_month+'-'+ [Invoicing_Day])<=convert(date,T.Delivery_Year+'-'+ t.Delivery_month+'-'+ t.[Invoicing_Day]) AND [Sales_Organization]=T.Sales_Organization AND[Sales_Product_Name_N3]=T.Sales_Product_Name_N3 AND[Sales_Product_Name_N2]=T.[Sales_Product_Name_N2] AND[Sales_Product_Name_N1]=T.[Sales_Product_Name_N1] AND[Market_Segment_Name_N2]=T.[Market_Segment_Name_N2] AND[Doc_Currency]=T.Doc_Currency AND[User_Name_N1]=T.User_Name_N1 AND[Engineer_Code]=T.Engineer_Code AND[User_Name_N2]=T.User_Name_N2 AND[Bill_To_Customer_Code]=T.Bill_To_Customer_Code AND[User_Country_Code]=T.User_Country_Code AND[User_N3_Country_Name]=T.User_N3_Country_Name ) T1)SELECT lastYear.*, (SELECT SUM(thisYear.QTY_UoM) AS QTY_UoM_YTD_prev FROM cumulative_sum_for_ytd thisYear WHERE convert(int,thisYear.Delivery_Year) = convert(int,lastYear.Delivery_Year - 1) AND (convert(int,thisYear.Delivery_Month) < convert(int,lastYear.Delivery_Month) OR convert(int,thisYear.Delivery_Month) = convert(int,lastYear.Delivery_Month) AND convert(int,thisYear.Invoicing_Day) <- convert(int,lastYear.Invoicing_Day)) AND thisYear.[Sales_Organization]=lastYear.Sales_Organization AND thisYear.[Sales_Product_Name_N3]=lastYear.Sales_Product_Name_N3 AND thisYear.[Sales_Product_Name_N2]=lastYear.[Sales_Product_Name_N2] AND thisYear.[Sales_Product_Name_N1]=lastYear.[Sales_Product_Name_N1] AND thisYear.[Market_Segment_Name_N2]=lastYear.[Market_Segment_Name_N2] AND thisYear.[Doc_Currency]=lastYear.Doc_Currency AND thisYear.[User_Name_N1]=lastYear.User_Name_N1 AND thisYear.[Engineer_Code]=lastYear.Engineer_Code AND thisYear.[User_Name_N2]=lastYear.User_Name_N2 AND thisYear.[Bill_To_Customer_Code]=lastYear.Bill_To_Customer_Code AND thisYear.[User_Country_Code]=lastYear.User_Country_Code AND thisYear.[User_N3_Country_Name]=lastYear.User_N3_Country_Name )AS QTY_UoM_YTD_prev, (SELECT SUM(thisYear.QTY) FROM cumulative_sum_for_ytd thisYear WHERE convert(int,thisYear.Delivery_Year) = convert(int,lastYear.Delivery_Year - 1) AND (convert(int,thisYear.Delivery_Month) < convert(int,lastYear.Delivery_Month) OR convert(int,thisYear.Delivery_Month) = convert(int,lastYear.Delivery_Month) AND convert(int,thisYear.Invoicing_Day) <- convert(int,lastYear.Invoicing_Day)) AND thisYear.[Sales_Organization]=lastYear.Sales_Organization AND thisYear.[Sales_Product_Name_N3]=lastYear.Sales_Product_Name_N3 AND thisYear.[Sales_Product_Name_N2]=lastYear.[Sales_Product_Name_N2] AND thisYear.[Sales_Product_Name_N1]=lastYear.[Sales_Product_Name_N1] AND thisYear.[Market_Segment_Name_N2]=lastYear.[Market_Segment_Name_N2] AND thisYear.[Doc_Currency]=lastYear.Doc_Currency AND thisYear.[User_Name_N1]=lastYear.User_Name_N1 AND thisYear.[Engineer_Code]=lastYear.Engineer_Code AND thisYear.[User_Name_N2]=lastYear.User_Name_N2 AND thisYear.[Bill_To_Customer_Code]=lastYear.Bill_To_Customer_Code AND thisYear.[User_Country_Code]=lastYear.User_Country_Code AND thisYear.[User_N3_Country_Name]=lastYear.User_N3_Country_Name )AS QTY_YTD_prev, (SELECT SUM(thisYear.AMOUNT_LCPNV) FROM cumulative_sum_for_ytd thisYear WHERE convert(int,thisYear.Delivery_Year) = convert(int,lastYear.Delivery_Year - 1) AND (convert(int,thisYear.Delivery_Month) < convert(int,lastYear.Delivery_Month) OR convert(int,thisYear.Delivery_Month) = convert(int,lastYear.Delivery_Month) AND convert(int,thisYear.Invoicing_Day) <- convert(int,lastYear.Invoicing_Day)) AND thisYear.[Sales_Organization]=lastYear.Sales_Organization AND thisYear.[Sales_Product_Name_N3]=lastYear.Sales_Product_Name_N3 AND thisYear.[Sales_Product_Name_N2]=lastYear.[Sales_Product_Name_N2] AND thisYear.[Sales_Product_Name_N1]=lastYear.[Sales_Product_Name_N1] AND thisYear.[Market_Segment_Name_N2]=lastYear.[Market_Segment_Name_N2] AND thisYear.[Doc_Currency]=lastYear.Doc_Currency AND thisYear.[User_Name_N1]=lastYear.User_Name_N1 AND thisYear.[Engineer_Code]=lastYear.Engineer_Code AND thisYear.[User_Name_N2]=lastYear.User_Name_N2 AND thisYear.[Bill_To_Customer_Code]=lastYear.Bill_To_Customer_Code AND thisYear.[User_Country_Code]=lastYear.User_Country_Code AND thisYear.[User_N3_Country_Name]=lastYear.User_N3_Country_Name )AS AMOUNT_LCPNV_prevFROM cumulative_sum_for_ytd lastYear
推荐答案
美好的一天 jaweher89 ，
优化一个数据库的查询，并不意味着它将与不同的数据库相关。
Optimize a query for one database, does not mean that it will be relevant to a different database.
为了讨论查询性能，提供工具来重现与您的方案一样接近的方案以及有关如何执行方案的信息非常重要。您的系统。换句话说，我们需要您提供：
In order to discuss on query performance it is important to provide the tools to reproduce a scenario as close as yours and information about how it is executes in your system.in other words we need you to provide:
（1）查询以创建相关表格并插入一些示例数据
(1) queries to create the relevant table(s) and to insert some sample data
（2）哟u执行计划（完整执行计划的XML而不是图像）
(2) you execution plan (XML of the full execution Plan and not image)
同时：
1。尽快在你的查询中使用*！
1. Get ride of all the use of * in your queries ASAP!!!
2。以可读格式发布您的查询，而不是一行
2. post your queries in a readable format and not in one line
3。尽量避免在过滤条件下使用的列上使用操作，因为这会导致数据的完全扫描（服务器首先需要在检查条件之前对值进行计算
3. try to avoid using manipulation on the column that you usein the filter condition, since this lead to full scan ofthe data(the server needfirst to calcultae the value before it can check the condition
例如如果X是INT，那么而不是使用：WHERE CONVERT（CHAR（2），X）='2'
你应该使用：WHERE X = CONVERT（INT，'2'）
For example if X is INT then instead of using: WHERE CONVERT(CHAR(2), X) = '2'
You should use: WHERE X = CONVERT(INT, '2')
上面例子中的计算只进行一次，服务器可以在X列上使用INDEX。
The calculation in my example above is done only once, and the server can use INDEX on the column X for example.
4.尽量避免这么多条件在您的查询中。您可能需要重新设计数据库和JOIN表而不是使用WHERE中的条件
4. Try to avoid so many conditions in your query. You might need to re-design the database and JOIN tables instead of using conditions in the WHERE
...
这些是乍一看可能与你有关的一般要点，但在继续前进之前我们需要得到上面提到的信息
These are general points which in first glance probably relevant to you, but wewill needto get the information which I asked above, before moving forward
这篇关于优化计算年初至今和上一年的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
