У меня есть представление 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 ;