February 25, 2014 at 8:01 pm
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)
February 26, 2014 at 12:41 am
Table definitions, index definitions and execution plan please (estimated if the query won't complete, actual otherwise). See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2014 at 4:19 am
What Gail says.
But, just a little bit of help without the details.
All those != commands are going to prevent good cardinality estimates from the optimizer which is likely to lead to a poor plan. OR statements are frequently better served by using a UNION ALL command to do the two sides of the OR.
I can't say much more without at least the execution plan as a guide.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2014 at 6:01 am
Additionally, logic-wise this is questionable - T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL
AND and OR without brackets. What's actually meant by this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2014 at 4:45 pm
Apologies for the poor question structure. Its obviously my first time posting. Please find attached the table, indexes definitions and the estimated query plan.
T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL
has been changed to
(T1.TEMPLATE_FLG = 'N')
The distinct values for T1.TEMPLATE_FLG is only P, Y or N so I thought i could simplify this by only putting N for the where clause.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply