Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: SQL statement tuning

    I have the following SQL statement which is going WAY to slow. Anyone have an idea on how to speed it up?

    /Martin

    SELECT T1.SHIPMENT_DATE__ISO_ AS SHIPMENT_DATE__ISO_,
    T1.TOTAL_GROSS_WEIGHT AS TOTAL_GROSS_WEIGHT,
    T1.NUMBER_OF_PACKAGES AS NUMBER_OF_PACKAGES,
    T1.TOTAL_VOLUME AS TOTAL_VOLUME,
    T1.ISO_COUNTRY_OF_DESTIN_ AS ISO_COUNTRY_OF_DESTIN_,
    (UCASE(T2.ADDRESS_LINE_1)) AS ADDRESS_LINE_1_TYP4,
    (UCASE(T2.ADDRESS_LINE_4)) AS ADDRESS_LINE_4_TYP4,
    T3.PARTY_ID AS PARTY_ID,
    (UCASE(T3.ADDRESS_LINE_1)) AS ADDRESS_LINE_1_TYP3,
    (UCASE(T3.ADDRESS_LINE_4)) AS ADDRESS_LINE_4_TYP3,
    T3.ISO_COUNTRY_CODE AS ISO_COUNTRY_CODE,
    T4.CARRIER_CODE AS CARRIER_CODE,
    T5.MODE_OF_TRANSPORT AS MODE_OF_TRANSPORT,
    T1.TRANSPORT_MODE_CODE AS TRANSPORT_MODE_CODE

    FROM GTFG2.GTADDR T2,
    GTFG2.GTTRPI T1,
    GTFG2.GTADDR T3,
    (GTFG2.GTTMCC T5 LEFT OUTER JOIN GTFG2.GTTCAR T4 ON ((T5.TRANSPORT_MODE_CODE = T4.TRANSPORT_MODE_CODE)
    AND (T5.ISO_COUNTRY_OF_DEPARTURE = T4.ISO_COUNTRY_OF_DEPARTURE))
    AND (T5.ISO_COUNTRY_OF_DESTIN_ = T4.ISO_COUNTRY_OF_DESTIN_))

    WHERE ((T1.TIMESTAMP = T2.TIMESTAMP) AND (T2.TYPE_OF_ADDRESS = '4'))
    AND ((T1.TIMESTAMP = T3.TIMESTAMP) AND (T3.TYPE_OF_ADDRESS = '3'))
    AND (((T1.TRANSPORT_MODE_CODE = T5.TRANSPORT_MODE_CODE)
    AND ((T1.ISO_COUNTRY_OF_DEPARTURE = T5.ISO_COUNTRY_OF_DEPARTURE) OR (T5.ISO_COUNTRY_OF_DEPARTURE = '*')))
    AND ((T1.ISO_COUNTRY_OF_DESTIN_= T5.ISO_COUNTRY_OF_DESTIN_) OR (T5.ISO_COUNTRY_OF_DESTIN_ = '*')))

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The OR operators in the WHERE clause are probably forcing a table scan. Test this by removing them. If that works, consider creating four queries and selecting between them using code, or via UNIONs.

    -PatP

Posting Permissions

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