Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / Entity range performance
Finance forum
Suggested answer

Entity range performance

editSubscribe (1) ShareShare
ReportReport
Posted on by CU04051814-0 118
Hi,

In order to use computed column as a range to the entity. Then it's either i put the range on the data project OR i create a view with this computed column method, then add this view to the entity in order to be able to filter on that range.
My question is, is one of the approaches better than the other in terms of performance? I mean does it matter if i filter on the data project or in the entity itself?

Here's the range:
((Field1= Enum1::ValueX) || ((Field1= Enum1::ValueY) && (Field2= Enum2::ValueA)))
Categories:
Development / Customization / SDK
Integrations
  • Andre Arnaud de Calavon Profile Picture
    Andre Arnaud de Cal... 283,375 Super User on at
    Entity range performance
    Initially, I think the second option should be faster, like your expectation, but query plans sometimes can behave like a diva. 
    To monitor the performance, you can go to the execution history details. There is a start time and end time available. The difference is the duration time. 
    Ensure you test also with a larger data set.
  • CU04051814-0 Profile Picture
    CU04051814-0 118 on at
    Entity range performance
    Hi Andre,

    I will explain more:

    The first option:
    I create a single entity with TableLines inner joined with TableHeader.
    Then i create a computed column in the entity that satisfies this:
    ((Field1= Enum1::ValueX) || ((Field1= Enum1::ValueY) && (Field2= Enum2::ValueA)))
    Now i can't filter this computed column on the entity. So i create an export data project and I filter on this computed column there.

    Then 2nd option
    I create a view with TableLines inner joined with TableHeader.
    Then i create a computed column in the view that satisfies this:
    ((Field1= Enum1::ValueX) || ((Field1= Enum1::ValueY) && (Field2= Enum2::ValueA)))
    Now I create a single entity with this view.
    and Now i can filter on the computed column on the entity itself without the need to filter on the data project


    I know that the first option, will return everything, then in the data project the filter will happen
    in the 2nd option, the filter will be on the entity itself

    So does that affect performance if we filter on the entity directly or on the data project?
    And how can I test the performance for both options?
  • Suggested answer
    Kevin Xia Profile Picture
    Kevin Xia Microsoft Employee on at
    Entity range performance
    Hi,
    As Andre said, monitoring your system using performance tools will let you know more specifically which approach is better. Here are some tools and suggestions about performance monitoring, you can refer to them:
    Performance timer - Finance & Operations | Dynamics 365 | Microsoft Learn: This article provides an overview of the Performance timer, which is a tool that helps you to determine why your system's performance might be slow.
    Work with performance and monitoring tools in finance and operations apps - Training | Microsoft Learn: Learn how to use performance tools and Lifecycle Services environment monitoring tools to enhance your validation and testing experience in finance and operations apps.
    Best regards,
    Kevin
  • Suggested answer
    Andre Arnaud de Calavon Profile Picture
    Andre Arnaud de Cal... 283,375 Super User on at
    Entity range performance
    Hi,
     
    A range or a view with computed columns might have different outcomes for performance. You only provided the range, not the details of how you created the view. It all depends on how SQL will create the plan for each of the two options. I would suggest doing some performance tests on your environment to be sure which one will perform better.

Helpful resources

Quick Links

Take the Community feedback survey!

Answer this brief 15-question survey about your Community experience…

Demystifying Copilot: Service Edition with Sundar Raghavan

Sundar answers more questions about Copilot for Service...

Dynamics 365 Business Central vs Finance and SCM

Take a look at the key differences between Business Central and…

more Community News

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,375 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 223,308 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,140

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans