IBM Db2 Sql query optimization

Question

I have this query:

select DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,  PCSACST.CSTTYAPP, 
SUM(PCSACST.CSTBURD1 + PCSACST.CSTBURD2 + PCSACST.CSTLABOR + 
PCSACST.CSTMAT + PCSACST.CSTSETUP + PCSACST.CSTEXTRN) as COSTO_STD 
from MVSACPE, PCSACST, DBSAITE
WHERE PCSACST.CSTCOSC = MVSACPE.CODSOC
AND PCSACST.CSTITEM = MVSACPE.CODITM
AND MVSACPE.CODITM = DBSAITE.MITEM
AND DBSAITE.MCOSC = 'GRS'
AND MVSACPE.CODSOC = 'GRS23'
AND MVSACPE.DTPERI = '201512'
AND MVSACPE.CODMAG = 'ALL'
AND MVSACPE.CODCOS = 'MPR' 
AND PCSACST.CSTDATE = '20150630'
AND PCSACST.CSTTYPE = 'SG'
GROUP BY DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,PCSACST.CSTTYAPP

I'd like to optimize the query someway, because join works with hundreds of thousands records and is very slow (3 minutes).

Any advice?

Answer 1

First, rewrite the query using explicit JOIN syntax. If nothing else, this makes it easier to see what to do:

select DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,  PCSACST.CSTTYAPP, 
SUM(PCSACST.CSTBURD1 + PCSACST.CSTBURD2 + PCSACST.CSTLABOR + 
PCSACST.CSTMAT + PCSACST.CSTSETUP + PCSACST.CSTEXTRN) as COSTO_STD 
from MVSACPE JOIN
     PCSACST
     ON PCSACST.CSTCOSC = MVSACPE.CODSOC AND
        PCSACST.CSTITEM = MVSACPE.CODITM JOIN
     DBSAITE
     ON MVSACPE.CODITM = DBSAITE.MITEM
WHERE DBSAITE.MCOSC = 'GRS' AND
      MVSACPE.CODSOC = 'GRS23' AND
      MVSACPE.DTPERI = '201512' AND
      MVSACPE.CODMAG = 'ALL' AND
      MVSACPE.CODCOS = 'MPR' AND
      PCSACST.CSTDATE = '20150630' AND
      PCSACST.CSTTYPE = 'SG'
GROUP BY DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,PCSACST.CSTTYAPP;

Indexes will probably help the query. It is unclear which of the conditions are most selective. Lacking other information, a good place to start is with these:

- `MVSACPE(CODSOC, DTPERI, CODMAG, CODCOS, CODITM)`
- `PCSACST(CSTCOSC, CSTITEM, CSTDATE, CSTTYPE)`
- `DBSAITE(MITEM, MCOSC)`