Hi Mike!

I'm sorry to say that the plan doesn't seem all too bad, though it may
take some time if TE_TRAN contains a lot of records or if some of the
indexes used in the plan has low selectivity.

For the rest of my answer, I have assumed that you have indexes for
ACCT_TRAN_MASTER.ACCT_TRAN_ID and TE_TRAN.TRAN_ID. Also, it would be
benefitial if there existed indexes for selective fields in
ACCT_TRAN_DETAIL.

It might be better (or it may be worse) to have AT1 as the first table
of the plan, and this can probably be achieved by changing one JOIN clause:

JOIN ACCT_TRAN_MASTER ATM ON ATM.ACCT_TRAN_ID = AT1.ACCT_TRAN_ID + 0

Another part that you may change (though I don't know whether it will
affect the chosen plan or performance), is this:

WHERE (AT1.QUE_STATUS_CODE = 'W'
OR (AT1.QUE_STATUS_CODE IN ('P', 'O')
AND coalesce(T.TRAN_TYPE_ID, 0) <> -3))

As for the second time being faster, I would assume that to be due to
things being stored in cache. 'Run' vs 'fetch all' is probably just
showing that 'Run' only loads as many records (or a few more) as is
required to fill the screen. I would expect going to the last record not
to be very different from 'fetch all' (well, unless it utilizes some
features to skip records).

HTH,
Set

SoftTech wrote:

> Notice that the second dataset takes a long time to open. I have

seen the time to open this dataset take anywhere from as little as
01:32.0204 to as long as 10:19.0031.

>
> Here is the SQL Statement:
>
> SELECT AT1.*,
> ATM.COMPANY_ID,
> ATM.DEPT_ID,
> ATM.TRAN_ID,
> ATM.TRAN_PLAN_ID,
> T.DESCRIPTION,
> T.TRAN_BASED_ON,
> T.EVALUATE_DEBT_STATUS,
> TP.PROCESS_WITH_PAY_PLAN,
> ATQS.DESCRIPTION AS QUE_DESC,
> ATQS2.DESCRIPTION AS PREV_QUE_DESC,
> ATM.QUE_STATUS_CODE AS TRAN_STATUS,
> ATQS2.DESCRIPTION AS TRAN_STATUS_DESC,
> CAST(1 AS SMALLINT) AS CAN_RUN
> FROM ACCT_TRAN_DETAIL AT1
> JOIN ACCT_TRAN_MASTER ATM ON ATM.ACCT_TRAN_ID = AT1.ACCT_TRAN_ID
> JOIN TE_TRAN T on T.TRAN_ID = ATM.TRAN_ID
> LEFT OUTER JOIN TE_PLAN_TRAN TP ON TP.TRAN_PLAN_ID = ATM.TRAN_PLAN_ID
> AND TP.TRAN_ID = ATM.TRAN_ID
> LEFT OUTER JOIN ACCT_TRAN_QUE_STATUS ATQS ON ATQS.QUE_STATUS_CODE =

AT1.QUE_STATUS_CODE

> LEFT OUTER JOIN ACCT_TRAN_QUE_STATUS ATQS2 ON ATQS2.QUE_STATUS_CODE

= AT1.PREV_QUE_STATUS_CODE

> LEFT OUTER JOIN ACCT_TRAN_QUE_STATUS ATQS3 ON ATQS3.QUE_STATUS_CODE

= ATM.QUE_STATUS_CODE

> WHERE ((T.TRAN_TYPE_ID = -3 AND AT1.QUE_STATUS_CODE = 'W')
> OR (((T.TRAN_TYPE_ID IS NULL) OR (T.TRAN_TYPE_ID <> -3)) AND

AT1.QUE_STATUS_CODE IN ('P', 'W', 'O')))

> AND ((CAST(AT1.LAST_SERVICE_DATE AS DATE) < CURRENT_DATE) OR

(AT1.LAST_SERVICE_DATE IS NULL))

> AND ((AT1.NEXT_RUN_DATE <= CURRENT_DATE) OR (AT1.NEXT_RUN_DATE IS

NULL))

> AND AT1.ALLOW_SYSTEM_UPDATE = 1
> AND AT1.ACTIVE_TRAN = 1
> AND (AT1.TRAN_DATE <= CURRENT_DATE)
> ORDER BY AT1.ACCT_ID, AT1.POSITION_INDEX
>
> Here is the Plan according to Database Workbench:
>
> PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (T NATURAL,ATM INDEX

(REFTE_TRAN259),AT1 INDEX (REFACCT_TRAN_MASTER777)),TP INDEX
(PK_TE_PLAN_TRAN)),ATQS INDEX (PK_ACCT_TRAN_QUE_STATUS)),ATQS2 INDEX
(PK_ACCT_TRAN_QUE_STATUS)),ATQS3 INDEX (PK_ACCT_TRAN_QUE_STATUS)))

>
> In order to retreive this plan I execute the above SQL in DBW where I

clicked the Prepare Query button, follows by the Run - Fetch All button,
and it took 6 Min, 56.391 sec - rows fetched: 77578

>
> Now here is something I do not understand. The above query took

almost 7 minutes to return 77578 records. If I clicked the Prepare
Query button again, follows by the Run - Fetch All button again, and it
takes only 41.453 sec - rows fetched: 77578. Why does it take so long
the first time, but only a fraction of the time to do it a second time?

>
> If I click the Prepare Query button, follows by the Run button, and

it takes only 34.046 sec - rows fetched: 11

>
> Anyway, if there is anyway that anyone can see to speed this query up

I would really aappreaciate any guidance.

>
> If you need any further information to help diagnose this, please do

not hesitate to ask.

>
> Thanks for anyone who can point me in the right direction.
>
> Mike