user2075861 user2075861 - 6 months ago 25
SQL Question

IBM Db2 Sql query optimization

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

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)`