CiviCRM Community Forums (archive)

I've been testing the API performance of CiviCRM.

The motivation
I'll need to perform big synchronization processes daily (including new contacts, updates and unsuscribes from other databases). I'll need to "move" some thousands of records daily (the database size is over two millions of records but the number of records with changes every day is, more or less, some thousands).

The results may be interesting for other people, so I want to share them with you.

The REST "experiment"
Throught Talend (http://www.talend.com/), I used the CiviCRM API to create new contacts in a fresh CiviCRM installation. Both Talend and CiviCRM were installed on the same machine (so I "attacked" the RESP using something like http://localhost/.../civicrm/extern/rest.php).

The process I executed was:

• I choosed a 2 million records text file as the source (its column length was about 300 characters),
• I only filled 6 fields (date added, first and last name, mobile and work phone),
• I only created contacts (didn't care about other related tables),
• the machine processor was: Intel(R) Core(TM)2 Quad CPU Q8400 @ 2.66GHz,
• the machine had 4Gb RAM and
• the machine was running a Ubuntu 12.04 OS.

The performance was very close to 2 records per second (I made the same "experiment" with SugarCRM, using Talend's SugarCRM plugin, and obtained -almost the same performance- something like 2.2 records per second).

The PHP "experiment"
After that, I tried a second option: writting PHP code to be executed in the same machine. The conditions were, more or less, the same conditions specified for the previous experiment (except I only filled email, first and last name). The code I wrote for the test, was:

    require_once "/var/www/drupal/sites/all/modules/civicrm/api/class.api.php";
    $api = new civicrm_api3 ( array ( "conf_path" => "/var/www/drupal/sites/default" ) );

    $sourceFile = "source.txt";
    $stopInRow = 500; /* 0 if you want to process the whole file */

    $startTime = time();

    $row = 1;
    if (($handle = fopen($sourceFile, "r")) !== FALSE) {
        while (($row <= $stopInRow || $stopInRow == 0) &&
            ($data = fgetcsv($handle, 1000, "\t")) !== FALSE) {

            if ($row == 1) {
                $header = $data;
            } else {
                $email = $data[ array_search ( "EMAIL", $header ) ];
                $first_name = $data[ array_search ( "FIRSTNAME", $header ) ];
                $last_name = $data[ array_search ( "LASTNAME", $header ) ];

                $api->Contact->Create ( array (
                    "contact_type"=>"Individual",
                    "last_name"=>$last_name,
                    "first_name"=>$first_name
                ) );
            }

            $row++;
        }

        fclose($handle);
    }

    $endTime = time();

    $timeLapse = $endTime - $startTime;
    echo $timeLapse . " seconds" . "\n";
    echo ($row - 1) . " records inserted" . "\n";
    echo abs ( ($row - 1) / $timeLapse) . " records / second" . "\n";

This time, I obtained a performance of 6 records per second. My hypothesis is this process is probably faster because it doesn't pass trought Apache / Drupal. Anyway, performance is still too slow for my purposes.

I tried to make the same experiment with SugarCRM but I didn't find a way to use its API skipping REST (and so, skipping Apache). Point for CiviCRM! The performance of the SugarCRM API, in this context, was still close to 2 records per second (as expected because, indeed, the experiment was almost the same experiment I made trought Talend but using only a different interface). (If anyone wants to have the code I used, just ask).

The risked solution
Both API performance results are not sufficient for my purposes, so I'm thinking about to do something risky and I want to have comments from you:

- What about to write some stored procedures to play the role of "SQL API"? Something as "CREATE STORED PROCEDURE spContactCreate...".

I understand that, If I write it, I'll have to "replicate" the API behaviour in SQL and review it for every change of version.

- If you think it's not a good idea, do you have any suggestion about what to do?

Thanks for reading!
Best!

Hi,

With the rest interface from node, I could get on my laptop (i7) around 1000 individual created per minute, including their organisation (200 created, 800 fetched and relationship created), email, phone and address.

 https://github.com/tttp/civi-charlatan

It's as parallel as the webserver can deal with it. I didn't test much of the error messages and you might get some contacts creation that failed because concurrency reasons.

Still, and order of magnitude faster than your case with talend over rest.

Could you try running civi-charlatan see if the diff is somewhere in the config or if it's just faster because more concurrent operations?

totten, xavier, thanks for your advices. I'll try to make a new "experiment", this time, parallelizing. I totally agree if I manage to reduce the time of execution to a couple of minutes, it'd be completely acceptable and I wont have no excuses to skip using the API (it has been the intention since the very first minute).

Thanks!
Keep you informed!