Slow API Calls when adding relationship based on database size

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'] != '')
	if($CARDTRANJSON['Card_ID'] != null && $CARDTRANJSON['Card_ID'] != '')
	if($CARDTRANJSON['Branch_ID'] != null && $CARDTRANJSON['Branch_ID'] != '')
} else {
	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.


  • Hello  

    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


    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!


    Enrico Simonetti

    Sugar veteran (from 2007)

    Feel free to reach out for consulting regarding:

    • API Integration and Automation Services
    • Sugar Architecture
    • Sugar Performance Optimisation
    • Sugar Consulting, Best Practices and Technical Training
    • AWS and Sugar Technical Help
    • CTO-as-a-service
    • Solutions-as-a-service
    • and more!

    All active SugarCRM certifications

    Actively working remotely with customers based in APAC and in the United States