Query taking way to long

From: Chuck <chuck_at_phi.org>
Date: Mon, 08 Nov 1999 23:52:45 GMT
Message-ID: <38275f9e.23164008@client.nw.news.psi.net>

I have the following query that was built by Seagate Crystal Reports (7). The query takes a very long amount of time against a database with relatively little data. The query is taking anywhere from 30 to 60 minutes on an idle box (Sun E3500 with 2 cpu's and 2 gig of ram) While monitoring TEMP tablespace I noticed that is uses up about 126Megs of space (out of 300Meg) and then continues to consume CPU cycles. About 25% of the query time is used writing to TEMP, the remaining 75% is all CPU time. I have tried various arrangements of indexes but none seem to affect the query time.

Here is the query:

SELECT
    ACACCTCATX."ACTIVITY", ACACCTCATX."ACCT_CATEGORY", ACACCTCATX."ACCT_CAT_CLASS",
    ACLEVEL."POSTING_FLAG",
    ACACCTCLSS."DESCRIPTION",
    ACACTIVITY."ACTIVITY", ACACTIVITY."DESCRIPTION",     ACBUDDTLX."FISCAL_YEAR", ACBUDDTLX."PERIOD", ACBUDDTLX."AMOUNT",     ACTRANS."TRAN_AMOUNT", ACTRANS."PERIOD", ACTRANS."FISCAL_YEAR" FROM
"LAWDEV"."ACACCTCATX" ACACCTCATX,
"LAWDEV"."ACLEVEL" ACLEVEL,
"LAWDEV"."ACACCTCLSS" ACACCTCLSS,
"LAWDEV"."ACMASTERX" ACMASTERX,
"LAWDEV"."ACACTIVITY" ACACTIVITY,
"LAWDEV"."ACBUDDTLX" ACBUDDTLX,
"LAWDEV"."ACTRANS" ACTRANS

WHERE

    ACACCTCATX."ACTIVITY_GRP" = ACLEVEL."ACTIVITY_GRP" AND
    ACACCTCATX."ACTIVITY" = ACLEVEL."ACTIVITY" AND
    ACACCTCATX."ACCT_CAT_CLASS" = ACACCTCLSS."ACCT_CAT_CLASS" AND
    ACACCTCATX."ACTIVITY" = ACMASTERX."ACTIVITY" AND
    ACACCTCATX."ACCT_CATEGORY" = ACMASTERX."ACCT_CATEGORY" AND
    ACACCTCATX."ACTIVITY" = ACACTIVITY."ACTIVITY" AND
    ACMASTERX."ACTIVITY" = ACBUDDTLX."ACTIVITY" AND
    ACMASTERX."ACCT_CATEGORY" = ACBUDDTLX."ACCT_CATEGORY" AND

    ACMASTERX."ACTIVITY" = ACTRANS."ACTIVITY" AND     ACMASTERX."ACCT_CATEGORY" = ACTRANS."ACCT_CATEGORY" AND     ACLEVEL."POSTING_FLAG" = 'P' AND
    ACTRANS."FISCAL_YEAR" = 1999. AND
    ACBUDDTLX."PERIOD" >= 1. AND
    ACBUDDTLX."PERIOD" <= 10. AND
    (ACACCTCATX."ACTIVITY" = '01995-01-01' OR     ACACCTCATX."ACTIVITY" = '01596-19-05') ORDER BY
    ACACCTCATX."ACCT_CAT_CLASS" ASC,
    ACACCTCLSS."DESCRIPTION" ASC Here are the number of rows in each of the tables:

ACACCTCATX=4389
ACLEVEL=72
ACACCTCLSS=55
ACMASTERX=73
ACACTIVITY=72
ACBUDDTLX=7882
ACTRANS=141 I can provide further info such as table descriptions if needed.

NOTE: By removing the last section of the WHERE clause containing the OR has relatively no effect on the query completion time.

Thanks greatly,
Chuck Received on Mon Nov 08 1999 - 17:52:45 CST