Data Entity export slow

(0) Share

Report

Posted on by tyhj

625

I'm creating a new data entity from standard PurchPurchaseOrderLineV2Entity, with 10+ virtual fields that are assigned values in postLoad() method.

The new method added has some select / while select query written to select the column lists required, and index hint is used as well.

I have around 400K records, export the standard (PurchPurchaseOrderLineV2Entity) data entity quite slow but still can complete, but the new data entity is not. There is no error, it is always in executing status. Count in SQL, staging is inserting records, about 100K after 4 hours.

My question is, what could be wrong to the new data entity ? Is virtual field a problem to the long running process ?

I have the same question (0)

All responses (11)

Answers (3)

Verified answer

Mea_ 60,284 on at

Like (2)

Report

It's definitely caused by new virtual fields because new selects are executed for each row. To improve performance you can try to use computed columns https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/data-entity-computed-columns-virtual-fields#example-create-a-computed-field also you can try incremental export instead of exporting 400k records all the time.

Was this reply helpful? Yes No
nmaenpaa 101,162 Moderator on at

Like (0)

Report

You can easily see if your code makes it slow, by temporarily commenting out the code and running the export again.

Virtual field itself is not a problem but if you run some SQL query 400 000 times it might take time.

Could you also consider sharing the code with us?

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi

Exporting this amount of data using staging tables is always slow. If there is a real need to export all data at once every time I would suggest using the "Skip staging" option, but in this case "computed columns" is only the option as PostLoad method doesn't work with "Skip staging" parameter.

BR, Sergei

Was this reply helpful? Yes No
Suggested answer

Rahul Mohta 21,032 on at

Like (0)

Report

enable change tracking and schedule export for delta records that way this would not be huge

other way would be seperate the computed column to a new entity and have that schedule seperately than the original one

Was this reply helpful? Yes No
tyhj 625 on at

Like (0)

Report

When comparing standard data entity it take about half an hour to run 400k records. I have tried comment out the code in postLoad, but the export is also running slow. So I suspect it is the unmapped field that is added to the entity object that cause the slowness.

In postLoad(), there are virtual fields to select from other tables

VendInvoiceInfoTable vendInvoiceInfoTable = this.vendInvoiceInfoTable(this.PurchaseOrderNumber);

this.InvoiceNumber = vendInvoiceInfoTable.Num;

private VendInvoiceInfoTable vendInvoiceInfoTable(PurchId _purchId)

{

VendInvoiceInfoTable invoiceInfoTable;

select firstonly Num, DocumentDate from invoiceInfoTable

index hint PurchIdx

order by CreatedDateTime desc

where invoiceInfoTable.PurchId == _purchId;

return invoiceInfoTable;

}

Is computed field better to use in this scenario ?

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

How about adding VendInvoiceInfoTable as a data source in your entity?

Was this reply helpful? Yes No
tyhj 625 on at

Like (0)

Report

I did try to add PurchTable at first, it is running slow also. I can give it a try but I have quite some tables to add.

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

Also, as suggested above, you could try to use change tracking (or other filtering) and avoid exporting all purchase orders every time. After all, once the PO is invoiced it will never change anymore and should not be needed to be exported again and again.

Was this reply helpful? Yes No
tyhj 625 on at

Like (0)

Report

Yes, I'm looking how to enable change tracking. Hopefully this can help. I think export should happen at least once a month.

After about 8 hours I'm still waiting for the process to complete but anyhow the slowness may not be tolerable to the user. Worst case I'll need to change to computed field.

Was this reply helpful? Yes No
tyhj 625 on at

Like (0)

Report

I have changed my columns to computed fields, trying the export with "Skip staging" turn on, but still there is staging data exported. Tried refresh entity, restart everything but still the same. So I had no choice but to use the filter.

Was this reply helpful? Yes No