First CiviCRM API call is slower, are unnecessary resources loaded?

Question

We're looking at importing a lot of data via the API call. But we've noticed that on a page load the first API call is significantly slower than following ones. Eg:

$result = civicrm_api3('Contact', 'get', array()); // Slow
$result = civicrm_api3('Contact', 'get', array()); // 10x faster
$result = civicrm_api3('Contact', 'get', array()); // 10x faster

I thought this might be because CiviCRM has to autoload a number of classes the first time it is called. However, the same classes (inspected using get_declared_classes()) seem to be called when using lower-level and faster functions, which don't have a slow first query like:

$dao = CRM_Core_DAO::executeQuery($query); // Fast
$dao = CRM_Core_DAO::executeQuery($query); // Fast

Obviously the raw SQL will always be faster, but does anyone know what could be causing this initial slowness on first API calls, or have any tips on how to debug it?

It would be great if we could optimise this since REST calls are all single page requests it can be quite slow.

Answer 1

They are several distinct issues:

1. Calling civicrm_api3, this isn't REST, and (unless requested) the code runs as admin, so probably not linked with authentication

Here, a common issue is the database access, the first time you access the underlying tables (civicrm_contact and co), mysql might not have kept them in the cache, but the second time they are and it will be several order of magnitude faster.

My guess would be that most of the difference in time is due to the database layer (that might has well be kept some cached in the DAO layer) but mostly the difference between an empty mysql cache.

When you compare with running directly the executeQuery, which one do you run? They are likely quite a few sql queries inside the api call. Do you run them after having called the api?

if you want to dig into the detail of where the time is spent, I'd recommend you to use a profiler, because it's hard enough th it, and black magic and speculation without

2. The REST interface. It's always going to be slower, both because of the overhead of the network and the extra authentication step (based on the keys you provide)

3. It will be faster if you provide a return param to the api call, to specify which fields you want. by default, it will returns tons of fields that you probably won't use, some that are from related joined tables (eg. email, phone, address, name for options or pseudo constants...).

adding a return="first_name,last_name,email" would avoid fetching stuff you don't need.