Проблема с LedgerJournalTrans

[DSPIC]

Если SQL 2000, то тут с index hint конечно очень аккуратно нужно быть. Если юзается индекс, то порядок полей в условии нужно подгонять под порядок полей в индексе, по возможности. Если не получается то index вообще лучше убрать.

Не ломая кверю GroupFinancLedgerJournalTrans, попробуйте ещё такой вариант, по-идее будет быстрее

(убрал некрасивый цикл, заменил ledgerTrans на qLedgerTrans везде где используется)

    QueryRun            qrLedgerJour;
    RContractTable      qContractTable;
    LedgerJournalTrans  qLedgerTrans;
    LedgerJournalTrans  ledgerTrans;
    LedgerJournalTable  ledgerTable;
    CustSettlement      custSettlement;
    CustSettlement      settlement;
    CustTrans           custTrans, custTransSelect;
    ;


    qrLedgerJour = new QueryRun(queryStr(GroupFinancLedgerJournalTrans));

    qrLedgerJour.query().dataSourceTable(tableNum(LedgerJournalTrans)).addRange(fieldnum(LedgerJournalTrans,TransDate)).value(queryRange(dateFrom,dateTo));
    qrLedgerJour.query().dataSourceTable(tableNum(LedgerJournalTrans)).addRange(fieldnum(LedgerJournalTrans,AmountCurCredit)).value("!=0");
    qrLedgerJour.query().dataSourceTable(tableNum(LedgerJournalTrans)).addRange(fieldnum(LedgerJournalTrans,AccountType)).value(enum2str(LedgerJournalACType::Cust));
    qrLedgerJour.query().dataSourceTable(tableNum(RContractTable)).addRange(fieldnum(RContractTable,RContractPartnerType)).value(enum2str(RContractPartnerType::Cust));

    if (rangeDimension)
        qrLedgerJour.query().dataSourceTable(tableNum(LedgerJournalTrans)).addRange(fieldId2ext(fieldnum(LedgerJournalTrans, Dimension),2)).value(rangeDimension);
    if (rangeGroupFinanc)
        qrLedgerJour.query().dataSourceTable(tableNum(RContractTable)).addRange(fieldnum(RContractTable,GroupFinancId)).value(rangeGroupFinanc);
    if (accountNum)
        qrLedgerJour.query().dataSourceTable(tableNum(LedgerJournalTrans)).addRange(fieldNum(LedgerJournalTrans,AccountNum)).value(accountNum);
    if (contractAccount)
    {
        qrLedgerJour.query().dataSourceTable(tableNum(RContractTable)).addRange(fieldNum(RContractTable,RContractCode)).value(contractCode);
        qrLedgerJour.query().dataSourceTable(tableNum(RContractTable)).addRange(fieldNum(RContractTable,RContractAccount)).value(contractAccount);
        qrLedgerJour.query().dataSourceTable(tableNum(RContractTable)).addRange(fieldNum(RContractTable,RContractPartnerCode)).value(accountNum);
    }

    while (qrLedgerJour.next())
    {
        qLedgerTrans    = qrLedgerJour.get(tableNum(LedgerJournalTrans));
        qContractTable  = qrLedgerJour.get(tableNum(RContractTable));

        // dspic -->
        // orig:
        //while select RecId, budget, JournalNum, Voucher, AccountNum, AmountCurCredit from ledgerTrans
        //    where ledgerTrans.RecId        == qLedgerTrans.RecId
        ///*dspic(*/ exists /*)dpsic*/ join JournalNum, Posted, JournalType from ledgerTable
        //    index hint PostedJournalNumIdx
        //    where ledgerTable.JournalNum    == ledgerTrans.JournalNum   &&
        //          ledgerTable.Posted        == NoYes::Yes
        if (qLedgerTrans.ledgerJournalTable().posted)
        // dspic <--
        {
            select firstonly Voucher,AccountNum,SettleAmountCur,TransDate,AmountCur from custTrans
            while select Voucher, AccountNum, SettleAmountCur, TransDate, AmountCur from custTrans
                index hint AccountDateIdx
//dspic:
                where custTrans.Voucher     == qLedgerTrans.Voucher  && //dspic: ledgerTrans --> qLedgerTrans
                      custTrans.AccountNum  == qLedgerTrans.AccountNum   //dspic: ledgerTrans --> qLedgerTrans
            {

                if (custTrans.AmountCur - custTrans.SettleAmountCur != 0)
                {
                    tmpTable.clear();
//dspic:
                    if (qLedgerTrans.budget == NoYes::Yes) //dspic: ledgerTrans --> qLedgerTrans
                    {
                        tmpTable.BPayCurrent    = custTrans.AmountCur - custTrans.SettleAmountCur;
                        tmpTable.PayCurrent     = 0;
                    }
                    else
                    {
                        tmpTable.PayCurrent     = custTrans.AmountCur - custTrans.SettleAmountCur;
                        tmpTable.BPayCurrent    = 0;
                    }
                    tmpTable.GroupFinancName    = ICLGroupsFinancTable::find(qContractTable.GroupFinancId).GroupFinancName;
                    tmpTable.AccountNum         = qLedgerTrans.AccountNum;
                    tmpTable.AccountName        = CustTable::find(qLedgerTrans.AccountNum).NameAlias;
                    tmpTable.ContractNumberDate = qContractTable.contractNumberDate;
                    tmpTable.Voucher            = qLedgerTrans.Voucher;
                    tmpTable.AddQty             = 0;
                    tmpTable.AddAmount          = 0;
                    tmpTable.DebtBeginPeriod    = 0;
                    tmpTable.Advance            = NoYes::Yes;
                    tmpTable.CurrentPayTotal    = tmpTable.BPayCurrent + tmpTable.PayCurrent;
                    tmpTable.PayPeriodTotal     = tmpTable.BPay + tmpTable.Pay + tmpTable.CurrentPayTotal;
                    tmpTable.CurrentDebt        = tmpTable.AddAmount + tmpTable.CurrentPayTotal;
                    tmpTable.EndDebt            = tmpTable.DebtBeginPeriod + tmpTable.AddAmount + tmpTable.PayPeriodTotal/*CurrentPayTotal*/;

                    tmpTable.insert();
                }
                while select OffsetTransVoucher, AccountNum, OffsetRecid, TransRecId, SettleAmountCur from custSettlement
                    index hint TransIndex //orig:  index hint OffsetVoucherIdx
                    //where custSettlement.OffsetTransVoucher == ledgerTrans.Voucher      &&
                    //      custSettlement.AccountNum         == ledgerTrans.AccountNum   &&
                    where custSettlement.TransRecId    == custTrans.RecId      &&
                          custSettlement.AccountNum    == custTrans.AccountNum &&
                          custSettlement.CanBeReversed == NoYes::Yes

                {
                   select firstonly Voucher, AccountNum, SettleAmountCur, TransDate from custTransSelect
                        index hint AccountDateIdx
                        //where custTransSelect.Voucher     == settlement.OffsetTransVoucher  &&
                        where custTransSelect.RecId      == custSettlement.OffsetRecid  &&
                              custTransSelect.AccountNum == custSettlement.AccountNum;

                    tmpTable.clear();
//dspic:
                    if (qLedgerTrans.budget == NoYes::Yes)  //dspic: ledgerTrans --> qLedgerTrans
                    {
                        if (custTransSelect.TransDate >= dateFrom)
                        {
                            tmpTable.BPayCurrent    = custSettlement.SettleAmountCur;
                            tmpTable.BPay           = 0;
                            tmpTable.PayCurrent     = 0;
                            tmpTable.Pay            = 0;
                        }
                        else
                        {
                            tmpTable.BPayCurrent    = 0;
                            tmpTable.BPay           = custSettlement.SettleAmountCur;
                            tmpTable.PayCurrent     = 0;
                            tmpTable.Pay            = 0;
                        }
                    }
                    else
                    {
                        if (custTransSelect.TransDate >= dateFrom)
                        {
                            tmpTable.BPayCurrent    = 0;
                            tmpTable.BPay           = 0;
                            tmpTable.PayCurrent     = custSettlement.SettleAmountCur;
                            tmpTable.Pay            = 0;
                        }
                        else
                        {
                            tmpTable.BPayCurrent    = 0;
                            tmpTable.BPay           = 0;
                            tmpTable.PayCurrent     = 0;
                            tmpTable.Pay            = custSettlement.SettleAmountCur;
                        }
                    }

                    tmpTable.GroupFinancName    = ICLGroupsFinancTable::find(qContractTable.GroupFinancId).GroupFinancName;
                    tmpTable.AccountNum         = qLedgerTrans.AccountNum;
                    tmpTable.AccountName        = CustTable::find(qLedgerTrans.AccountNum).NameAlias;
                    tmpTable.ContractNumberDate = qContractTable.contractNumberDate;
                    tmpTable.Voucher            = qLedgerTrans.Voucher;
                    tmpTable.AddQty             = 0;
                    tmpTable.AddAmount          = 0;
                    tmpTable.DebtBeginPeriod    = 0;
                    tmpTable.Advance            = (custTransSelect.TransDate > dateTo ? NoYes::Yes : NoYes::No);NoYes::No;
                    tmpTable.CurrentPayTotal    = tmpTable.BPayCurrent + tmpTable.PayCurrent;
                    tmpTable.PayPeriodTotal     = tmpTable.BPay + tmpTable.Pay + tmpTable.CurrentPayTotal;
                    tmpTable.CurrentDebt        = tmpTable.AddAmount + tmpTable.CurrentPayTotal;
                    tmpTable.EndDebt            = tmpTable.DebtBeginPeriod + tmpTable.AddAmount + tmpTable.PayPeriodTotal/*CurrentPayTotal*/;

                    tmpTable.insert();

                }
           }
        }
    }

P.S. Если работать будет быстрее, проверьте все ещё раз внимательно, может что упустил и подломал