帮助所需的调整SQL [英] help required tune the sql

问题描述

大家好，
请帮助我在sql server中调整以下sql.插入时花很长时间.

Hi all,
Please help me to tune the below sql in sql server .taking long time while inserting.

insert into AN_MAPPING(MAPPING_ID,MAPPING_NAME,SESSION_ID,FOLDER_NAME,TOOL_ID,PROJECT_ID)
( 
select distinct mapping_id,
	mapping_name,
	session_id,
	subject_area,
	tool_id,
	PROJECT_ID
from
(
select  distinct rsm.mapping_id,
rls.mapping_name,
rls.session_id,
rls.subject_area,
1  tool_id,
A.PROJECT_ID
              from
              dbo.FD_INFA_REP_LOAD_SESSIONS rls,
              dbo.FD_INFA_REP_SRC_MAPPING rsm,
              (
SELECT distinct
 OPB_MAPPING.PROJECT_ID,
   OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
    OPB_SUBJECT.SUBJ_ID SUBJ_ID,
    OPB_MAPPING.MAPPING_NAME MAPPING_NAME
   
FROM  dbo.FD_INFA_OPB_MAPPING OPB_MAPPING 
inner join dbo.FD_INFA_OPB_TDS OPB_TDS 
on OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
and OPB_TDS.VERSION_NUMBER=OPB_MAPPING.VERSION_NUMBER
inner join dbo.FD_INFA_OPB_WIDGET_INST OPB_WIDGET_INST on 
OPB_WIDGET_INST.MAPPING_ID=OPB_MAPPING.MAPPING_ID 
and OPB_MAPPING.VERSION_NUMBER = OPB_WIDGET_INST.VERSION_NUMBER
inner JOIN dbo.FD_INFA_OPB_TARG OPB_TARG on 
OPB_TARG.TARGET_ID=OPB_WIDGET_INST.WIDGET_ID
inner JOIN dbo.FD_INFA_OPB_SUBJECT OPB_SUBJECT on 
OPB_SUBJECT.SUBJ_ID=OPB_TARG.SUBJ_ID
AND OPB_WIDGET_INST.WIDGET_TYPE = 2
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE in(1,2)
AND OPB_TARG.IS_VISIBLE in( 1,2)
AND OPB_MAPPING.PROJECT_ID= -511779242
--    AND(datediff (ss,CNTL.FN_Last_UpdateTime,OPB_MAPPING.LAST_SAVED)>0
--OR datediff (ss,CNTL.FN_Last_UpdateTime,OPB_TARG.LAST_SAVED)>0)
              ) A
 where
    rls.mapping_name = rsm.mapping_name
   and rls.SUBJECT_AREA = rsm.SUBJECT_AREA
   and  rls.IS_VALID=1
   and A.mapping_name = rls.mapping_name
   and rsm.PROJECT_ID = rls.PROJECT_ID
   and rls.PROJECT_ID = -511779242)B
   )

[发布为解决方案]
在估计的执行计划中，我发现尚未匹配的总成本占了64％.

[Posted as solution]
in estimated execution plan i found hast match aggregate cost is taking 64%.

推荐答案

DO n't使查询复杂化.选择一个特定的块值以CTE并在您的insert语句中加入.会更好.

例如.

DOn''t make complicate in your query. Select a particular block values to CTE and Join in your insert statement. It will be better.

For eg.

SELECT distinct
 OPB_MAPPING.PROJECT_ID,
   OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
    OPB_SUBJECT.SUBJ_ID SUBJ_ID,
    OPB_MAPPING.MAPPING_NAME MAPPING_NAME
   
FROM  dbo.FD_INFA_OPB_MAPPING OPB_MAPPING 
inner join dbo.FD_INFA_OPB_TDS OPB_TDS 
on OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
and OPB_TDS.VERSION_NUMBER=OPB_MAPPING.VERSION_NUMBER
inner join dbo.FD_INFA_OPB_WIDGET_INST OPB_WIDGET_INST on 
OPB_WIDGET_INST.MAPPING_ID=OPB_MAPPING.MAPPING_ID 
and OPB_MAPPING.VERSION_NUMBER = OPB_WIDGET_INST.VERSION_NUMBER
inner JOIN dbo.FD_INFA_OPB_TARG OPB_TARG on 
OPB_TARG.TARGET_ID=OPB_WIDGET_INST.WIDGET_ID
inner JOIN dbo.FD_INFA_OPB_SUBJECT OPB_SUBJECT on 
OPB_SUBJECT.SUBJ_ID=OPB_TARG.SUBJ_ID
AND OPB_WIDGET_INST.WIDGET_TYPE = 2
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE in(1,2)
AND OPB_TARG.IS_VISIBLE in( 1,2)
AND OPB_MAPPING.PROJECT_ID= -511779242

使此块成为CTE并加入.
希望对您有帮助.

make this block to CTE and Join.
I Hope it will help for you.

这篇关于帮助所需的调整SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！