Если SQL 2000, то тут с index hint конечно очень аккуратно нужно быть. Если юзается индекс, то порядок полей в условии нужно подгонять под порядок полей в индексе, по возможности. Если не получается то index вообще лучше убрать.
Не ломая кверю GroupFinancLedgerJournalTrans, попробуйте ещё такой вариант, по-идее будет быстрее
(убрал некрасивый цикл, заменил ledgerTrans на qLedgerTrans везде где используется)
X++:
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. Если работать будет быстрее, проверьте все ещё раз внимательно, может что упустил и подломал
