help required tune the sql

Question

1.00/5 (1 vote)

See more:

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

SQL

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 4:41am

nirkar jena

Updated 5-Nov-12 5:01am

lewax00

v2

Add a Solution

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.

Sergey Alexandrovich Kryukov 5-Nov-12 12:02pm

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

kankeyan · Answer 1 · 2012-11-05T18:36:00

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.

SQL

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.