web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to Improve Perform...
Finance | Project Operations, Human Resources, ...
Suggested Answer

How to Improve Performance on X++ Select Class

(0) ShareShare
ReportReport
Posted on by tristansenk99 89

Hi

I have a run() method that populates a custom table which is used to populate a "cached" entity upon data management export. However, the class is running extremely slow (we have an immense amount of data), but I wanted to see if any of you guys have any ideas to improve performance of this method below. Any help is appreciated!

public void run()
{
	ProjectAccountingDistribution   projectAccountDistribution;
	ReasonTableRef                  reasonTableRef;
	VendTrans                       vendTrans;
	VendSettlement                  vendSettlement,vendSettlementAmount;
	ProjTable                       projTable;
	LedgerJournalTrans              ledgerJournalTrans;
	VendInvoiceInfoTable            vendInvoiceInfoTable;
	GeneralJournalEntry             generalJournalEntry;
	GeneralJournalAccountEntry      generalJournalAccountEntry;
	CH_VendorInvoiceEntityCache     vendorInvoiceEntityCache;
	VendInvoiceJour                 vendInvoiceJour;
	VendInvoiceTrans                vendInvoiceTrans;
	SourceDocumentLine              sourceDocumentLine;
	AccountingDistribution          accountingDistribution;
	PurchTable                      purchTable;
	PurchAgreementHeader            purchAgreementHeader;

	delete_from vendorInvoiceEntityCache;

	while select vendInvoiceJour
		outer join vendInvoiceTrans
			where vendInvoiceTrans.PurchID  == vendInvoiceJour.PurchId
				&& vendInvoiceTrans.InvoiceId  == vendInvoiceJour.InvoiceId
				&& vendInvoiceTrans.InvoiceDate  == vendInvoiceJour.InvoiceDate
				&& vendInvoiceTrans.numberSequenceGroup  == vendInvoiceJour.numberSequenceGroup
				&& vendInvoiceTrans.InternalInvoiceId  == vendInvoiceJour.InternalInvoiceId

	{
		vendorInvoiceEntityCache.PurchId                    = vendInvoiceJour.PurchId;
		vendorInvoiceEntityCache.InvoiceId                  = vendInvoiceJour.InvoiceId;
		vendorInvoiceEntityCache.InvoiceAccount             = vendInvoiceJour.InvoiceAccount;
		vendorInvoiceEntityCache.InvoiceDate                = vendInvoiceJour.InvoiceDate;
		vendorInvoiceEntityCache.InvoiceAmount              = vendInvoiceJour.InvoiceAmount;
		vendorInvoiceEntityCache.CurrencyCode               = vendInvoiceJour.CurrencyCode;
		vendorInvoiceEntityCache.DueDate                    = vendInvoiceJour.DueDate;
		vendorInvoiceEntityCache.LedgerVoucher              = vendInvoiceJour.LedgerVoucher;
		vendorInvoiceEntityCache.SumTax                     = vendInvoiceJour.SumTax;

		select firstonly purchTable
		where purchTable.PurchId == vendInvoiceJour.PurchId;

		vendorInvoiceEntityCache.PurchAgreementId = (select PurchNumberSequence from purchAgreementHeader where purchAgreementHeader.RecId == purchTable.MatchingAgreement).PurchNumberSequence;


		if(vendInvoiceJour.InvoiceAccount)
		{
			vendorInvoiceEntityCache.VendorName = VendTable::find(vendInvoiceJour.InvoiceAccount).name();
		}

		vendorInvoiceEntityCache.LineNum            = vendInvoiceTrans.LineNum;
		vendorInvoiceEntityCache.ItemId             = vendInvoiceTrans.ItemId;
		vendorInvoiceEntityCache.Qty                = vendInvoiceTrans.Qty;
		vendorInvoiceEntityCache.PurchPrice         = vendInvoiceTrans.PurchPrice;
		vendorInvoiceEntityCache.PurchUnit          = vendInvoiceTrans.PurchUnit;
		vendorInvoiceEntityCache.Name               = vendInvoiceTrans.Name;
		vendorInvoiceEntityCache.Tax1099Amount      = vendInvoiceTrans.Tax1099Amount;
		vendorInvoiceEntityCache.Tax1099State       = vendInvoiceTrans.Tax1099State;
		vendorInvoiceEntityCache.Tax1099StateAmount = vendInvoiceTrans.Tax1099StateAmount;
		
		vendorInvoiceEntityCache.ProcuremetnCategoryName = EcoResCategory::find(vendInvoiceTrans.ProcurementCategory).Name;

		reasonTableRef      = ReasonTableRef::find(vendInvoiceTrans.ReasonTableRef);

		vendorInvoiceEntityCache.ReasonCode     = reasonTableRef.Reason;
		vendorInvoiceEntityCache.ReasonComment  = reasonTableRef.ReasonComment;

		select firstonly sourceDocumentLine 
			where sourceDocumentLine.RecId == vendInvoiceTrans.SourceDocumentLine
			join accountingDistribution 
			where  accountingDistribution.SourceDocumentLine == sourceDocumentLine.RecId;

		vendorInvoiceEntityCache.Number =  accountingDistribution.Number;

		 if(accountingDistribution.LedgerDimension)
		{
			vendorInvoiceEntityCache.LedgerAccount = LedgerDimensionFacade::getDisplayValueForLedgerDimension(accountingDistribution.LedgerDimension);
		}
		else
		{
			select firstonly RecId,SubledgerVoucher from generalJournalEntry
			join GeneralJournalEntry,LedgerDimension,PostingType from generalJournalAccountEntry
				where generalJournalEntry.SubledgerVoucher == vendInvoiceJour.LedgerVoucher
				&& generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
			&& generalJournalAccountEntry.PostingType == LedgerPostingType::LedgerJournal;

			if(generalJournalEntry)
			{
				vendorInvoiceEntityCache.LedgerAccount = LedgerDimensionFacade::getDisplayValueForLedgerDimension(generalJournalAccountEntry.LedgerDimension);                 
			}
			
		}

		select firstonly vendInvoiceInfoTable
			where vendInvoiceInfoTable.Num == vendInvoiceJour.InvoiceId &&
				  vendInvoiceInfoTable.PurchId == vendInvoiceJour.PurchId &&
				  vendInvoiceInfoTable.ParmId == vendInvoiceJour.ParmId;

		select firstonly Voucher,AccountNum,TransDate,PaymReference,RecId,DataAreaId from vendTrans
		where   vendTrans.Voucher  == vendInvoiceJour.LedgerVoucher
			&&  vendTrans.AccountNum == vendInvoiceJour.InvoiceAccount
			&&  vendTrans.TransDate  == vendInvoiceJour.InvoiceDate;

		if(vendTrans)
		{
			vendorInvoiceEntityCache.PaymReference  =  vendTrans.PaymReference;
			vendorInvoiceEntityCache.PaymentVoucher = vendTrans.Voucher;

			select Txt from ledgerJournalTrans
			where ledgerJournalTrans.VendTransId == vendTrans.RecId;

			if(ledgerJournalTrans)
			{
				vendorInvoiceEntityCache.InvoiceDescription = ledgerJournalTrans.Txt;
				vendorInvoiceEntityCache.LineAmount         = vendInvoiceJour.InvoiceAmount;
			}
			else
			{
				vendorInvoiceEntityCache.InvoiceDescription = vendInvoiceInfoTable.Description;
				vendorInvoiceEntityCache.LineAmount         = vendInvoiceTrans.LineAmount;
			}
		}
		else
		{
			vendorInvoiceEntityCache.InvoiceDescription = vendInvoiceInfoTable.Description;
			vendorInvoiceEntityCache.LineAmount         = vendInvoiceTrans.LineAmount;
		
		} 

		select firstonly TransDate,TransCompany,TransRecId,AccountNum from vendSettlement
		where vendSettlement.TransCompany   ==  vendTrans.DataAreaId
			&& vendSettlement.TransRecId    == vendTrans.RecId
		&& vendSettlement.AccountNum    == vendTrans.AccountNum;

		if(vendSettlement)
		{
			vendorInvoiceEntityCache.PaymentDate    = vendSettlement.TransDate;
		}

		select firstonly vendSettlementAmount where vendSettlementAmount.OffsetTransVoucher == vendorInvoiceEntityCache.PaymentVoucher;
						   
		if(vendSettlementAmount)
		{
			vendorInvoiceEntityCache.SettleAmount = abs(vendSettlementAmount.SettleAmountCur);
		}
   
		select firstonly AccountingDistribution,TransactionCurrencyPrice,TransactionCurrencyAmount,ProjFundingSource from projectAccountDistribution where projectAccountDistribution.AccountingDistribution == accountingDistribution.RecId;

		if(projectAccountDistribution)
		{
			select ProjId,RecId from projTable where projTable.RecId == projectAccountDistribution.ProjTable;

			vendorInvoiceEntityCache.ProjectId      = projTable.ProjId;
			vendorInvoiceEntityCache.Category       = ProjCategory::findRecId(projectAccountDistribution.ProjCategory).CategoryId;
			vendorInvoiceEntityCache.FundingSouceId = ProjFundingSource::find(projectAccountDistribution.ProjFundingSource).FundingSourceId;
		}

		if(accountingDistribution.RecId)
		{
			vendorInvoiceEntityCache.TransactionCurrencyAmount = accountingDistribution.TransactionCurrencyAmount;
		}
		else
		{
			vendorInvoiceEntityCache.TransactionCurrencyAmount = vendInvoiceJour.InvoiceAmount;
		}
		vendorInvoiceEntityCache.insert();
	}
	
}

