SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Long running query need to be tuned
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

d3fee
Starting Member

Australia
1 Posts

Posted - 02/25/2014 :  20:53:04  Show Profile  Reply with Quote
Hi I have a long running query that's been running for more 20 hours now. I have stopped the query. I need to tune this query for it to run faster. Any suggestions would be greatly appreciated.

SELECT
T1.CONFLICT_ID,
CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T1.LAST_UPD_BY,
T1.CREATED_BY,
T1.MODIFICATION_NUM,
T1.ROW_ID,
CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
T3.INTEGRATION_ID,
T1.X_ATO_ESCALATION_FLAG,
T1.X_SECURITY_CLASSIFICATION_LVL,
T1.X_SPECIAL_INT_SECURITY_LVL,
T1.X_STAFF_SECURITY_LVL,
T1.X_TAX_ADV_ID,
T1.X_CON_TYPE,
T1.NAME,
T1.ASGN_MANL_FLG,
CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
T1.OWNER_PER_ID,
T1.EVT_STAT_CD,
T1.X_SUB_CON_TYPE,
T4.X_TAN_NUM,
T1.TODO_CD,
T1.X_IWD_ID,
T1.TARGET_OU_ID,
T1.X_STATUS_UPD_BY,
T8.ATTRIB_02,
T1.TEMPLATE_FLG,
T1.OPTY_ID,
T1.ASGN_SYS_FLG,
T1.X_CAPABILITY,
T1.ALARM_FLAG,
T1.APPT_REPT_FLG,
T1.TARGET_PER_ID,
T1.APPT_REPT_REPL_CD,
T7.OU_ID,
T1.X_INTG_FLG,
T7.POSTN_TYPE_CD,
CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
CONVERT (VARCHAR (10),T1.TODO_AFTER_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_AFTER_DT, 8),
T1.CAL_TYPE_CD,
T1.OWNER_LOGIN,
CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
T1.CAL_DISP_FLG,
T11.LOGIN,
T10.ROW_ID,
T8.ROW_ID,
T8.PAR_ROW_ID,
T8.MODIFICATION_NUM,
T8.CREATED_BY,
T8.LAST_UPD_BY,
CONVERT (VARCHAR (10),T8.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T8.CREATED, 8),
CONVERT (VARCHAR (10),T8.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T8.LAST_UPD, 8),
T8.CONFLICT_ID,
T8.PAR_ROW_ID,
T9.ROW_ID,
T10.ROW_ID
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.OWNER_PER_ID = T2.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T3 ON T1.TARGET_OU_ID = T3.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.X_TAX_ADV_ID = T4.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T5 ON T1.OPTY_ID = T5.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY_POSTN T6 ON T1.OPTY_ID = T6.OPTY_ID AND T6.POSITION_ID = '0-5220'
LEFT OUTER JOIN dbo.S_POSTN T7 ON T2.PR_HELD_POSTN_ID = T7.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT_X T8 ON T1.ROW_ID = T8.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_ACT_EMP T9 ON T1.OWNER_PER_ID = T9.EMP_ID AND T1.ROW_ID = T9.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_PARTY T10 ON T9.EMP_ID = T10.ROW_ID
LEFT OUTER JOIN dbo.S_USER T11 ON T10.ROW_ID = T11.PAR_ROW_ID
WHERE
((T1.APPT_REPT_REPL_CD IS NULL) AND
((T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL) AND (T1.OPTY_ID IS NULL OR T5.SECURE_FLG = 'N' OR T6.OPTY_ID IS NOT NULL) AND T1.SUBTYPE_CD != 'LODGMENT' AND T1.SUBTYPE_CD != 'ALERT')) AND
(T1.X_IWD_ID IS NULL)


DNGO

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 02/26/2014 :  08:40:00  Show Profile  Reply with Quote
See your post at: http://www.sqlservercentral.com/Forums/Topic1545199-392-1.aspx

Please note that date formating is better done by a front end.

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000