Click here to Skip to main content
12,077,457 members (47,111 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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
   )

[Posted as solution]
in estimated execution plan i found hast match aggregate cost is taking 64%.
Posted 5-Nov-12 5:41am
Edited 5-Nov-12 6:01am
lewax0046.1K
v2
Comments
lewax00 5-Nov-12 11:03am
   
Please add additional information by editing your question (click "Improve question"), if you add it as a solution it will look like your question has already been answered.
   
Not a question. Imagine someone is eager to help you. And what this person would write?
--SA
Rohit Shrivastava 5-Nov-12 12:59pm
   
without the knowledge of schema and indexes i can say try to remove inner distinct as outer distinct anyway will take care of duplicates.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160212.1 | Last Updated 6 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100