Greetings All,

Using Firebird 1.5.3
Delphi 5.1 (TClientDataSet > TDataSetProvider > TIBQuery)

I inherited this project from another developer. The project is a transaction processing application, that will load all transaction that need to be processed. It runs every half hour during the business day to continue to move legal cases forward. It opens and closes these datasets multiple times during each run. Every time it runs it has to open 27 Datasets as shown here:

12/18/2008 05:24:51 PM - 01 cdsFeeCode : 00:00.0125
12/18/2008 05:30:03 PM - 02 cdsAcctTran : 05:12.0500
12/18/2008 05:30:03 PM - 03 cdsAcctTranTask : 00:00.0250
12/18/2008 05:30:03 PM - 04 cdsAcctTranTaskStartDate : 00:00.0047
12/18/2008 05:30:03 PM - 05 cdsAcctTranTaskFee : 00:00.0063
12/18/2008 05:30:04 PM - 06 cdsAcctTranTaskForward : 00:00.0062
12/18/2008 05:30:04 PM - 07 cdsAcctTranTaskGuar : 00:00.0047
12/18/2008 05:30:04 PM - 08 cdsAcctTranTaskIntCalc : 00:00.0016
12/18/2008 05:30:04 PM - 09 cdsAcctTranTaskCredRep : 00:00.0015
12/18/2008 05:30:04 PM - 10 cdsAcctTranTaskChkVer : 00:00.0032
12/18/2008 05:30:04 PM - 11 cdsAcctTranTaskPlan : 00:00.0046
12/18/2008 05:30:04 PM - 12 cdsAcctTranTaskPlanStop : 00:00.0032
12/18/2008 05:30:04 PM - 13 cdsAcctTranTaskElecPmt : 00:00.0047
12/18/2008 05:30:04 PM - 14 cdsAcctTranTaskChgSts : 00:00.0187
12/18/2008 05:30:04 PM - 15 cdsAcctTranTaskDebts : 00:00.0453
12/18/2008 05:30:04 PM - 16 cdsAcctTranTaskWait : 00:00.0031
12/18/2008 05:30:05 PM - 17 cdsAcctTranTaskRev : 00:00.0063
12/18/2008 05:30:05 PM - 18 cdsCase : 00:00.0297
12/18/2008 05:30:05 PM - 19 cdsCaseDebt : 00:00.0031
12/18/2008 05:30:05 PM - 20 cdsCaseDebtor : 00:00.0047
12/18/2008 05:30:05 PM - 21 cdsAcctTranDebt : 00:00.0031
12/18/2008 05:30:05 PM - 22 cdsClientSite : 00:00.0016
12/18/2008 05:30:05 PM - 23 cdsDebtDebtor : 00:00.0234
12/18/2008 05:30:05 PM - 24 cdsDebtChk : 00:00.0078
12/18/2008 05:30:05 PM - 25 cdsAcctPlan : 00:00.0125
12/18/2008 05:30:05 PM - 26 cdsAcctPlanDebtor : 00:00.0063
12/18/2008 05:30:05 PM - 27 cdsChkVerExport : 00:00.0062

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

[Non-text portions of this message have been removed]