Forum Discussion

Meismart's avatar
Meismart
Creator III
9 years ago

Best way to add 30,000 Records

Please can you help with a clever way to add 30,000 related items to an item. (AML Part Structure) <Item type="MasterList"> <Relationships> <Item type="MasterList_Part"><properties...></Item> </Relationships> </Item>   I have Millions of lines of application which I am resolving into tens of thousands of parts. Currently I have logic that matches the application to the parts and this gives me my gap/changes which I can manage - up to 10,000 records can change during normal operation. Unfortunately under certain circumstances, I need to add anywhere up to 200,000 related Items. Which I can split it down to chunks of approximately 30,000 related items. I am using: (C#) for (I=0; I<SQLResult.getItemCount(); I++) { Innovator inn = this.getInnovator(); Item relpart = inn.newItem("MasterList_Part", "add"); relpart.setProperties("...","..."); relpart = relpart.apply(); }   This approach works, however it is taking 20 minutes to add 30,000 records, as it makes a separate call for each iteration of the loop. Is there a recommended way to add multiple child parts to an existing parent Item. eg. setRelatedItem, appendItem, etc.

1 Reply

  • Zahar_Chernov's avatar
    Zahar_Chernov
    Creator II
    9 years ago
    As I'm not sure if all 30K rows are from the same "source_id" I'll assume they are and I'll assume that you know the ID of the source_id You don't need to send relationship one-by-one, you can write something like this: 
    string AML = "" +
             "<Item type='MasterList' action='merge' id='{SOURCE_ID}'>" +
             " <Relationships>";
for (I=0; I<SQLResult.getItemCount(); I++) {
        AML += " <Item type='MasterList_Part' action='add'><related_id>";

//If you know the ID of the "related_id" (child) item use following row:
	AML += "{ID or the child item}";
//    - OR -
//If you know the KEYED_NAME of the "related_id" (child) item use following row:
	AML += "<Item action='get'><keyed_name>{KEYED_NAME OF THE CHILD ITEM}</keyed_name></Item>";

	AML += "</related_id></Item>";
}

if (!string.IsNullOrEmpty(AML)) 
	Item qryResult = this.getInnovator().applyAML("<AML>" + AML + "</AML>");
    To optimize even more:
    • If your rows "source_id" is different you can select all source_id (distinct) from your SQLResult (LINQ can be good idea) and update my  example code so it will run based on source_id.
    • You can use .NET async to split data loading to couple threads to speed up your code.
    With that amount of data I would consider using Aras data loader (if you are subscriber) or other Aras partners data loaders (if you are freeloader) - for example check our company solution