Long running query need to be tuned

  • 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)

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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