GROUP BY with SUM takes too long

Kincaco · Jul 25, 2000

In MS Access, I can run this SQL in about 9 minutes.  I am trying to convert it to an Oracle view so that users can link to the results instead of having to export it from Access and import it into other applications that don't support SQL.  I am having difficulty with performance issues without any GROUP BY.  With the GROUP BY I run out of space in TEMP.  Any help would be appreciated. MS Access SQL: SELECT DISTINCTROW CSIOWNER_PLGR.ORG_ID, CSIOWNER_PLGR.PROJ_ELEM_ID, CSIOWNER_CEPM.COST_ELEM_DESC, CSIOWNER_PLGR.COST_ELEM, CSIOWNER_PLGR.PRD, Sum(IIf([SUM_UNIT]="C",[PRD_ACT],0)) AS PRDACT, CSIOWNER_PLGR.SUM_UNIT, Sum(IIf([SUM_UNIT]="R",[PRD_ACT],0)) AS PRD_HRS FROM CSIOWNER_PLGR INNER JOIN CSIOWNER_CEPM ON (CSIOWNER_PLGR.COST_ELEM = CSIOWNER_CEPM.COST_ELEM) AND (CSIOWNER_PLGR.ORG_ID = CSIOWNER_CEPM.ORG_ID) GROUP BY CSIOWNER_PLGR.ORG_ID, CSIOWNER_PLGR.PROJ_ELEM_ID, CSIOWNER_CEPM.COST_ELEM_DESC, CSIOWNER_PLGR.COST_ELEM, CSIOWNER_PLGR.PRD, CSIOWNER_PLGR.SUM_UNIT HAVING (((CSIOWNER_PLGR.ORG_ID) Like [FORMS]![FLAUNCHER]![CO]) AND ((CSIOWNER_PLGR.COST_ELEM)<>"L738&quot

) ORDER BY CSIOWNER_PLGR.ORG_ID, CSIOWNER_PLGR.PROJ_ELEM_ID, CSIOWNER_PLGR.COST_ELEM; The Oracle SQL runs out of TEMP space: SELECT DISTINCT PLGR.ORG_ID, PROJ_ELEM_ID, PLGR.COST_ELEM, PRD, SUM_UNIT, CEPM.COST_ELEM_DESC, SUM(DECODE(SUM_UNIT,'C',PRD_ACT,0)) AS PRDACT, SUM(DECODE(SUM_UNIT,'R',PRD_ACT,0)) AS PRD_HRS FROM PLGR, CEPM GROUP BY PLGR.ORG_ID, PLGR.PROJ_ELEM_ID, CEPM.COST_ELEM_DESC, PLGR.COST_ELEM, PLGR.PRD, PLGR.SUM_UNIT, CEPM.ORG_ID, CEPM.COST_ELEM HAVING CEPM.COST_ELEM = PLGR.COST_ELEM AND CEPM.ORG_ID = PLGR.ORG_ID AND PRD BETWEEN 9901 AND 9912; It doesn't matter if I use a date range or not (9901 to 9912).  Any help would be appreciated. Thanks, Connie

MalcolmW · Jul 27, 2000

Your join (or lack of it) is the problem - putting it in the WHERE clause rather than the HAVING clause is better, ideally it should be in the FROM clause Your HAVING clause doesn't get evaluated until after all the summaries are calculated.  Your temp file is filled up with the number of rows in PLGR times the number in CEPM.  Then all the rows that don't meet the HAVING criteria are discarded.   ...better... FROM PLGR, CEPM WHERE CEPM.COST_ELEM = PLGR.COST_ELEM AND CEPM.ORG_ID = PLGR.ORG_ID AND PRD BETWEEN 9901 AND 9912; ... or ...best... FROM PLGR INNER JOIN CEPM ON CEPM.COST_ELEM = PLGR.COST_ELEM      AND CEPM.ORG_ID = PLGR.ORG_ID WHERE PRD BETWEEN 9901 AND 9912; ... Malcolm Wynden <a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

GROUP BY with SUM takes too long

Kincaco

Programmer

MalcolmW

Programmer

Similar threads

Part and Inventory Search

Sponsor