Re: Query taking way to long

Hi Chuck,

I think the optimizer has some problems with this query. Are the statistics filled properly?

Try

analyze table ACACCTCATX compute statistics for all indexed columns

and afterwards

analyze table ACACCTCATX compute statistics

Do it for all affected tables.

If it has no effect, try the EXPLAIN PLAN utility, to see what the execution plan is.

Bye
Uli

> 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 Tue Nov 09 1999 - 04:08:49 CST