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();
	}
	
}

  • Martin Dráb Profile Picture
    Martin Dráb 232,034 Most Valuable Professional on at
    RE: How to Improve Performance on X++ Select Class

    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.

  • tristansenk99 Profile Picture
    tristansenk99 89 on at
    RE: How to Improve Performance on X++ Select Class

    Hi Martin,

    Thanks for your response.

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

  • Arunraj Rajasekar Profile Picture
    Arunraj Rajasekar 1,731 on at
    RE: How to Improve Performance on X++ Select Class

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

  • Martin Dráb Profile Picture
    Martin Dráb 232,034 Most Valuable Professional on at
    RE: How to Improve Performance on X++ Select Class

    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.

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: How to Improve Performance on X++ Select Class

    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.

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,280 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,034 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans