Speeding Up Auto-completes

With the advent of Google search auto-complete, it seems like auto-complete should be everywhere.

Any site that has masses of information that is exposed through various search widgets has considered auto-complete in various stages of product design and development. In light of this, Drupal has allowed for quick auto-completion inclusion for over 6 years. In Drupal 7 it looks a bit like this (search for full guides for complete walkthroughs).

This example is filling in city, state combos in a search field.

/** Implementation of hook_menu **/

function example_menu() {
  
  $items['city_autocomplete'] = array(
    'page callback' => 'autocomplete_city',
    'access arguments' => array('access content'),
  ); 
  return items;
}

function autocomplete_city($string){
  $string = array(':s' => $string . '%');
  $result = db_query('SELECT DISTINCT city, province FROM {location} WHERE city LIKE :s order by city limit 20', $string);

 
  $items = array();
  foreach ($result as $location) {
    $items[$location->city .', '.$location->province;] = $location->city .', '.$location->province;
  }
  print drupal_json_encode($items);
}

/** Some form alter, or form generation function **/
...

    $form['search_string']['#autocomplete_path'] =  'city_autocomplete';
    $form['search_string']['#attributes']['class'][] = 'form-autocomplete';
...

After this, the field now will issue auto-complete suggestions as the user types.

Not Fast Enough

BUT, our business people were down on the speed that the results came back. They wanted it to go faster. So I decided strip out as much of the Drupal bootstrap as possible, and then bypass completely the index.php loading of the normal Drupal framework.

The goal was to do this while still being able to use the #autocomplete_path form enhancement, no need reinvent everything. To do this, we actually have to create a new base script and point the #autocomplete_path paths to that script. It was easiest to do this by placing the scripts in the same root directory as the regular Drupal index.php script is found.

Let call it city_autocomplete.php. Below is the adjusted module code.

Note that the hook_menu item has the '.php' in it. It still works as that is a valid Drupal menu name.

/** Implementation of hook_menu **/

function example_menu() {
  
  $items['city_autocomplete.php'] = array(
    'page callback' => 'autocomplete_city',
    'access arguments' => array('access content'),
  ); 
  return items;
}

//Technically not needed anymore, but leaving in for posterity.
function autocomplete_city($string){
  $string = array(':s' => $string . '%');
  $result = db_query('SELECT DISTINCT city, province FROM {location} WHERE city LIKE :s order by city limit 20', $string);

 
  $items = array();
  foreach ($result as $location) {
    $items[$location->city .', '.$location->province;] = $location->city .', '.$location->province;
  }
  print drupal_json_encode($items);
}

/** Some form alter, or form generation function **/
...

    $form['search_string']['#autocomplete_path'] =  'city_autocomplete.php';
    $form['search_string']['#attributes']['class'][] = 'form-autocomplete'; 
...

Now, inside the new city_autocomplete.php file. Much of this is taken directly from the index.php file, but we restrict the drupal bootstrap to only the database, and we include one extra file, common.inc which has the drupal_json_encode() function.

<?php

  define('DRUPAL_ROOT', getcwd());
  
  require_once DRUPAL_ROOT . '/includes/bootstrap.inc';
  require_once DRUPAL_ROOT . '/includes/common.inc';
  drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE);
  
  // arg(1) is used to gather the data sent to the auto complete script
  $string = array(':s' => arg(1) . '%');
  
  // The rest is just the body of the autocomplete_city($string) function
  $result = db_query('SELECT DISTINCT city, state FROM {zipcodes} WHERE city LIKE :s order by city limit 20', $string);

 
  $items = array();
  foreach ($result as $location) {
    //$items[$location->city] = $location->city .', '.$location->province;
    $items[$location->city .', '.$location->state] = $location->city .', '.$location->state;
  }
  print drupal_json_encode($items);

Clear your cache to reload the menu items, and revisit whatever page had your auto-complete attached to it. That is all.

Benchmarking (all times from Firebug Console)

I wouldn't call this a strenuous set of benchmarking, but does give the ballpark opportunities for speed improvements.

Local system (vanilla MAMP setup, no code caching):

Regular Drupal auto complete: 250-350ms
Limited Bootstrap auto complete: 35-55ms

Dramatic improvement on a vanilla Apache setup, no code caching.

Remote Pantheon System (Pantheon DROPS, http://helpdesk.getpantheon.com/customer/portal/articles/361254):

Regular Drupal auto complete: 150-250ms
Limited Bootstrap auto complete: 80-150ms

First take-away, the regular auto-complete was faster under a remote Pantheon server, than my vanilla localhost. Obviously using a code cache is a great performance booster for the regular code execution.

Second take-away, this method does improve performance speeds by around 20-40%, even under great code conditions.

Bonus tip

You can decrease the delay time between the last key stroke and when the search starts. It defaults to 300ms (essentially doubling the times to display results). It is found in the /misc/autocomplete.js file

...

Drupal.ACDB = function (uri) {
  this.uri = uri;
  this.delay = 300;
  this.cache = {};
};
...

Change that delay to something less to make all auto-completes start faster. If you don't want to do a core hack, then you need to remake all the Drupal.ACDB JS found in the /misc/autocomplete.js in your own theme or module JS, and make the adjustment there.

That is really interesting. I

That is really interesting.

I noticed that you did not mention that code cache is only one of many
factors that would mean affect the Pantheon-vs-local comparison.
Network distance and hops is just one that comes to mind.
Nevertheless, any thoughts on why Pantheon was _slower_ using the
custom bootstrap than on your local machine?

This would make a great patch to Drupal core. And even a contrib
module. Perhaps there would be away to specify in hook_menu() how far
Drupal should bootstrap in order to invoke a menu callback. (A bit
like drush commands do). Although probably Drupal needs to bootstrap
too far in order just to do that.

You can probably modify the Drupal.ACDB delay with one line of
Javascript that executes after has been initialised.

Cheers,
Bevan/

Bevan, thanks for the

Bevan, thanks for the comment. To address why the Pantheon custom numbers were slower, I think you have it right, that it mostly is network distances/processing that is adding 30-40 ms, against the localhost setup.

The best comparison, would to add APC to my localhost and run the numbers again, but I don't have it in me to do that at the moment.

As for the Drupal.ACDB delay, yes, the correct solution is to add a line of JS to either a base theme JS file, or elsewhere to over-write the defaults in the /misc/autocomplete.js file. Which is exactly what we did since I wrote the posting.