Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    71

    Unanswered: oh so slow query...

    The following query is causing some problems because it's taking too long to complete. I looked at the estimated execturion plan and I am unsure why it appears to spend over 50% of its time doing a 'Bookmark Lookup' on on particular column (SRA_SR_ID in the S_EVT_ACT table). There is an index on the column - S_EVT_F14. I'm not sure if the query is using the index properly. What can be done to specifically improve this particular problem? In general, does anyone have some suggestions for optimizing the query as a whole?

    Thanks in advance. Clive

    SELECT
    T1.APPT_REPT_FLG,
    T18.X_ALIS_ID,
    CONVERT (VARCHAR (10),T1.APPT_START_TM, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_TM, 8),
    T1.ASGN_USR_EXCLD_FLG,
    T2.NAME,
    T19.STAT_CD,
    T1.APPT_REPT_TYPE,
    T15.NAME,
    CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_END_DT, 8),
    T1.TODO_CD,
    T1.X_DOC_CAT_ID,
    CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_START_DT, 8),
    T1.TARGET_OU_ID,
    T7.ZIPCODE,
    T3.ZIPCODE,
    T9.EXP_RPT_NUM,
    T1.LAST_UPD_BY,
    T1.OWNER_PER_ID,
    T1.PART_RPR_ID,
    T1.RATE_LST_ID,
    CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_REPT_END_DT, 8),
    T1.ACTIVITY_UID,
    T4.NAME,
    T1.PR_TMSHT_LINE_ID,
    T18.LAST_NAME,
    T7.ADDR,
    T18.SEX_MF,
    T1.BILLABLE_FLG,
    CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_PLAN_END_DT, 8),
    T1.SRA_SR_ID,
    T1.TARGET_PER_ADDR_ID,
    T18.X_FST_NAME,
    T1.EVT_STAT_CD,
    CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_DATE, 8),
    T1.ROW_STATUS,
    T1.ACD_CALL_DURATION,
    T5.NAME,
    T8.FAX_PH_NUM,
    T8.X_FST_NAME,
    T8.LAST_NAME,
    T1.MODIFICATION_NUM,
    T1.X_CAMP_ID,
    CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 101) + ' ' + CONVERT (VARCHAR (10),T1.X_SCAN_TIME, 8),
    T1.ASSOCIATED_COST,
    T13.NAME,
    CONVERT (VARCHAR (10),T1.LAST_UPD, 101) + ' ' + CONVERT (VARCHAR (10),T1.LAST_UPD, 8),
    T17.TMSHT_NUM,
    T1.PR_SYMPTOM_CD,
    T1.OPTY_ID,
    CONVERT (VARCHAR (10),T18.BIRTH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T18.BIRTH_DT, 8),
    T1.PR_EXP_RPT_ID,
    CONVERT (VARCHAR (10),T1.APPT_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.APPT_START_DT, 8),
    T8.FST_NAME,
    T16.SR_NUM,
    T1.SRA_DEFECT_ID,
    T1.CREATED_BY,
    T8.WORK_PH_NUM,
    CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.COST_EXCH_DT, 8),
    T1.CALL_ID,
    T1.X_CLIENT_ID,
    T1.PROJ_ID,
    T12.DEFECT_NUM,
    T1.CREATOR_LOGIN,
    T1.CONFLICT_ID,
    T19.OUTCOME_CD,
    T1.TEMPLATE_FLG,
    T2.PR_ADDR_ID,
    T1.PREV_ACT_ID,
    T1.X_DOC_NAME,
    T1.EXP_RLTD_FLG,
    T1.X_BATCH_REF,
    T1.PRI_LST_ID,
    T1.SRC_ID,
    T1.X_POLICY_REF,
    CONVERT (VARCHAR (10),T1.CREATED, 101) + ' ' + CONVERT (VARCHAR (10),T1.CREATED, 8),
    T1.EMAIL_FORWARD_FLG,
    T11.DMT_NUM,
    T1.TMSHT_RLTD_FLG,
    T1.ROW_ID,
    T10.NAME,
    T18.CONSUMER_FLG,
    T1.TARGET_PER_ID,
    T18.FST_NAME,
    T1.PRIV_FLG,
    T3.PROVINCE,
    T8.X_ALIS_ID,
    T8.JOB_TITLE,
    T14.NAME,
    T1.NAME,
    T1.PCT_COMPLETE,
    T1.SRA_TYPE_CD,
    T1.ALARM_FLAG,
    T1.CAL_DISP_FLG,
    T1.EVT_PRIORITY_CD,
    T1.COST_CURCY_CD,
    T2.LOC,
    CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 101) + ' ' + CONVERT (VARCHAR (10),T1.TODO_ACTL_START_DT, 8),
    T20.FILE_NAME,
    T1.SRA_RESOLUTION_CD,
    T6.PRDINT_ID,
    T1.OWNER_LOGIN
    FROM
    dbo.S_EVT_ACT T1
    LEFT OUTER JOIN dbo.S_ORG_EXT T2 ON T1.TARGET_OU_ID = T2.ROW_ID
    LEFT OUTER JOIN dbo.S_ADDR_ORG T3 ON T2.PR_ADDR_ID = T3.ROW_ID
    LEFT OUTER JOIN dbo.S_PRI_LST T4 ON T1.PRI_LST_ID = T4.ROW_ID
    LEFT OUTER JOIN dbo.S_PRI_LST T5 ON T1.RATE_LST_ID = T5.ROW_ID
    LEFT OUTER JOIN dbo.S_ACT_PRDINT T6 ON T1.ROW_ID = T6.ACTIVITY_ID
    LEFT OUTER JOIN dbo.S_ADDR_PER T7 ON T1.TARGET_PER_ADDR_ID = T7.ROW_ID
    LEFT OUTER JOIN dbo.S_CONTACT T8 ON T1.TARGET_PER_ID = T8.ROW_ID
    LEFT OUTER JOIN dbo.S_EXP_RPT T9 ON T1.PR_EXP_RPT_ID = T9.ROW_ID
    LEFT OUTER JOIN dbo.S_OPTY T10 ON T1.OPTY_ID = T10.ROW_ID
    LEFT OUTER JOIN dbo.S_PART_RPR T11 ON T1.PART_RPR_ID = T11.ROW_ID
    LEFT OUTER JOIN dbo.S_PROD_DEFECT T12 ON T1.SRA_DEFECT_ID = T12.ROW_ID
    LEFT OUTER JOIN dbo.S_PROD_INT T13 ON T6.PRDINT_ID = T13.ROW_ID
    LEFT OUTER JOIN dbo.S_PROJ T14 ON T1.PROJ_ID = T14.ROW_ID
    LEFT OUTER JOIN dbo.S_SRC T15 ON T1.SRC_ID = T15.ROW_ID
    LEFT OUTER JOIN dbo.S_SRV_REQ T16 ON T1.SRA_SR_ID = T16.ROW_ID
    LEFT OUTER JOIN dbo.S_TMSHT_LINE T17 ON T1.PR_TMSHT_LINE_ID = T17.ROW_ID
    LEFT OUTER JOIN dbo.S_CONTACT T18 ON T1.X_CLIENT_ID = T18.ROW_ID
    LEFT OUTER JOIN dbo.S_CAMP_CON T19 ON T1.X_CAMP_ID = T19.SRC_ID AND T1.TARGET_PER_ID = T19.CON_PER_ID
    LEFT OUTER JOIN dbo.S_ACTIVITY_ATT T20 ON T1.ROW_ID = T20.PAR_ROW_ID
    WHERE
    ((T1.APPT_REPT_FLG != 'Y' OR T1.APPT_REPT_FLG IS NULL) AND
    (T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL)) AND
    (T1.SRA_SR_ID = '1-EQLOO')

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummmm...nothing?

    Seriously though...I think I'd break it up...

    Looks like the driver is the aliased table T1....

    Make a dervide table out of that and get it as small as possible, then do your joins...

    You need to help us with DDL though...esp. the indexes...

    Ya gotta love surrogate keys....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Posts
    71
    I think I've realised what's going on. The query in question is generated by the application (app sever probably). However, it's not a stored procedure. The query is exactly the same every time it is executed with one exception - a single id value changes. That will mean compilation every time the query is made I guess. I was noticing this as I was trying out a derived table approach and I was using Profiler to see if any improvements were made. Running any version of the query for the first time in QA took around 2 minutes but subequent exectutions were a few seconds. I am assuming that if the application vendor rewrote that part of their app to call a stored procedure with the id value as a parameter, it would run a lot more quickly without all those compilations.

    Thanks,

    Clive

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Either sp or prepared statement would do. In either case the plan should be reused, though I'd prefer sp.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •