一段实在无能为力的优化

[yangchen8497]

各位大神你们好，我这遇到了个优化瓶颈，还望大家伸出援手，帮帮哈，多谢。
以下就是需要优化的SQL，和一些相关信息，如果有需要可以直接联系我进行远程分析
一、需要优化的SQL：
SELECT * FROM
(SELECT CASE[t0].[Owner_Org_RTK] WHEN N'PLANT'
THEN[t1].[PLANT_CODE] END AS [Owner_Org_ROid_CODE],
CASE[t0].[Owner_Org_RTK] WHEN N'PLANT'
THEN[t1].[PLANT_NAME] END AS [Owner_Org_ROid_NAME],
[t2].[DOC_NAME]AS [DOC_ID_DOC_NAME], [t0].[DOC_NO] AS [DOC_NO],
[t0].[SOURCE_ID_RTK]AS [RTK], [t0].[URGENT] AS [URGENT],
[t0].[DOC_DATE]AS [DOC_DATE], [t3].[ITEM_CODE] AS [ITEM_ID_ITEM_CODE],
[t0].[ITEM_DESCRIPTION]AS [ITEM_DESCRIPTION],
[t0].[ITEM_SPECIFICATION]AS [ITEM_SPECIFICATION],
[t4].[ITEM_FEATURE_CODE]AS [ITEM_FEATURE_CODE],
[t4].[ITEM_SPECIFICATION]AS [ITEM_FEATURE_ID_ITEM_SPECIFICATION],
[t3].[E_CODE]AS [E_CODE],
CASE[t3].[DRAWING_NO_FROM] WHEN N'1'
THEN[t3].[DRAWING_NO]
WHENN'2'
THEN[t4].[DRAWING_NO] END AS [DRAWING_NO],
[t0].[BOM_VERSION_TIMES]AS [BOM_VERSION_TIMES],
[t0].[BOM_DATE]AS [BOM_DATE], [t0].[STATUS] AS [STATUS],
[t0].[LOT_MO_FLAG]AS [LOT_MO_FLAG], [t0].[PLAN_QTY] AS [PLAN_QTY],
[t0].[REQ_QTY]AS [REQ_QTY], [t0].[COMPLETED_QTY] AS [COMPLETED_QTY],
[t0].[SCRAP_QTY]AS [SCRAP_QTY], [t0].[DESTROYED_QTY] AS [DESTROYED_QTY],
[t0].[LOT_MO_QTY]AS [LOT_MO_QTY],
[t5].[UNIT_NAME]AS [BUSINESS_UNIT_ID_UNIT_NAME],
CASE[t0].[SOURCE_ID_RTK] WHEN N'SUPPLIER'
THEN[t6].[SUPPLIER_NAME]
WHENN'WORK_CENTER'
THEN[t7].[WORK_CENTER_NAME]
ENDAS [SOURCE_ID_ROid],
[t8].[ADMIN_UNIT_NAME]AS [ADMIN_UNIT_ID_ADMIN_UNIT_NAME],
[t0].[ITEM_ROUTING_CONTROL]AS [ITEM_ROUTING_CONTROL],
[t9].[ROUTING_DES]AS [ITEM_ROUTING_ID_ROUTING_CODE],
[t0].[RECEIPT_REQ_CONTROL]AS [RECEIPT_REQ_CONTROL],
[t0].[PLAN_START_DATE]AS [PLAN_START_DATE],
[t0].[PLAN_COMPLETE_DATE]AS [PLAN_COMPLETE_DATE],
[t0].[ACTUAL_START_DATE]AS [ACTUAL_START_DATE],
[t0].[ACTUAL_COMPLETE_DATE]AS [ACTUAL_COMPLETE_DATE],
[t0].[PLAN_LOT]AS [PLAN_LOT], [t10].[DOC_NO] AS [SOURCE_MO_ID_DOC_NO],
[t11].[DOC_NO]AS [DOC_NO1],
[t12].[DOC_NO]AS [PARA_MO_ID_DOC_NO],
[t13].[ADMIN_UNIT_NAME]AS [Owner_Dept_ADMIN_UNIT_NAME],
[t14].[EMPLOYEE_NAME]AS [Owner_Emp_EMPLOYEE_NAME],
[t0].[REMARK]AS [REMARK], [t0].[ApproveStatus] AS [APPROVESTATUS],
[t0].[EFNETStatus]AS [EFNETStatus2], [t0].[MO_ID] AS [MO_ID],
[t0].[EFNETStatus]AS [EFNETStatus],
[t0].[ProcessInstanceId]AS [Sys_Qc_ProcessInstanceId],
[t0].[ApproveStatus]AS [Sys_Qc_ApproveStatus],
ISNULL(LEN(SUBSTRING([t0].[Attachments],0,2)),0)AS [Sys_Qc_Attachments],
[t0].[CreateDate]AS [Sys_Qc_CreateDate],
[t15].[LOGONNAME]AS [Sys_Qc_CreateBy_CODE],
[t15].[USER_NAME]AS [Sys_Qc_CreateBy_NAME],
[t0].[ModifiedDate]AS [Sys_Qc_ModifiedDate],
[t16].[LOGONNAME]AS [Sys_Qc_ModifiedBy_CODE],
[t16].[USER_NAME]AS [Sys_Qc_ModifiedBy_NAME],
[t0].[LastModifiedDate]AS [Sys_Qc_LastModifiedDate],
[t17].[LOGONNAME]AS [Sys_Qc_LastModifiedBy_CODE],
[t17].[USER_NAME]AS [Sys_Qc_LastModifiedBy_NAME],
[t0].[ApproveDate]AS [Sys_Qc_ApproveDate],
[t18].[LOGONNAME]AS [Sys_Qc_ApproveBy_CODE],
[t18].[USER_NAME]AS [Sys_Qc_ApproveBy_NAME],
[t0].[MO_ID]AS [sys_MainId],ROW_NUMBER() OVER (
ORDERBY CASE [t0].[Owner_Org_RTK]
WHENN'PLANT'
THEN[t1].[PLANT_CODE] END ASC,
[t0].[DOC_NO]DESC,[t0].[DOC_DATE] DESC
)AS DCMS_ROWNUM
FROM[MO] AS [t0]
LEFTOUTER JOIN [PLANT] AS [t1]
ON[t0].[Owner_Org_ROid] = [t1].[PLANT_ID]
LEFTOUTER JOIN [DOC] AS [t2]
ON[t0].[DOC_ID] = [t2].[DOC_ID]
LEFTOUTER JOIN [ITEM] AS [t3]
ON[t0].[ITEM_ID] = [t3].[ITEM_BUSINESS_ID]
LEFTOUTER JOIN [ITEM_FEATURE] AS [t4]
ON[t0].[ITEM_FEATURE_ID] = [t4].[ITEM_FEATURE_ID]
LEFTOUTER JOIN [UNIT] AS [t5]
ON[t0].[BUSINESS_UNIT_ID] = [t5].[UNIT_ID]
LEFTOUTER JOIN [SUPPLIER] AS [t6]
ON[t0].[SOURCE_ID_ROid] = [t6].[SUPPLIER_BUSINESS_ID]
LEFTOUTER JOIN [WORK_CENTER] AS [t7]
ON[t0].[SOURCE_ID_ROid] = [t7].[WORK_CENTER_ID]
LEFTOUTER JOIN [ADMIN_UNIT] AS [t8]
ON[t0].[ADMIN_UNIT_ID] = [t8].[ADMIN_UNIT_ID]
LEFTOUTER JOIN [ITEM_ROUTING] AS [t9]
ON[t0].[ITEM_ROUTING_ID] = [t9].[ITEM_ROUTING_ID]
LEFTOUTER JOIN [MO] AS [t10]
ON[t0].[SOURCE_MO_ID] = [t10].[MO_ID]
LEFTOUTER JOIN [MO] AS [t11]
ON[t0].[UP_MO_ID] = [t11].[MO_ID]
LEFTOUTER JOIN [MO] AS [t12]
ON[t0].[PARA_MO_ID] = [t12].[MO_ID]
LEFTOUTER JOIN [ADMIN_UNIT] AS [t13]
ON[t0].[Owner_Dept] = [t13].[ADMIN_UNIT_ID]
LEFTOUTER JOIN [EMPLOYEE] AS [t14]
ON[t0].[Owner_Emp] = [t14].[EMPLOYEE_ID]
LEFTOUTER JOIN [USER] AS [t15]
ON[t0].[CreateBy] = [t15].[USER_ID]
LEFTOUTER JOIN [USER] AS [t16]
ON[t0].[ModifiedBy] = [t16].[USER_ID]
LEFTOUTER JOIN [USER] AS [t17]
ON[t0].[LastModifiedBy] = [t17].[USER_ID]
LEFTOUTER JOIN [USER] AS [t18]
ON[t0].[ApproveBy] = [t18].[USER_ID]
WHERE1 = 1
) AS [DCMS_DYNC]
WHERE DCMS_ROWNUM BETWEEN 101 AND201

