改进存储过程中的完整 sql 查询 [英] Improving complete sql query in stored proc
问题描述
我有一个存储过程，用于在 WPF 中填充像网格这样的仪表板.随着时间的推移，我已经改进了查询，老实说，性能可能会更好.我现在不知道有什么好的方法可以改进查询，我可以通过执行所需的时间来提高性能.
I have a stored proc that is used to populate a dashboard like grid in WPF. I've refined the query over time, and to be honest, the performance could be better. I'm at the point now that I do not know any good ways to improve the query where I can gain performance with the time it takes to execute.
任何帮助将不胜感激.下面是存储过程:
Any help would be greatly appreciated. Below is the stored proc:
ALTER PROCEDURE [dbo].[spGetDashboardMainNew]
AS
BEGIN
SET NOCOUNT ON;
SELECT JC.job_number AS SalesOrder,
dbo.cust.Company AS Customer,
JC.Plan_ship_date AS PlannedShipDate,
JC.latest_ship_date AS LatestShipDate,
JC.planned_fab_complete AS PlanFabComplete,
JC.fldPromisedDate AS CommittedShipDate,
JC.Materials_of_Construction AS MatofCons,
JC.Fab_rating AS FabRating,
JC.ass_rating AS [Assembly Rating],
JC.shipped AS OpenShipped,
JC.electrical_status,
JC.dp_credit_status,
JC.fldCustRequestDate,
JC.commercial_terms,
JC.Approved_by,
fldLiquidatedDamages,
fldLiquidatedDamagesDesc,
CASE
WHEN hot_list = '0'
OR hot_list = 'No'
THEN 'No'
ELSE 'Yes'
END AS HotList,
CASE
WHEN JC.latest_ship_date IS NOT NULL
AND JC.fldPromisedDate IS NULL
THEN JC.latest_ship_date
ELSE JC.fldPromisedDate
END AS CommitDate,
CASE
WHEN dp_credit_status = 1
AND commercial_terms = 'Complete'
THEN 1
ELSE 0
END AS TermsMet,
CASE
WHEN JC.fldPromisedDate IS NULL
THEN 1
ELSE 0
END AS SortByDate,
dbo.tblShippingInfo.fldShipmentRequestID,
CASE
WHEN JC.fleInspectionType <> 'None'
THEN JC.fleInspectionType
ELSE NULL
END AS InspectionType,
JC.fldInspectionNotes,
JC.DueDateExtraTime,
CASE
WHEN JC.fleInspectionType <> 'None'
AND JC.fleInspectionType IS NOT NULL
THEN 1
ELSE 0
END AS InspectionDesc,
advanced_buying,
SortedOrder,
SalesOrderStatusGroup,
SalesOrderStatus,
SpecialOpList,
OperationDueBy,
test,
wpStatus,
SpecialOpsCount,
InChangeOrder,
RequiresSpecialPaint
FROM dbo.cust
INNER JOIN dbo.Job_Control JC ON dbo.cust.CUST# = JC.Cust#
LEFT OUTER JOIN dbo.tblShippingInfo ON JC.job_number = dbo.tblShippingInfo.fldJobNumberID
OUTER APPLY
(
SELECT dbo.udfGetManStatusFullSortOrder(JC.job_number) AS SortedOrder
) acolumn
OUTER APPLY
(
SELECT ISNULL(dbo.udfGetManStatusBySOGroupNo(JC.job_number, 1), dbo.udfGetManStatusNew(JC.job_number)) AS SalesOrderStatusGroup
) bcolumn
OUTER APPLY
(
SELECT ISNULL(dbo.udfGetManStatusBySOGroupNo(JC.job_number, 1), dbo.udfGetManStatusNew(JC.job_number)) AS SalesOrderStatus
) ccolumn
OUTER APPLY
(
SELECT dbo.udfGetSpecialOperations(JC.job_number) AS SpecialOpList
) dcolumn
OUTER APPLY
(
SELECT CASE
WHEN dbo.udfGetManStatusNew(JC.job_number) LIKE '%Approval%' OR dbo.udfGetManStatusNew(JC.job_number) LIKE '%Re-Approval%'
THEN JC.Approval_Due
ELSE dbo.udfGetDueByPerOperation(JC.job_number, 1)
END AS OperationDueBy
) ecolumn
OUTER APPLY
(
SELECT dbo.udfGetGroupCount(JC.job_number) AS test
) fcolumn
OUTER APPLY
(
SELECT dbo.udfGetOpenWorkPackagesByDesigner(JC.job_number) AS wpStatus
) gcolumn
OUTER APPLY
(
SELECT dbo.udfGetSpecialOpsCount(JC.job_number) AS SpecialOpsCount
) hcolumn
OUTER APPLY
(
SELECT dbo.udfIsActiveChangeOrder(JC.job_number) AS InChangeOrder
) icolumn
OUTER APPLY
(
SELECT dbo.udfRequiresSpecialColor(JC.job_number) AS RequiresSpecialPaint
) jcolumn
WHERE(JC.shipped = 'Open')
OR (JC.shipped = 'Hold');
END;
以下是调用的外部应用函数:
Below are the outter apply functions that are called:
ALTER FUNCTION [dbo].[udfGetManStatusFullSortOrder]
(
@SalesOrderNumber int
)
RETURNS INT
AS
BEGIN
DECLARE @shipped nvarchar(50);
DECLARE @eng_complete datetime;
DECLARE @Drawing_Info nvarchar(50);
DECLARE @Release_to_engineering datetime;
DECLARE @Dwg_Sent datetime;
DECLARE @Approval_done datetime;
DECLARE @Detail bit;
DECLARE @Release_to_shop datetime;
DECLARE @OperationCount int;
DECLARE @SortOrder INT;
DECLARE @p as INT = 1;
DECLARE @NonOpSortOrder INT;
SET @NonOpSortOrder = dbo.udfIsOnHoldSort(@SalesOrderNumber)
IF @NonOpSortOrder > 0
RETURN @nonopsortorder
SELECT @SortOrder = (SELECT TOP(@p) dbo.ShopRouting.DashboardSortOrder
FROM dbo.Product INNER JOIN
dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
WHERE (dbo.Product.Job_number = @SalesOrderNumber) AND (dbo.MfgGrouping.GroupingNumber = 1) AND (dbo.GroupOperations.Completed IS NULL)
GROUP BY dbo.ShopRouting.Description, dbo.GroupOperations.NewSortOrder, dbo.ShopRouting.DashboardSortOrder
ORDER BY dbo.GroupOperations.NewSortOrder);
IF @SortOrder IS NOT NULL
RETURN @SortOrder
SELECT
@shipped = shipped,
@eng_complete = eng_complete,
@Drawing_Info = Drawing_Info,
@Release_to_engineering = Release_to_engineering,
@Dwg_Sent = Dwg_Sent,
@Approval_done = Approval_done,
@Detail = Detail,
@Release_to_shop = Release_to_shop
FROM
job_control
WHERE
job_number = @SalesOrderNumber
BEGIN
SET @OperationCount = flex2ksql.dbo.udfGetGroupOperationsCount(@SalesOrderNumber);
END
IF (@shipped = 'Hold')
RETURN 33
IF (@eng_complete IS NULL)
BEGIN
IF (@Release_to_engineering IS NULL)
RETURN 32
IF (@Drawing_Info = 'Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NOT NULL AND
@Approval_done IS NULL)
RETURN 28
IF (@Drawing_Info = 'Re-Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NOT NULL AND
@Approval_done IS NULL)
RETURN 29
IF (@Drawing_Info = 'Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NOT NULL AND
@Detail = 1 AND
@Approval_done IS NOT NULL)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 27
ELSE
RETURN 26
IF (@Drawing_Info = 'Approval' AND
@Release_to_engineering IS NOT NULL)
RETURN 30
IF (@Drawing_Info = 'Re-Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NULL AND
@Approval_done IS NULL)
RETURN 31
IF (@Drawing_Info = 'Certified' AND
@Release_to_engineering IS NOT NULL AND
@Detail = 1)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 27
ELSE
RETURN 26
IF (@Drawing_Info = 'No Drawing Required' AND
@Release_to_engineering IS NOT NULL)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 27
ELSE
RETURN 26
IF (@Drawing_Info = 'Certified')
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 27
ELSE
RETURN 26
IF (@Drawing_Info = 'Sales Drawing' AND
@Release_to_engineering IS NOT NULL AND
@Release_to_shop IS NOT NULL)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 27
ELSE
RETURN 26
ELSE
RETURN 27
END
ELSE
BEGIN
RETURN 27
END
RETURN 99
ALTER FUNCTION [dbo].[udfGetManStatusBySOGroupNo]
(
@SalesOrder int, @GroupNo int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @ManStatus varchar(100);
DECLARE @p as INT = 1;
DECLARE @NonOpSortOrder INT;
SET @NonOpSortOrder = dbo.udfIsOnHoldSort(@SalesOrder)
IF @NonOpSortOrder > 0
RETURN 'Hold'
ELSE
SELECT @ManStatus = (SELECT TOP(@p) dbo.ShopRouting.Description
FROM dbo.Product INNER JOIN
dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
WHERE (dbo.Product.Job_number = @SalesOrder) AND (dbo.MfgGrouping.GroupingNumber = @GroupNo) AND (dbo.GroupOperations.Completed is null)
GROUP BY dbo.ShopRouting.Description, dbo.Product.Model_NO, dbo.GroupOperations.NewSortOrder
ORDER BY dbo.GroupOperations.NewSortOrder);
RETURN @ManStatus
ALTER FUNCTION [dbo].[udfGetManStatusNew]
(
@SalesOrderNumber int
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @shipped nvarchar(50);
DECLARE @eng_complete datetime;
DECLARE @Drawing_Info nvarchar(50);
DECLARE @Release_to_engineering datetime;
DECLARE @Dwg_Sent datetime;
DECLARE @Approval_done datetime;
DECLARE @Detail bit;
DECLARE @Release_to_shop datetime;
DECLARE @OperationCount int;
SELECT
@shipped = shipped,
@eng_complete = eng_complete,
@Drawing_Info = Drawing_Info,
@Release_to_engineering = Release_to_engineering,
@Dwg_Sent = Dwg_Sent,
@Approval_done = Approval_done,
@Detail = Detail,
@Release_to_shop = Release_to_shop
FROM
job_control
WHERE
job_number = @SalesOrderNumber
BEGIN
SET @OperationCount = flex2ksql.dbo.udfGetGroupOperationsCount(@SalesOrderNumber);
END
IF (@shipped = 'Hold')
RETURN 'Hold'
IF (@eng_complete IS NULL)
BEGIN
IF (@Release_to_engineering IS NULL)
RETURN 'Not Released'
IF (@Drawing_Info = 'Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NOT NULL AND
@Approval_done IS NULL)
RETURN 'Approval Out'
IF (@Drawing_Info = 'Re-Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NOT NULL AND
@Approval_done IS NULL)
RETURN 'Re-Approval Out'
IF (@Drawing_Info = 'Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NOT NULL AND
@Detail = 1 AND
@Approval_done IS NOT NULL)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 'Routing'
ELSE
RETURN 'Detail'
IF (@Drawing_Info = 'Approval' AND
@Release_to_engineering IS NOT NULL)
RETURN 'Approval To Be Done'
IF (@Drawing_Info = 'Re-Approval' AND
@Release_to_engineering IS NOT NULL AND
@Dwg_Sent IS NULL AND
@Approval_done IS NULL)
RETURN 'Re-Approval To Be Done'
IF (@Drawing_Info = 'Certified' AND
@Release_to_engineering IS NOT NULL AND
@Detail = 1)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 'Routing'
ELSE
RETURN 'Detail'
IF (@Drawing_Info = 'No Drawing Required' AND
@Release_to_engineering IS NOT NULL)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 'Routing'
ELSE
RETURN 'Detail'
IF (@Drawing_Info = 'Certified')
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 'Routing'
ELSE
RETURN 'Detail'
IF (@Drawing_Info = 'Sales Drawing' AND
@Release_to_engineering IS NOT NULL AND
@Release_to_shop IS NOT NULL)
If (@OperationCount) = 0 OR (@OperationCount IS NULL)
RETURN 'Routing'
ELSE
RETURN 'Detail'
ELSE
RETURN 'Routing'
END
ELSE
BEGIN
RETURN 'Routing'
END
RETURN NULL
ALTER FUNCTION [dbo].[udfGetSpecialOperations]
(
@SalesOrder int
)
RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @returnVal nvarchar(MAX)
SELECT @returnVal = Stuff((SELECT N', ' + dbo.ShopRouting.Description FROM dbo.Product INNER JOIN
dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
WHERE (dbo.ShopRouting.Standard = 0) AND (dbo.Product.Job_number = @SalesOrder)
GROUP BY dbo.ShopRouting.Description, dbo.ShopRouting.DashboardSortOrder
ORDER BY dbo.ShopRouting.DashboardSortOrder DESC
FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')
RETURN @returnVal
ALTER FUNCTION [dbo].[udfGetDueByPerOperation]
(
@SalesOrder int, @GroupNo int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @ManStatus varchar(100);
DECLARE @p as INT = 1;
SELECT @ManStatus = (SELECT TOP(@p) dbo.GroupOperations.StartBy
FROM dbo.Product INNER JOIN
dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
WHERE (dbo.Product.Job_number = @SalesOrder) AND (dbo.MfgGrouping.GroupingNumber = @GroupNo) AND (dbo.GroupOperations.Completed IS NULL) AND (dbo.GroupOperations.Active = 0)
GROUP BY dbo.ShopRouting.[Description], dbo.Product.Model_NO, dbo.GroupOperations.NewSortOrder, dbo.GroupOperations.StartBy
ORDER BY dbo.GroupOperations.NewSortOrder);
RETURN @ManStatus
ALTER FUNCTION [dbo].[udfGetGroupCount]
(
@SalesOrder int
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @result varchar(MAX)
DECLARE @result2 varchar(MAX)
SET @result = ''
SELECT @result = @result + Description + ': ' + Cast(StatusCount as varchar(5)) + ', ' FROM vwLineItemGroupStatusCounts WHERE Job_number = @SalesOrder
ORDER BY vwLineItemGroupStatusCounts.NewSortOrder
SELECT @result2 = substring(@result, 0, len(@result)) --trim extra "," at end
-- Return the result of the function
RETURN @result2
ALTER FUNCTION [dbo].[udfGetOpenWorkPackagesByDesigner]
(
@SalesOrder int
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @result varchar(MAX)
DECLARE @result2 varchar(MAX)
SET @result = ''
--SELECT @result = @result + Fldusername + ': ' + Cast(StatusCount as varchar(5)) + ', ' FROM vwOpenWorkPackagesByDesigner WHERE Job_number = @SalesOrder
SELECT @result = @result + eng_difficulty_category + ' ' FROM vwOpenWorkPackagesEngRating WHERE job_number = @SalesOrder
SELECT @result = @result + Fldusername + ' ' + Cast(coalesce(fldPriority,'none') as varchar(5)) + ' ' + cast(PercentComplete as varchar(10)) + '% ' + fldTaskCategoryAbbr + '; ' FROM vwOpenWorkPackagesByDesigner WHERE fldSalesOrder = @SalesOrder
SELECT @result2 = substring(@result, 0, len(@result)) --trim extra "," at end
-- Return the result of the function
RETURN @result2
ALTER FUNCTION [dbo].[udfGetSpecialOpsCount]
(
@SalesOrder int
)
RETURNS int
AS
BEGIN
DECLARE @returnVal int;
SELECT @returnVal = COUNT(dbo.GroupOperations.GroupOperationID)
FROM dbo.Product INNER JOIN
dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
WHERE (dbo.ShopRouting.Standard = 0) AND (dbo.Product.Job_number = @SalesOrder) AND (dbo.ShopRouting.ShopRoutingID NOT IN (15,16))
If (@returnVal) = 0 OR (@returnVal IS NULL)
SET @returnVal = 0;
Return @returnVal;
ALTER FUNCTION [dbo].[udfIsActiveChangeOrder] (@SalesOrder numeric(18, 0))
RETURNS Bit
AS
BEGIN
DECLARE @isCO Bit
DECLARE @SOFound as numeric(18)
SET @SOFound = 0
BEGIN
SELECT
@SOFound = fldSalesOrder
FROM
[flex2kSQL].[dbo].[vwChangeOrdersAllActive]
WHERE
fldSalesOrder = @SalesOrder
END
if @SOFound =0
SET @isCO=0
else
SET @isCO=1
RETURN @isCO
ALTER FUNCTION [dbo].[udfRequiresSpecialColor]
(
@SalesOrder INT
)
RETURNS INT
AS
BEGIN
DECLARE @ReturnVal int;
IF EXISTS (SELECT 1
FROM dbo.MfgGrouping INNER JOIN
dbo.ProductMfgGrouping ON dbo.MfgGrouping.MfgGroupingID = dbo.ProductMfgGrouping.MfgGroupingID INNER JOIN
dbo.Product ON dbo.ProductMfgGrouping.Record_no = dbo.Product.Record_no INNER JOIN
dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID
WHERE (dbo.Product.Job_number = @SalesOrder) AND (dbo.GroupOperations.ShopRoutingID IN (23, 24, 25)))
BEGIN
SET @ReturnVal = 1
END
ELSE
BEGIN
SET @ReturnVal = 0
END
Return @ReturnVal;
下面是执行存储过程的一行示例数据(删除了除测试订单之外的每一行):
Below is a row of sample data from executing the stored proc (removed every row but test order):
SalesOrder客户PlannedShipDate LatestShipDate PlanFabComplete CommittedShipDate MatofCons FabRating大会评分OpenShipped electrical_status dp_credit_status fldCustRequestDate commercial_terms Approved_by fldLiquidatedDamages fldLiquidatedDamagesDesc好友名单CommitDate TermsMet SortByDate fldShipmentRequestID InspectionType fldInspectionNotes DueDateExtraTime InspectionDesc advanced_buying SortedOrder SalesOrderStatusGroup SalesOrderStatus SpecialOpList OperationDueBy测试wpStatus SpecialOpsCount InChangeOrder RequiresSpecialPaint76506 测试客户 6/16/2020 12/31/2020 NULL 11/28/2016 SS CC Open In Test 0 9/29/2016 NULL 预安装/预接线 0 NULL 是 11/28/2016 0 0 NULL NULL NULL 完成 NULL 7 026 细节细节 NULL 6/2/2020 细节:6 NULL 0 0 0
SalesOrder Customer PlannedShipDate LatestShipDate PlanFabComplete CommittedShipDate MatofCons FabRating Assembly Rating OpenShipped electrical_status dp_credit_status fldCustRequestDate commercial_terms Approved_by fldLiquidatedDamages fldLiquidatedDamagesDesc HotList CommitDate TermsMet SortByDate fldShipmentRequestID InspectionType fldInspectionNotes DueDateExtraTime InspectionDesc advanced_buying SortedOrder SalesOrderStatusGroup SalesOrderStatus SpecialOpList OperationDueBy test wpStatus SpecialOpsCount InChangeOrder RequiresSpecialPaint 76506 Test Customer 6/16/2020 12/31/2020 NULL 11/28/2016 SS C C Open In Test 0 9/29/2016 NULL Premount/Prewire 0 NULL Yes 11/28/2016 0 0 NULL NULL NULL 7 0 Complete 26 Detail Detail NULL 6/2/2020 Detail: 6 NULL 0 0 0
最后，下面是一个屏幕截图，显示了数据在主窗体上的显示和组织方式.
Lastly, below is a screen shot showing how the data is displayed and organized on the main form.
--编辑--将调用的函数更改为内联表函数后的最新查询执行计划.最新查询执行计划
--EDIT-- Latest query execution plan after changing functions called to inline table functions. Latest Query Execution Plan
推荐答案
查询优化与其说是一门科学，不如说是一门艺术.
Query optimisation is more of an art form than a science.
突出的是，您使用的许多函数都是以命令式风格编写的.作为起点，我将调查针对函数调用的 OUTER APPLY 是否可以重写为针对视图的 LEFT JOIN(内联编写，如果函数对于此报告是唯一的，或者针对预定义的视图).
What stands out is that many of the functions you've employed are written in an imperative style. As a starting point, I would investigate whether the OUTER APPLYs against function calls can be rewritten as LEFT JOINs against views (either written inline, if the functions are unique to this report, or against a pre-defined view).
例如，函数 udfRequiresSpecialColor 可以重写为:
So for example, the function udfRequiresSpecialColor can be rewritten as:
SELECT
dbo.Product.Job_number
FROM
dbo.MfgGrouping
INNER JOIN
dbo.ProductMfgGrouping
ON dbo.MfgGrouping.MfgGroupingID = dbo.ProductMfgGrouping.MfgGroupingID
INNER JOIN
dbo.Product
ON dbo.ProductMfgGrouping.Record_no = dbo.Product.Record_no
INNER JOIN
dbo.GroupOperations
ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID
AND (dbo.GroupOperations.ShopRoutingID IN (23, 24, 25))
这将返回一个包含所有 Job_number 需要特殊颜色的表，可以连接.
This returns a single table containing all Job_numbers that require a special colour, which can be joined against.
然后，在主查询中，替换如下 OUTER APPLY:
Then, in the main query, the following OUTER APPLY is replaced:
SELECT
...
RequiresSpecialPaint
...
OUTER APPLY
(
SELECT dbo.udfRequiresSpecialColor(JC.job_number) AS RequiresSpecialPaint
) AS jcolumn
...并在主查询中用 LEFT JOIN 替换:
...and is replaced with a LEFT JOIN in the main query:
SELECT
...
IIF(jobs_requiring_special_paint.job_number IS NOT NULL, 1, 0) AS RequiresSpecialPaint
...
LEFT JOIN
(
[here you can either a call to the new udfRequiresSpecialColor defined as a database view or an inlinable function, or simply include the code above as a subquery]
) AS jobs_requiring_special_paint
ON (JC.job_number = jobs_requiring_special_paint.job_number)
通过左连接到这个表，那些有特殊油漆的工作会有一个 job_number，那些没有特殊油漆的工作会有一个 NULL 值 - 然后在 select 语句中转换为 0/1 标志, 而不是子功能的一部分.
By left-joining onto this table, there will be a job_number for those jobs that have special paint, and a NULL value for those jobs that don't - that is then converted to a 0/1 flag in the select statement, rather than being part of the sub-function.
您获得了大致情况，并且您可以看到处理获取特殊油漆数据的整个查询部分现在以纯粹基于集合的方式处理.
You get the general picture, and you can see that the entire part of the query that deals with getting the special paint data, is now dealt with in a purely set-based fashion.
删除命令式代码为查询优化器提供了更多的空间来分析和重新组织查询，以更有效地执行但保留您定义的逻辑.
Removing imperative code gives the query optimiser more latitude to analyse and reorganise the query in a way that executes more efficiently but preserves the logic you've defined.
您最终可能还会发现，当前各种单独的 OUTER APPLY 查询可以浓缩为相关表关系的单个通用定义(或更少的通用定义)，并在 ON 子句中应用必要的过滤.
You may also find ultimately that what are currently a variety of separate OUTER APPLY queries can be condensed into a single common definition (or fewer common definitions) of the relevant table relationships, with the necessary filtering applied in the ON clause.
例如，udfGetDueByPerOperation 似乎命中与 udfRequiresSpecialColor 相同的表(这些表是 MfgGrouping、ProductMfgGrouping、Product、GroupOperations)，并且在相同的基本关系中(相同键上的 INNER JOIN)，但在末尾添加 ShopRouting.
For example, udfGetDueByPerOperation seems to hit the same tables as udfRequiresSpecialColor (those tables being MfgGrouping, ProductMfgGrouping, Product, GroupOperations), and in the same basic relationships (INNER JOINs on the same keys), but with ShopRouting tacked on the end.
因此可以定义公共代码(作为数据库视图或内联函数，或作为公共表表达式/WITH 子句)，并在最终 JOIN 子句中针对公共表实施过滤.这可能会使代码更短，更易于理解和维护，并且它可能再次帮助查询引擎优化其计划.如果需要，我可以更多地讨论如何应用这种方法.
It might therefore be possible to define common code (either as a database view or inline function, or as a common table expression/WITH clause), and the filtering implemented in the final JOIN clauses against the common table. This is likely to make the code far shorter and easier to understand and maintain, and again it possibly helps the query engine optimise its plan. I can talk more about how this approach would be applied if required.
我注意到有一些函数，例如 udfGetManStatusNew，其决策逻辑相对复杂，在保持清晰和正确性的同时，可能难以简化为基于表达式的计算.
I note that there are some functions, such as udfGetManStatusNew, that have relatively complex decision logic, which might be difficult to reduce to an expression-based calculation whilst retaining clarity and correctness.
同样，即使作为没有从函数中完全消除命令式代码的部分解决方案，如果以给定一个表 在所有 job_numbers 中，点击一次 job_control 表以获取所有相关作业的详细信息，使用 OUTER APPLY 为每个作业点击 udfGetGroupOperationsCount 函数(或使用所描述的技术)将 OUTER APPLY 减少到 LEFT JOIN)并捕获 @OperationCount 作为附加列(而不是分配给标量变量).然后，一旦以这种方式将函数的成分"收集到单个表中，然后遍历命令式代码以获得每个作业状态的必要计算).
Again though, even as a partial solution without completely eliminating imperative code from the function, it is likely to be far more efficient if such a function is rewritten in such a way that it is given a table of all job_numbers, hitting the job_control table once for the details of all relevant jobs, using an OUTER APPLY to hit the udfGetGroupOperationsCount function for each job (or using the techniques described above to reduce the OUTER APPLY to a LEFT JOIN) and capturing @OperationCount as an additional column (instead of assigning to a scalar variable). Then, once the 'ingredients' of the function have been gathered in into a single table this way, then iterate through the imperative code to derive the necessary calculations for the status of each job).
我不想预先判断您将从这种通用方法中获得的结果，因为查询引擎可能是不可预测的，但我怀疑如果可以改进，它应该会提高性能.
I don't want to pre-judge the results that you will get from this general approach, because query engines can be unpredictable, but my suspicion is that it should improve performance, if it can be improved.
如果您能够了解查询的哪些部分对其性能不佳的贡献最大(假设它并非始终缓慢)，这也可能会有所帮助.
It might also help if you can get an idea of which parts of the query are contributing most to its poor performance - assuming it is not uniformly slow.
如果您需要更多指导，请尽管提出.如果您对此感到高兴，也请告诉我.
If you need any more guidance, ask away. Let me know also if you have any joy with this.
更多信息，基于以下评论.
Further information, based on comments below.
我刚刚意识到我误解了您发布的查询计划 - 因为这些函数是必不可少的，它不显示它们的查询计划，也不包括它们的成本.如果将它们重写为可内联函数，请发布另一个查询计划，该计划将开始显示查询计划和函数本身的全部成本(目前它们作为低成本常量扫描出现在查询计划中，没有进一步的计划信息).
I've just realised that I was misinterpreting the query plan that you posted - because the functions are imperative, it doesn't show the query plan for them, and does not include their cost. If you rewrite them as inlinable functions, post another query plan, which will start to show the query plan and the full cost of the functions themselves (at the moment they appear on the query plan as low-cost constant scans, with no further plan information).
命令式/多语句函数和内联函数之间的区别如下所示(注意避免混淆，我在 SQL Server 中有一个 DUAL 表，以反映默认内置的 Oracle 功能):
The difference between an imperative/multi-statement function, and an inlinable function looks like this (note to avoid confusion, I have a DUAL table in SQL Server, to mirror the Oracle functionality which is built-in by default):
CREATE FUNCTION [dbo].[fxn_imperative]
(
@dummy VARCHAR(1)
)
RETURNS INT
AS
BEGIN
DECLARE @ReturnVal int;
IF EXISTS (SELECT 1 FROM DUAL WHERE DUMMY = @dummy)
BEGIN
SET @ReturnVal = 1
END
ELSE
BEGIN
SET @ReturnVal = 0
END
Return @ReturnVal;
END
-- called like so: SELECT dbo.fxn_imperative('X') AS dummy_exists;
CREATE FUNCTION [dbo].[fxn_inline]
(
@dummy VARCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
SELECT ISNULL( (SELECT 1 FROM DUAL WHERE DUMMY = @dummy), 0) AS dummy_exists
)
-- called like so: SELECT * FROM fxn_inline('X');
这些函数在功能上或多或少是等效的 - 但只有内联函数会出现在调用该函数的查询的查询计划中.
These functions are more or less equivalent in functionality - but only the inline one will appear on the query plan for the query which calls the function.
关于 LEFT JOIN 方法，在这种情况下，您不会将内部查询定义为函数 - 您将其定义为视图(或简单地将其内联编写为子查询)并加入它(使用 job_number 作为连接条件，作为将传递给原始函数的参数的替换).作为我的原始帖子的一部分，我已经在上面粗略地展示了如何应用这种方法.
Regards the LEFT JOIN approach, you don't define the inner query as a function in that case - you define it as a view (or simply write it inline as a subquery) and join onto it (using the job_number as the join condition, as the replacement for the parameter that would have been passed to the original function). I've already shown roughly how that approach is applied above as part of my original post.
如果我想了解现有查询是如何处理事物的(就调用 OUTER APPLYed 的函数而言)，我将定义如下函数(我假设底层查询是特殊的对于每个作业/销售订单，paint 仅返回一行):
If I wanted to go with the grain of how your existing query is approaching things (in terms of calls to functions which are OUTER APPLYed), I'd define the function as follows (I'm assuming the underlying query for special paint only ever returns a single row per job/salesorder):
CREATE FUNCTION [dbo].[udfRequiresSpecialColor2]
(
@SalesOrder INT
)
RETURNS TABLE
AS
RETURN
(
WITH special_paint_subquery AS
(
SELECT
dbo.Product.Job_number
FROM
dbo.MfgGrouping
INNER JOIN
dbo.ProductMfgGrouping
ON dbo.MfgGrouping.MfgGroupingID = dbo.ProductMfgGrouping.MfgGroupingID
INNER JOIN
dbo.Product
ON dbo.ProductMfgGrouping.Record_no = dbo.Product.Record_no
INNER JOIN
dbo.GroupOperations
ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID
AND (dbo.GroupOperations.ShopRoutingID IN (23, 24, 25))
)
SELECT ISNULL( (SELECT 1 FROM special_paint_subquery WHERE dbo.Product.Job_number = @SalesOrder), 0) AS requires_special_paint
)
如果函数是这样编写的，那么如果您获得主查询的计划，就会显示该函数的计划.
If the function is written this way, the plan for the function will show up if you get a plan for the main query.
该函数的调用方式与您当前调用 udfRequiresSpecialColor 的方式相同 - 通过将其与作为参数传递的 job_number 交叉应用.
The function is called in the same way you currently call udfRequiresSpecialColor - by cross applying it with the job_number passed as a parameter.
一旦你以这种方式定义了函数，实际上将函数调用转换为左连接就变得微不足道了(WITH special_paint_subquery ... 部分进入视图定义，WHEREdbo.Product.Job_number = @SalesOrder 进入主查询中的连接条件，主查询的 SELECT 语句被修改为将 NULL 匹配转换为零，如我最初所示).
Once you have defined the function in this way, actually converting the function call to a left join becomes trivial (the WITH special_paint_subquery ... part goes into the view definition, the WHERE dbo.Product.Job_number = @SalesOrder goes into the join condition in the main query, and the SELECT statement of the main query is modified to translate a NULL match into a zero as I originally illustrated).
这篇关于改进存储过程中的完整 sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
