Reduce the number of fields in your table design as low as possible: boost your performance using UnitOfWork framework

 



As a best practice issue, you might have seen this BP deviation warning: The number of fields in <SO_&_So_Table> is more than 18. Consider breaking down the fields into smaller number of fields. This is a standard BP check, that emphasizes to keep the number of fields in a table as low as possible. Factually, keeping the number of fields more than the breakeven of 18 is just not a BP issue, but could lead to various CRUD operations as well as a lot of unforeseen performance impacts (although in standard, you could see a lot many OOB tables that have violated this fact and have over 30+ fields. Standard has safely marked these tables with Cache lookup as: NOTINTTS -- although that might not be a good solution, always).

I prefer the following arrangement:


The parent table could be kept with a bare minimum number of fields' set, while all the necessary children tables could be mined with the differential number of fields. And make the children-to-parent table relation by using a RecId-RefRecID cardinality. 
Depending on the situation maybe, we can think of splitting up the fields into a base table and keep on stacking up children tables as inherited tables. 

Unit Of Work Framework:

A robust and very effective way to mass insert a number of tables (parent and children), could be to make use of Unit of work Framework. A Unit of framework allows you to just let the system understand that this table is the father table and these are its children -- and Tada! Done...The rest of the insertion could all be done internally.

Eg: a typical example is when you have SalesParmUpdate (father) and then SalesParmTable, SalesParmLine, SalesParmSubline as its children. When you have a huge record list to be inserted, from a bulky staging source, you can bulk insert the entire recordset at one go, at one end.

Set the 'Create navigation' method:


In the relation property of each of the child table, set 'CreateNavigationMethod'  as True. Optionally, you can set the Navigation property method. It's a cool feature of x++ that allows you to directly get the parent table buffer from the child table, without actually needing to query it:

salesTable = salesLine.salesTable();

Declaring the variables:

Coming back to the code, just at the start of the loop, you declare the UnitOfWork class variable as:

UnitOfWork uow = new UnitOfWork();


And then you can keep adding to your children table as follows:

while (sortingListCustomerContentEnumeator.MoveNext())

{    

    stagingTable = sortingListCustomerContentEnumeator.getCurrent();

    //first assign all the fields that belong to Parent table here (not outside, but inside the loop):

    parentTable.ContentNum = stagingTable.ContentNum;

    parentTable.LabelName  = stagingTable.ContantLabelName;

    

    //Adding Child1 fields

    child1.LicensePlate = stagingTable .licensePlate;

    child1.custAccount = stagingTable.AccountNum;

    child1.ValidFrom   = stagingTable.ValidFrom;

    child1.ValidTo        = stagingTable.ValidTo;

    //Adding child2 fields

    child2.FromCountry = stagingTable.FromCountry;

    child2.TOCountry    = stagingTable.DestCountry;

    child2.OfficePhone  = stagingTable.OfficePhone;

 

    child1.ParenTable(parentTable);

    child2.ParentTable(ParentTable);

 

    uow.insertOnSaveChanges(parentTable);

  uow.insertOnSaveChanges(child1);

  uow.insertOnSaveChanges(child2);

}

uow.saveChanges();


As a result, you just don't need to 

a. Specify which is what: who is the parent and which are the children tables.

b. Don't need to worry about rolling back the entire transaction: envelope the entire loop in a try-catch, so that when on one record it breaks, you can write your own-catch logic to handle the exception.

c. The entire commit is happening at one shot, at the end of loop, consequently, improvising the performance, many folds.

d. Also the minimizing the number of fields on a table could help you maintain the load too. 

  

Comments

Post a Comment

Popular posts from this blog

X++ : mistakes which developers commit the most

Image
   The most important thing is to remember where and when to write the code. a. Developers have an inherent habit of writing erratic select statements: select * from custTable where custTable.AccountNum == <SomeValue> Where in essence we need to make use of optimized choice of fields in the select statement. b. Making use of 'continue' keywords: While select custTrans where <someConditions> { if (cusTTrans.CustGroup != mappingTable.CustGroup) { continue; } } 'Continue' statements are precisely a very unhygienic way of designing your code. The above statement could have been easily ornamented by using exists/not exists joins. Using Continue statements would eventually result in a high performance impact. Also select statements should be made punctuated with a 'firstonly' clause -- just not from best practise standpoint, but also from performance considerations. c. Writing nested loops: This is a common mistake that perha
Read more

Make your menu items visible on main menu, conditionally,, using this cool feature of D365FO

Image
  Wassup guys? I am sure you must have encountered this situation where you needed to display your menuItems, on main menu, based on some condition, haven't you? This was only possible by using config keys and flights. Things get a bit tricky,  when suppose you have a requirement like this: you need to make it dynamic, by introducing a toggle switch on a field on a paremeter form.  Suppose on Accounts Receivables Parameters >> You have a field called ' Show US customer '. You have a requirement that says when you turn on this toggle, then only the menuItem ' Input SSN details ', under AR module. This is now highly acheivable by leveraging subscription to 'SysMenuNavigationObjectFactory' class, simply by saying a. Create a new class USMenuItemsVisibilityManager b. Copy the following method, with proper documentation (don't change signature/anything else) [SubscribesTo(classstr(SysMenuNavigationObjectFactory), staticdelegatestr(SysMenuNavigationObjec
Read more

Speed up your execution performance by using SysGlobalCaches

Image
  Ever come across a situation where you needed to  a. Using a multi-threaded batch to import a journal header and lines, from a staging table. The table already contains the Journal Ids which you need to assign to the header and allocate the lines under that header. b. You need to continuously check after each line being imported, if the journal with the Id has been created. If not, it will create the header and create the lines under it;  If yes, it will directly create the lines for the header (Find or create). c. As you could rightly guess, this architecture has an inherent sluggishness associated with it -- each time you keep on checking if the header has been created or not, it does mean a lot of performance effects, as a consequence. Also as you can understand it's a multithreaded code, which means each thread has no direct relation with the other -- which does mean there is no way I can talk between the threads/pass on any information. SysGlobalCaches to the rescue: I perso
Read more