Просмотр возвращаемых результатов из нескольких таблиц

У меня есть представление DB2 SQL, созданное с помощью следующего оператора:

Проблема, с которой я столкнулся, довольно странная. Когда мы делаем select * в этом представлении, первые несколько раз каждый новый сеанс выполняется медленно. Но затем после 2–3 запусков скорость резко увеличивается, а время выполнения сокращается до 2 секунд с начальных 40 секунд.

Может ли кто-нибудь направить любые возможные оптимизации:

--  Generate SQL 
--  Version:                      V7R4M0 190621
--  Generated on:               02/09/21 10:26:37
--  Relational Database:        BIGBLUE 
--  Standards Option:             Db2 for i 
CREATE VIEW IESQAFILE.PSINPROGVW ( 
     
    AS 
    WITH INPROGRESS AS  
         (  
         SELECT  
         DIODR#  
         , DIDISP  
         , DIUNIT  
         , DIETAD  
         , DIDR1  
         , DIETAT  
         FROM  
         IESQAFILE.LOAD  
         WHERE  
         DIETAD = 0  
         AND DIETAT = '0000'  
         ORDER BY  
         1  
         )  
        , STOPGROUP AS  
         (  
         SELECT  
         SOORD STOPORDER  
         , COUNT(*) STOPSREMAIN  
         , MIN(SOSTP#) NEXTSTOP  
         , MAX(SOAPPR) APPTREQ  
         , MAX(SOAPTM) APPTMADE  
         FROM  
         IESQAFILE.STOPOFF  
         INNER JOIN  
         INPROGRESS  
         ON  
         DIODR# = SOORD  
         WHERE  
         SOARDT = 0  
         GROUP BY  
         SOORD  
         ORDER BY  
         1  
         )  
        , STOPAPPTS AS  
         (  
         SELECT  
         SOORD APPTORDER  
         , SOCUST STOPCUST  
         , SOEDA ETADATE  
         , SOETA ETATIME  
         , SOADT1 EARLYDATE  
         , SOATM1 EARLYTIME  
         , SOADT2 LATEDATE  
         , SOATM2 LATETIME  
         , SOCTYC NEXTCITY  
         , SOSTP# APPTSTOP  
         , SOST NEXTSTATE  
         FROM  
         IESQAFILE.STOPOFF  
         INNER JOIN  
         STOPGROUP  
         ON  
         STOPORDER = SOORD  
         AND NEXTSTOP = SOSTP#  
         )  
        SELECT  
         ORDER_NUMBER  
        , SHIPPER_ID  
        , SHIPPER_NAME  
        , SHIPPER_ADDRESS_1  
        , SHIPPER_ADDRESS_2  
        , SHIPPER_CITY  
        , SHIPPER_ST  
        , SHIPPER_ZIP  
        , SHIPPER_ZIP_EXT  
        , LOAD_AT_ID  
        , LOAD_AT_NAME  
        , LOAD_AT_ADDRESS_1  
        , LOAD_AT_ADDRESS_2  
        , LOAD_AT_CITY  
        , LOAD_AT_ST  
        , LOAD_AT_ZIP  
        , LOAD_AT_ZIP_EXT  
        , LOAD_AT_LATITUDE  
        , LOAD_AT_LONGITUDE  
        , EARLY_PU_DATE_TIME  
        , LATE_PU_DATE_TIME  
        , EARLY_DELV_DATE_TIME  
        , EST_REVENUE  
        , ORDER_DIV  
        , CONSIGNEE_ID  
        , CONSIGNEE_NAME  
        , CONSIGNEE_ADDRESS_1  
        , CONSIGNEE_ADDRESS_2  
        , CONSIGNEE_CITY  
        , CONSIGNEE_ST  
        , CONSIGNEE_ZIP  
        , CONSIGNEE_ZIP_EXT  
        , CONSIGNEE_LATITUDE  
        , CONSIGNEE_LONGITUDE  
        , TRAILER_TYPE  
        , ORDER_MESSAGE  
        , ADDITIONAL_STOPS  
        , CMDTY_CODE  
        , CMDTY_DESCRIPTION  
        , ORDER_MILES  
        , ORDER_WGT  
        , ORIGIN_CITY_CODE  
        , ORIGIN_CITY  
        , ORIGIN_ST  
        , DEST_CITY_CODE  
        , DEST_CITY_NAME  
        , DEST_ST  
        , PICK_UP_AREA  
        , PLAN_INFO  
        , NUMBER_LDS  
        , NUMBER_DISP  
        , SHIP_DATE_TIME  
        , NEW_PICKUP_AREA  
        , EQUIPMENT_NUMBER  
        , APPT_REQ  
        , APPT_MADE  
        , PRE_T_SEQ  
        , PRE_T_AREA  
        , LOAD_DISPATCHED  
        ,  
        CUST_SERV_REP,  
        NEGOTIATIONS,  
         (  
         CASE  
         WHEN UNUNIT IS NOT NULL  
         THEN UNUNIT  
         ELSE ' '  
         END  
         )  
         UNIT_DISPATCHED  
        ,  
         (  
         CASE  
         WHEN UNSUPR IS NOT NULL  
         THEN UNSUPR  
         ELSE ' '  
         END  
         )  
         DRIVER_MGR_CODE  
        , COALESCE(SUPNAM,' ') DRIVER_MGR_NAME  
                                  ,  
                                                                         (  
                                                                      CASE  
                                                                   WHEN UNFMGR IS NOT NULL  
                                                 THEN UNFMGR  
                                                           ELSE ' '  
                                                                 END  
                                                                   )  
                                                                      FLEET_MGR_CODE  
                                                       , COALESCE(FLTNAM,' ') FLEET_MGR_NAME  
                                  ,  
                                                                         (  
                                                                      CASE  
                                                                   WHEN UNTRL1 IS NOT NULL  
                                                 THEN UNTRL1  
                                                           ELSE ' '  
                                                                 END  
                                                                   )  
                                                                      TRAILER_ID  
                                                             , (COALESCE(DIDISP, ' ')) DISPATCH_NUMBER  
                                , (COALESCE(BCMCNEW, ' ')) FED_MC_ID  
                                  , (COALESCE(DIUNIT, ' ')) DISPATCHED_UNIT  
                                , CASE  
                                                                    WHEN UNETAD <> 0  
                                                         AND UNETAT = ''  
                                                         THEN SMIS.CVTDATETIM(CHAR(UNETAD),'0000', (  
         SELECT  
         SUBSTR(DATA_AREA_VALUE, 1109, 2) AS TIMEZONE  
         FROM  
         TABLE(QSYS2.DATA_AREA_INFO('COMPAN', '*LIBL'))  
         )  
         )  
         WHEN UNETAD <> 0  
         THEN SMIS.CVTDATETIM(CHAR(UNETAD),UNETAT, (  
         SELECT  
         SUBSTR(DATA_AREA_VALUE, 1109, 2) AS TIMEZONE  
         FROM  
         TABLE(QSYS2.DATA_AREA_INFO('COMPAN', '*LIBL'))  
         )  
         )  
         WHEN UNETAD = 0  
         THEN '0000-00-00T00:00:00-00:00'  
         END AS ETA_DATE_TIME  
        , (COALESCE(NEXTSTOP, 0 )) NEXTSTOP  
        FROM  
         INPROGRESS  
         INNER JOIN  
         IESQAFILE.PSMAINORVW  
         ON  
         ORDER_NUMBER= DIODR#  
         AND DIDISP = NUMBER_DISP  
         LEFT OUTER JOIN  
         IESQAFILE.LMCARR  
         ON  
         DIUNIT = BCCARR  
         LEFT OUTER JOIN  
         IESQAFILE.MMILES  
         ON  
         MMORD# = DIODR#  
         AND MMRECTYPE = 'D'  
         AND MMDSP# = DIDISP  
         LEFT OUTER JOIN  
         STOPGROUP  
         ON  
         STOPORDER = DIODR#  
         LEFT OUTER JOIN  
         IESQAFILE.DRIVERS  
         ON  
         DRCODE = DIDR1  
         LEFT OUTER JOIN  
         IESQAFILE.UNITS  
         ON  
         UNUNIT = DIUNIT  
         AND UNORD# = ORDER_NUMBER  
         LEFT OUTER JOIN  
         IESQAFILE.SUPMAST  
         ON  
         SUPCDE = UNSUPR  
         LEFT OUTER JOIN  
         IESQAFILE.FLTMAST  
         ON  
         UNFMGR = FLTCDE  
        WHERE  
         DIETAD = 0  
         AND DIETAT = '0000'  
    RCDFMT PSINPROGVW ; 
   
LABEL ON COLUMN IESQAFILE.PSINPROGVW 
( ORDER_NUMBER IS 'ORDER#' , 
    SHIPPER_ID IS 'SHIPPER' , 
    SHIPPER_NAME IS 'Name' , 
    SHIPPER_ADDRESS_1 IS 'Address1' , 
    SHIPPER_ADDRESS_2 IS 'Address2' , 
    SHIPPER_CITY IS 'City' , 
    SHIPPER_ST IS 'State' , 
    SHIPPER_ZIP IS 'Zip' , 
    SHIPPER_ZIP_EXT IS 'Zip                 Ext' , 
    LOAD_AT_ID IS 'LOAD                AT' , 
    LOAD_AT_NAME IS 'Name' , 
    LOAD_AT_ADDRESS_1 IS 'Address1' , 
    LOAD_AT_ADDRESS_2 IS 'Address2' , 
    LOAD_AT_CITY IS 'City' , 
    LOAD_AT_ST IS 'State' , 
    LOAD_AT_ZIP IS 'Zip' , 
    LOAD_AT_ZIP_EXT IS 'Zip                 Ext' , 
    LOAD_AT_LATITUDE IS 'Latitude            Decimal' , 
    LOAD_AT_LONGITUDE IS 'Longitude           Decimal' , 
    ORDER_DIV IS 'DIV                 #' , 
    CONSIGNEE_ID IS 'CUSTOMER' , 
    CONSIGNEE_NAME IS 'Name' , 
    CONSIGNEE_ADDRESS_1 IS 'Address1' , 
    CONSIGNEE_ADDRESS_2 IS 'Address2' , 
    CONSIGNEE_CITY IS 'City' , 
    CONSIGNEE_ST IS 'State' , 
    CONSIGNEE_ZIP IS 'Zip' , 
    CONSIGNEE_ZIP_EXT IS 'Zip                 Ext' , 
    CONSIGNEE_LATITUDE IS 'Latitude            Decimal' , 
    CONSIGNEE_LONGITUDE IS 'Longitude           Decimal' , 
    TRAILER_TYPE IS 'TRLR                TYPE' , 
    ORDER_MESSAGE IS 'SPEC' , 
    ADDITIONAL_STOPS IS 'STP                 COM                 #' , 
    CMDTY_CODE IS 'CMDTY               CODE' , 
    CMDTY_DESCRIPTION IS 'CMDTY               DESC.' , 
    ORDER_WGT IS 'ORD                 WGT' , 
    ORIGIN_CITY_CODE IS 'ORG                 CITY' , 
    ORIGIN_CITY IS 'City Name' , 
    ORIGIN_ST IS 'ORG                 ST' , 
    DEST_CITY_CODE IS 'DEST                CITY' , 
    DEST_CITY_NAME IS 'City Name' , 
    DEST_ST IS 'DEST                ST' , 
    PICK_UP_AREA IS 'P/U                 AREA' , 
    PLAN_INFO IS 'PREASS              TRAC' , 
    NUMBER_LDS IS '#                   LDS' , 
    NUMBER_DISP IS '#                   DISP' , 
    NEW_PICKUP_AREA IS 'NEW                 P/U                 AREA' , 
    LOAD_DISPATCHED IS 'LDS                 DISP' , 
    CUST_SERV_REP IS 'Init' ) ; 
   
LABEL ON COLUMN IESQAFILE.PSINPROGVW 
( ORDER_NUMBER TEXT IS 'ORDER#' , 
    SHIPPER_ID TEXT IS 'SHIPPER' , 
    SHIPPER_NAME TEXT IS 'Name' , 
    SHIPPER_ADDRESS_1 TEXT IS 'Address1' , 
    SHIPPER_ADDRESS_2 TEXT IS 'Address2' , 
    SHIPPER_CITY TEXT IS 'City' , 
    SHIPPER_ST TEXT IS 'State' , 
    SHIPPER_ZIP TEXT IS 'Zip' , 
    SHIPPER_ZIP_EXT TEXT IS 'Zip Extension' , 
    LOAD_AT_ID TEXT IS 'LOAD AT' , 
    LOAD_AT_NAME TEXT IS 'Name' , 
    LOAD_AT_ADDRESS_1 TEXT IS 'Address1' , 
    LOAD_AT_ADDRESS_2 TEXT IS 'Address2' , 
    LOAD_AT_CITY TEXT IS 'City' , 
    LOAD_AT_ST TEXT IS 'State' , 
    LOAD_AT_ZIP TEXT IS 'Zip' , 
    LOAD_AT_ZIP_EXT TEXT IS 'Zip Extension' , 
    LOAD_AT_LATITUDE TEXT IS 'Latitude Decimal' , 
    LOAD_AT_LONGITUDE TEXT IS 'Longitude Decimal' , 
    EST_REVENUE TEXT IS 'EST. REV.' , 
    ORDER_DIV TEXT IS 'DIVISION NUMBER' , 
    CONSIGNEE_ID TEXT IS 'CUSTOMER' , 
    CONSIGNEE_NAME TEXT IS 'Name' , 
    CONSIGNEE_ADDRESS_1 TEXT IS 'Address1' , 
    CONSIGNEE_ADDRESS_2 TEXT IS 'Address2' , 
    CONSIGNEE_CITY TEXT IS 'City' , 
    CONSIGNEE_ST TEXT IS 'State' , 
    CONSIGNEE_ZIP TEXT IS 'Zip' , 
    CONSIGNEE_ZIP_EXT TEXT IS 'Zip Extension' , 
    CONSIGNEE_LATITUDE TEXT IS 'Latitude Decimal' , 
    CONSIGNEE_LONGITUDE TEXT IS 'Longitude Decimal' , 
    TRAILER_TYPE TEXT IS 'TRAILER TYPE' , 
    ORDER_MESSAGE TEXT IS 'SPECIAL' , 
    ADDITIONAL_STOPS TEXT IS '# OF STOPS & COMMENTS' , 
    CMDTY_CODE TEXT IS 'COMMODITY CODE' , 
    CMDTY_DESCRIPTION TEXT IS 'COMMODITY DESCRIPTION' , 
    ORDER_WGT TEXT IS 'ORDER WEIGHT' , 
    ORIGIN_CITY_CODE TEXT IS 'ORIGIN CITY' , 
    ORIGIN_CITY TEXT IS 'CITY NAME' , 
    ORIGIN_ST TEXT IS 'ORIGIN ST' , 
    DEST_CITY_CODE TEXT IS 'DESTINATION CITY' , 
    DEST_CITY_NAME TEXT IS 'CITY NAME' , 
    DEST_ST TEXT IS 'DESTINATION ST' , 
    PICK_UP_AREA TEXT IS 'P/U AREA' , 
    PLAN_INFO TEXT IS 'PREASSIGNED TRACTOR' , 
    NUMBER_LDS TEXT IS 'NUMBER OF LOADS' , 
    NUMBER_DISP TEXT IS 'NUMBER OF DISPATCHES' , 
    NEW_PICKUP_AREA TEXT IS 'NEW PICKUP AREA' , 
    LOAD_DISPATCHED TEXT IS 'LOADS DISPATCHED TO DESTINATION' , 
    CUST_SERV_REP TEXT IS 'Customer Service Rep.' ) ; 
   
GRANT ALTER , REFERENCES , SELECT   
ON IESQAFILE.PSINPROGVW TO QPGMR WITH GRANT OPTION ;

0

Добавить комментарий

Ваш адрес email не будет опубликован.