Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: high compilation time

    my db is db2V8.2 fp 5 (solaris)
    i have SQL select query joning 15 tables
    the default query optimization was set to 7. this query takes 5 min to compile(prepare) and jus a few seconds like 20sec to execute(from db2batch output).if we reduce the query optimization to 5 or 3 the compile time reduces and teh query execution time. this is happening only with this one particular query. even if the query optimization is lowered to 5 or 3 the compilation time still is around 100 secs.all these dim tables are very small tables.and the join columns of dim are all primary keys.what could be the possible reasons for more compilation time?. what changes do i need to do in the query so that it gets compiled fast.
    here is the query.

    SELECT DP.NAME AS Project, DP.PROJECT_ID AS Project_ID, DCC.NAME AS Control,DCa.NAME AS Carrier, DTC.FULL_NAME AS Tour_contact,DPC.FULL_NAME AS Project_contact,DTCD.NAME AS cost_div, DA_DEST.STATE AS DState,tf.CARRIER_MILES_QY AS Miles,DTrT.DESCRIPTION AS Equip_type, f.TOUR_ID AS t_id,DIWT.DESCRIPTION AS new_is_werner_flag,DTS.DESCRIPTION AS STATUS,DOT.DESCRIPTION AS MODE,DS.NAME AS o_ship_name,tf.STOPS AS "# of Stops",tf.CREATION_TS AS t_creation_date,tf.SCHEDULED_ARRIVAL_TS AS a_scd_arrive,DS.CITY_NM AS o_ship_ciy,DS.STATE_CD AS o_ship_state,DS.ZIP_CD AS o_ship_zip,DCo.CITY_NM AS o_cons_city,DCo.STATE_CD AS o_cons_state,DCo.ZIP_CD AS o_cons_zip,SUM ( TOF.WEIGHT_QY ) AS weight,SUM ( TOF.CUBE_QY ) AS cube,SUM ( TOF.QUANTITY_QY ) AS quantity,tf.TOTAL_EXPENSE,tf.TOTAL_REVENUE
    FROM FACT.TOUR_FACT tf,
    FACT.TOUR_ORDERS_FACT TOF,
    DIM.PROJECT DP,
    DIM.CUSTOMER_CONTROL DCC,
    DIM.CARRIER DCa,
    DIM.PROJECT_CONTACT DPC,
    DIM.ADDRESS DA_DEST,
    DIM.TRAILER_TYPE DTrT,
    DIM.IS_WERNER_TRIP DIWT,
    DIM.TOUR_STATUS DTS,
    DIM.TOUR_TYPE DTT,
    DIM.ORDER_TYPE DOT,
    DIM.SHIPPER DS,
    DIM.CONSIGNEE DCo,
    DIM.TOUR_CONTACT DTC,
    DIM.TOUR_COST_DIVISION DTCD
    WHERE tf.TOUR_ID = TOF.TOUR_ID
    AND tf.PROJECT_ID = DP.PROJECT_ID
    AND tf.CUSTOMER_CONTROL_ROLE_ID =
    DCC.CUSTOMER_CONTROL_ID
    AND tf.ACTUAL_CARRIER_ID = DCa.CARRIER_ID
    AND tf.PROJECT_CONTACT_ID = DPC.PROJECT_CONTACT_ID
    AND tf.DESTINATION_ADDRESS_ID = DA_DEST.ADDRESS_ID
    AND tf.TRAILER_TYPE_ID = DTrT.TRAILER_TYPE_ID
    AND tf.IS_WERNER_TRIP_ID = DIWT.IS_WERNER_TRIP_ID
    AND tf.TOUR_STATUS_ID = DTS.TOUR_STATUS_ID
    AND tf.TOUR_TYPE_ID = DTT.TOUR_TYPE_ID
    AND tf.ORDER_TYPE_ID = DOT.ORDER_TYPE_ID
    AND tf.SHIPPER_ID = DS.SHIPPER_ID
    AND tf.CONSIGNEE_ROLE_ID = DCo.CONSIGNEE_ID
    AND tf.TOUR_CONTACT_ID = DTC.TOUR_CONTACT_ID
    AND tf.TOUR_COST_DIVISION_ID =

    DTCD.TOUR_COST_DIVISION_ID
    AND tf.PROJECT_ID = 50
    AND tf.SCHEDULED_ARRIVAL_TS BETWEEN '2007-08-04

    00:00:00.0' AND '2007-08-10 23:59:59.0'
    AND DOT.DESCRIPTION NOT IN ( 'PMO Order' )
    AND TOF.SEQUENCE_NB = 9
    AND TOF.TOUR_STATUS_ID IN ( 2986 )
    GROUP BY DP.NAME,
    DP.PROJECT_ID,
    DCC.NAME,
    DCa.NAME,
    DTC.FULL_NAME,
    DPC.FULL_NAME,
    DTCD.NAME,
    DA_DEST.STATE,
    tf.CARRIER_MILES_QY,
    DTrT.DESCRIPTION,
    tf.TOUR_ID,
    DIWT.DESCRIPTION,
    DTS.DESCRIPTION,
    DOT.DESCRIPTION,
    DS.NAME,
    tf.STOPS,
    tf.CREATION_TS,
    tf.SCHEDULED_ARRIVAL_TS,
    DS.CITY_NM,
    DS.STATE_CD,
    DS.ZIP_CD,
    DCo.CITY_NM,
    DCo.STATE_CD,
    DCo.ZIP_CD,
    tf.TOTAL_EXPENSE,
    tf.TOTAL_REVENUE
    ORDER BY tf.TOUR_ID
    FOR
    FETCH ONLY
    any help is appreciated
    Last edited by rajaryan4545; 02-28-08 at 13:54.

Posting Permissions

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