2

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.

4

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

  1. 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)

  2. 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.

| improve this answer | |
  • Hi Xavier, thanks for the detailed response. I think I'll have to dig down with a profiler, as you say. When I was testing the performance of the raw query it was on a complete separate page load to when testing the API - I wanted to compare them separately. I was under the impression that the REST api uses the same functions as the PHP api, so whilst REST will always be slower (as you say) the API will be going through the same functions (after going through authentication etc.). Thanks for the useful advice. We also use 'limit' where appropriate to try and speed things up. – John Jul 29 '15 at 13:22
  • We have quite a large contacts table - so perhaps the performance boost after the first query is due to caching as you said. Although if that were the case I would expect the same thing to be true for the standard SQL queries. – John Jul 29 '15 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.