Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
352 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  
 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.03 seconds. Powered By: Snitz Forums 2000