Hi guys,
I've been playing with this query for about a week now, and I still
can't find ways to speed it up. It runs for about 5 minutes. The only
table (among the 41) with significant amount of data is dbo.S_EVT_ACT,
which contains about 900,000 records. The query is automatically
generated by Siebel, and therefore not really modifiable. And hints
are not really possible. While looking at the exec plan, we can see
that what takes so long is a clustered index scan of T1. This
clustered index is defined on a column ROW_ID (a Siebel-generated
primary key used for every table). I tried to define all kinds of
indexes in order to bypass this clustered index scan, but no success
so far ... Here's the query:
SELECT ...
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_EVT_ACT_FNX T2 ON T1.ROW_ID =
T2.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_FN_APPR T3 ON T2.AMS_ACT_ID =
T3.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.PERFRM_BY_OU_ID =
T4.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.PRI_LST_ID =
T5.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T6 ON T1.PR_CON_ID =
T6.PAR_ROW_ID
AND T1.PR_CON_ID = T6.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PRI_LST T7 ON T1.RATE_LST_ID =
T7.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PRDINT T8 ON T1.ROW_ID =
T8.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_ASSET T9 ON T1.ASSET_ID = T9.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T10 ON T1.PR_CON_ID =
T10.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_DOC_AGREE T11 ON T1.AGREEMENT_ID =
T11.ROW_ID
LEFT OUTER JOIN dbo.S_EXP_RPT T12 ON T1.PR_EXP_RPT_ID =
T12.ROW_ID
LEFT OUTER JOIN dbo.S_INS_CLAIM T13 ON T1.INSCLM_ID =
T13.ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT_SS T14 ON T1.ROW_ID =
T14.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_INT_INSTANCE T15 ON T14.OWN_INST_ID =
T15.ROW_ID
LEFT OUTER JOIN dbo.S_ME_EVT_LS T16 ON T1.ME_EVT_ID =
T16.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T17 ON T1.OPTY_ID = T17.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T18 ON T1.TARGET_OU_ID =
T18.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PART_RPR T19 ON T1.PART_RPR_ID =
T19.ROW_ID
LEFT OUTER JOIN dbo.S_POSTN T20 ON T1.OWNER_POSTN_ID =
T20.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PROD_DEFECT T21 ON T1.SRA_DEFECT_ID =
T21.ROW_ID
LEFT OUTER JOIN dbo.S_PROJ T22 ON T1.PROJ_ID = T22.ROW_ID
LEFT OUTER JOIN dbo.S_PROJITEM T23 ON T1.PROJ_ITEM_ID =
T23.ROW_ID
LEFT OUTER JOIN dbo.S_SRC T24 ON T1.SRC_ID = T24.ROW_ID
LEFT OUTER JOIN dbo.S_SRV_REQ T25 ON T1.SRA_SR_ID =
T25.ROW_ID
LEFT OUTER JOIN dbo.S_TMPL_PLANITEM T26 ON T1.ASSESS_TMPL_ID
=
T26.ROW_ID
LEFT OUTER JOIN dbo.S_TMSHT_LINE T27 ON T1.PR_TMSHT_LINE_ID
=
T27.ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT T28 ON T1.TEMPLATE_ID =
T28.ROW_ID
LEFT OUTER JOIN dbo.S_SUSP_ACT T29 ON T1.ROW_ID =
T29.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_EVT_MAIL T30 ON T1.ROW_ID =
T30.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_SRV_ACT T31 ON T1.ROW_ID =
T31.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_ACT_EMP T32 ON T1.OWNER_PER_ID =
T32.EMP_ID AND
T1.ROW_ID = T32.ACTIVITY_ID
LEFT OUTER JOIN dbo.S_PARTY T33 ON T32.EMP_ID = T33.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT_FNX T34 ON T32.EMP_ID =
T34.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_USER T35 ON T32.EMP_ID =
T35.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PARTY T36 ON T1.PR_CON_ID = T36.ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT T37 ON T1.PR_CON_ID =
T37.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT_FNX T38 ON T1.PR_CON_ID =
T38.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T39 ON T1.PR_PRDINT_ID =
T39.ROW_ID
LEFT OUTER JOIN dbo.S_ACT_PROD_APPL T40 ON T1.PR_PRDINT_ID =
T40.ROW_ID
LEFT OUTER JOIN dbo.S_PROD_INT T41 ON T40.PRDINT_ID =
T41.ROW_ID
WHERE
(
(T1.PAR_EVT_ID = T1.ROW_ID OR T1.PAR_EVT_ID IS NULL OR
T1.PAR_EVT_ID !=
T1.ROW_ID AND T1.OPTY_ID IS NOT NULL OR T1.SUBTYPE_CD =
'General' AND
T1.TODO_CD != 'Marketing eEvent') AND
(T1.APPT_REPT_REPL_CD IS NULL) AND
(T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' AND
(T1.OPTY_ID IS NULL OR T17.SECURE_FLG = 'N' OR T1.OPTY_ID IN
(
SELECT SQ1_T2.OPTY_ID
FROM dbo.S_PARTY SQ1_T1
INNER JOIN dbo.S_OPTY_POSTN SQ1_T2
ON SQ1_T2.POSITION_ID = SQ1_T1.ROW_ID
INNER JOIN dbo.S_POSTN SQ1_T3
ON SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID
LEFT OUTER JOIN dbo.S_CONTACT SQ1_T4
ON SQ1_T3.PR_EMP_ID = SQ1_T4.PAR_ROW_ID
WHERE
(SQ1_T4.ROW_ID = '1-23NDP')
)
)
)
AND
(T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
'1-23NDP')) AND
(T1.APPT_START_DT > DATEADD
(SECOND, ROUND (-183 * 86400, 0), '01/15/2004 00:00:00')
OR
T1.TODO_ACTL_END_DT >
DATEADD (SECOND, ROUND (-183 * 86400, 0), '01/15/2004
00:00:00'))
Anyone's got an idea of which index on T1 might help me?
Daniel