I have the same question (0)
  • Suggested answer
    GirishS Profile Picture
    GirishS 27,827 Moderator on at

    Hi tristansenk99,

    You can create a consolidated view which holds all the required tables and fields. So this will avoid the select statement you have added inside the while select.

    Also instead of using while select you can use query run to fetch data.

    Thanks,

    Girish S.

  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    It's not a trivial question.

    If I was you, I would challange the whole idea of throwing everything away and processing the "immense amount of data" again every time, even if little or nothing has changed. I don't know your particular business requirements, but I would be very surprised if no improvement could be done.

    Then you should try to limit the number of database requests. For example, you make select to PurchTable for every single VendInvoiceTrans, although you could simply add a join in the query. If you have a million lines, it means a million of extra queries. The actual number of database calls will be lower, because some values will come from a record cache, but it's still a very important topic.

    Note that PurchTable was just an example. There are other instances, such as with ReasonTableRef.

    You should utilize RecordInsertList instead of inserting records one by one.

    When you deal with the obvious things, it's time to measure what's actually taking the most time. If you find a query that behaves much worse than expected, consider checking its execution plan in SSMS.

  • Arunraj Rajasekar Profile Picture
    Arunraj Rajasekar 1,743 on at

    You can run a Trace to see the number of database calls

  • tristansenk99 Profile Picture
    tristansenk99 89 on at

    Hi Martin,

    Thanks for your response.

    Would you suggest putting the RecordInsertList.insertDataBase() outside of the while loop?

  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at

    Yes, of course. You'll call add() inside the loop and insertDatabase() at the end, i.e. outside the loop. Note that you can find more information about RecordInsertList on the internet, if you aren't familiar with it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 584 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 499 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 254 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans