Summing the prices from Transaction files

I need to cut down the run time of this query. Currently it's taking 45 minutes. Is there something I can change in the table or the query to allow this to run faster?

SELECT SUM(B.PRICE),C.ST_FILE
FROM
    TAD1D.ST_EUCM AS B,
    TAD1D.ST_DSA_TRAN  AS C,
    TAD1A.TIBHLD T
WHERE C.ST_TRAN_FILE=B.ST_TRAN_FILE
    AND C.ST_IND='Y' 
    AND C.ST_SRC_SYS_CD='HANN'
    AND C.ST_TBL_NAME='ST_EUCM'
    AND B.PARTNUMB=T.ACCT_NUM
    AND B.FVSRVCCODE=T.SRV_CD
GROUP BY C.ST_FILE
EXCEPT
SELECT sum(A.ACCUM_C),D.ST_FILE from
                    TAD1A.TIBHLD_DLY_VAL A,
                    TAD1D.ST_DSA_TRAN  AS D
            where A.SRC_SYS_CD = 'HANN'
                    AND D.ST_SRC_SYS_CD=A.SRC_SYS_CD
                    AND D.ST_TBL_NAME='ST_EUCM'
                    AND DATE(A.HLD_VAL_DT)=DATE(D.ST_FILE)
                    GROUP BY D.ST_FILE
                    WITH UR --

There are no indexes on either table. Please provide a tip/solution in the event i am able to change the table structure or add an index (I may not).

Nitpicks

There is a slight amount of inconsistency in your capitalization of keywords. SUM() / sum() and WHERE / where. Otherwise, it's pretty consistent throughout!

Aliases

You use the following aliases: B C T. There is no meaning or explanation of what those mean. You should use meaningful aliases, especially where your table names don't really help, such as in your case.

Old-style JOIN

This:

FROM
    TAD1D.ST_EUCM AS B,
    TAD1D.ST_DSA_TRAN  AS C,
    TAD1A.TIBHLD T
WHERE C.ST_TRAN_FILE=B.ST_TRAN_FILE
    AND C.ST_IND='Y' 
    AND C.ST_SRC_SYS_CD='HANN'
    AND C.ST_TBL_NAME='ST_EUCM'
    AND B.PARTNUMB=T.ACCT_NUM
    AND B.FVSRVCCODE=T.SRV_CD

...is deprecated pre-ANSI-92 syntax. It should not be used, rather you should favor explicit JOIN syntax, like such:

FROM
    TAD1D.ST_EUCM AS B
INNER JOIN 
    TAD1D.ST_DSA_TRAN  AS C
    ON C.ST_TRAN_FILE = B.ST_TRAN_FILE
INNER JOIN 
    TAD1A.TIBHLD T
    ON B.PARTNUMB = T.ACCT_NUM
    AND B.FVSRVCCODE = T.SRV_CD
WHERE
    AND C.ST_IND = 'Y' 
    AND C.ST_SRC_SYS_CD = 'HANN'
    AND C.ST_TBL_NAME = 'ST_EUCM'

Notice I added spaces around your = operators as it makes the code easier to read. You would also want to use similar JOIN syntax in your subquery.

Indexes

You stated:

There are no indexes on either table. Please provide a tip/solution in the event i am able to change the table structure or add an index (I may not).

And to that, I say resoundingly: YES! Add indexes!

But make sure even before you do that, look carefully at your query execution plan and look at where the most expensive steps are. That should give you a clue as to what is happening. I suspect there are a few nasty nested loops in there that are bogging it down, and depending on your RDBMS there are some optimizations available, and there may be ways to change the execution manually.

StackExchange Code Review Q#72155, answer score: 8