Slow API Calls when adding relationship based on database size

Question

Hi everyone. 
 I am currently trying to upload 500,000 records into CRM, using a custom module with 3 relationships. 
 Card Transactions which then links to Card, Sale Location, Contact. These are all M:1 relationship. I have a custom API endpoint to allow us to bulk upload into Sugar (As standard API is very slow already). 
 The upload is extremely fast to start, but as more Card Transactions are added the slower and slower the API calls are completed. 
 A snippet of the code is: 
 $cardTran = BeanFactory::newBean('cd_CardTransactions');
$cardTran->card_no = $CARDTRANJSON['Card_Number'];
$cardTran->tran_date = isset($CARDTRANJSON['Transaction_Date']) ? date("Y-m-d", strtotime($CARDTRANJSON['Transaction_Date'])) : null;
$cardTran->sequence_no = $CARDTRANJSON['Sequence_Number'];
$cardTran->till_operator_no = $CARDTRANJSON['Till_Operator'];
$cardTran->spend_value = $CARDTRANJSON['Total_Spent_Value'];
$cardTran->points = $CARDTRANJSON['Points'];
$cardTran->till_no = $CARDTRANJSON['Till_Number'];
$cardTran->qualify_spend_value = $CARDTRANJSON['Qualifying_Retail_Value'];
$cardTran->transaction_type = $CARDTRANJSON['Transaction_Type']; 

if ($cardTran->save()) {
	if($CARDTRANJSON['Contact_ID'] != null && $CARDTRANJSON['Contact_ID'] != '')
	{
 $cardTran->load_relationship('cd_cardtransactions_contacts');
 $cardTran->cd_cardtransactions_contacts->add($CARDTRANJSON['Contact_ID']);
	}
	if($CARDTRANJSON['Card_ID'] != null && $CARDTRANJSON['Card_ID'] != '')
	{
 $cardTran->load_relationship('cd_cards_cd_cardtransactions');
 $cardTran->cd_cards_cd_cardtransactions->add($CARDTRANJSON['Card_ID']);
	}
	if($CARDTRANJSON['Branch_ID'] != null && $CARDTRANJSON['Branch_ID'] != '')
	{
 $cardTran->load_relationship('cd_cardtransactions_bd_branches');
 $cardTran->cd_cardtransactions_bd_branches->add($CARDTRANJSON['Branch_ID']);
	} 
	$response['created']++;
} else {
	$response['failed']++;
	array_push($response['failed_lines'], $cardTran['sequence_no']);
} 
 Which essentially creates the Transaction record, then links once the save is completed. Am I missing something here? This site has 0 logic hooks or schedulers in the site, it's completely fresh. 
 Thanks, Daniel

Daniel Hill · Answer

Hello Daniel Hill 
 First Question 
 Have you tried the Sugar upsert API combined with bulk? It has a number of "tricks" that I included on its code when I created it, that disables some things that can speed up a little the system. Bulk sequentially executes multiple operations, upsert makes them atomic so that with external keys you know how to refer to a record before it is created. 
 Second Question 
 Do you really need that much data in the CRM? Or is another internal system/data placeholder best suitable for it, with a dashlet/integration in Sugar to look that info up from the external system? Especially in Sugar Cloud: data is money. And the data goes in backups, elasticsearch etc. 
 Third Question and some suggestions 
 In regards to your specific problem, is this system hosted on site or on Sugar Cloud? If it is hosted on site could you verify that the database is tuned for the size you are trying to achieve and not for the starting point? If it is it hosted on site is the database disk an SSD? SSD especially for a database server, will have a big impact as fast read and writes are a must. If you are on-site, are you perhaps currently chocking the hard drive with IOs, after a certain number of inserts? Is the db and elasticsearch on the same server? Some monitoring would help you there. I have many questions here if you are on-site, to improve the situation... 
 Expected max performance 
 Rule of thumb, on Sugar Cloud you could achieve speed of 25,000 records per hour per thread for modules that have a number of relationships and up to 40,000 records per hour per thread for super simple modules with no relationships. Perhaps use this as an expected benchmark 
 Tips 
 Especially when doing initial data loads you want to "throw away" as much system stuff as possible. 
 To mention a few: 
 
 no BPM 
 no hooks 
 no related formulas 
 no activity stream 
 no changelog if you can 
 no email notifications 
 no elasticsearch indexing real time 
 etc etc etc 
 
 If you need the migration to go faster and don't need any of the Sugar functionality provided by beans save, audit log etc, then you could dump the data in the respective tables which is way faster. If hosted on Sugar Cloud you could then ask Sugar support to load the 4-5 tables for you, especially if they are fresh, providing the targeted SQL dump. 
 Another thing you can attempt is to profile the system WHILE you try to load data and see where most of the time is spent on the code. Have a look at the URL provided, for some hints on what I am talking about. 
 I'm giving you the holy grail of Sugar data loading here, after many years of working to optimise its process and many data loads and integrations for Sugar's biggest customers. 
 All the best!