Dynamics Community Forum Thread Details

ordering records in file

Hi,

I have a text file with the following fields: ItemNumber, ItemName, Price, ItemChangeNumber

Item1 Item1Name 5
Item2 Item2Name 25
Item1 Item1Name 5 Item2
Item1 Item1Name 25 Item2
Item3 Item3Name 7
Item4 Item4Name 17
Item5 Item5Name 10
Item4 Item4Name 20
Item5 Item5Name 10 Item4

Item6 Item6Name 8

Item7 Item7Name 7 Item6

what i do is that i loop into this file, and use subStr to detect the values
where i insert ItemNumber, ItemName and Price into a table if they don't exist, or if the item exists then i just update
also if ItemChangeNumberis filled in the file, then i insert it into another table (insert or update)

and there is more logic to insert/update to other tables

Now this file is very big and it's taking alot of time till the process finishes.

So I'm thinking to use multi threading to split the file and speed up the process. However, i need to pay attention to the following:
1. it needs to be ordered by item number, as you saw item1 was repeated in more than one line and the last price for it was 25, so i want to make sure that the latest price for it gets inserted/updated

2. I need to also make sure that ItemChangeNumber was there in the file thread as itemNumber. For example:
Item4 Item4Name 20
Item5 Item5Name 10 Item4
when i split the file, i need to make sure that ItemNumber: item4 and ItemChangeNumber: Item4 are in the same thread, or this will cause an error to occur that the item doesn't exist if this line was run first /Item5 Item5Name 10 Item4/ before /Item4 Item4Name 20/

3. As i described i insert/update into some tables, so i need to make sure no dead locks happed -- would that happen with multi threading?

So I think what i need to do is this:

1. Loop through the file and insert those records into a staging table/Tmp table (i don't think i can do the ordering directly right? i will to insert to another table)

2. Order the records in the tmp table by itemNumber and itemChangeNumber, but How i'm also going to make sure that ItemChangeNumber is in the same thread where it appeared as itemNumber first?

3. How to use multi threading?

4. How to avoid deadlocks?

5. How to decide how many records go in each thread?

So if i will split the file above, it should look like this with ordering and splitting (of course assume there is alot more than this)

First thread:
Item1 Item1Name 5
Item2 Item2Name 25
Item1 Item1Name 5 Item2
Item1 Item1Name 25 Item2
Second thread:
Item3 Item3Name 7
Item4 Item4Name 17
Item4 Item4Name 20
Item5 Item5Name 10
Item5 Item5Name 10 Item4

Third thread:

Item6 Item6Name 8

Item7 Item7Name 7 Item6

Hi,

You can refer to this official document: Optimize data migration for finance and operations apps - Finance & Operations | Dynamics 365 | Microsoft Learn. The information in this article represents a set of steps and actions that you can use to optimize the performance of data migration. To better use parallelism and multithreading, you can configure the maximum number of batch threads per instance of Application Object Server (AOS) by setting the Maximum batch threads field on the Server configuration page (System administration > Setup > Server configuration). Be careful about changing the value of this field. A value that is too high can have negative performance implications. Currently, the default value is 8. You can increase the value to 12 or 16. However, you shouldn't set the field to a value that is more than 16 unless you do significant performance testing.

Best regards,

Kevin

Quick Links

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