这里面最大表的数据量也就在10多万左右，但是查询需要等待7S左右

二、以下是SQL中牵扯到的表的数据量


三、相关表的索引






















四执行计划

开销最大的就是这段

[道玄希言]

这么多关联。。。 可以尝试拆分成多个查询。 然后在前台datatable里面来组合。

[阳泉酒家小当家]

简单的办法是 更新一下统计信息： update statsitics 表名称 把上面所有涉及到的表 都更新一遍，再看看 速度有没有快

[阳泉酒家小当家]

你这个表的关联也太多了吧，19次关联。。。

[薛定谔的DBA]

--第一张表用临时表替换:
SELECT * INTO #MO
FROM (
    SELECT * ,ROW_NUMBER() OVER ( 
	   ORDER BY 
	   CASE [t0].[Owner_Org_RTK] WHEN N'PLANT' THEN[t1].[PLANT_CODE] END ASC
	   , [t0].[DOC_NO]DESC 
	   ,[t0].[DOC_DATE] DESC 
    )AS DCMS_ROWNUM
    FROM [MO]
) AS [t0]
WHERE DCMS_ROWNUM BETWEEN 101 AND 201


/*
以下不需要了:
,ROW_NUMBER() OVER ()
FROM [MO] AS [t0]
WHERE DCMS_ROWNUM BETWEEN 101 AND 201


FROM [MO] AS [t0] 改为用临时表  FROM [#MO] AS [t0] 

试试看!~
*/