Search code examples
atk4

Form autocomplete field - slow response

I have a form that has an autocomplete field like this

$f = $this->add('Form');
$f->addField('autocomplete','item')->setValueList($this->api->db->dsql()->table('item')->field('nroitem,concat(nroitem,\'-\',detalle)')->do_getAssoc());

When I test the form, the response of the autocomplete field is very slow ( 3-4 seconds to bring the information of the table, and the screen gets freezed.), the same when I delete the first field to do a search.

The concat is to allow to search by the item number or the item name.

Is there any limit with this kind of fields in size of data that it takes from the table?

I have tried making some index but with no luck. I have tried passing the array via session variable (setValueList($array) that I get with a previous query to the database).

Thanks.

Solution

    1. Try to use latest ATK version (at least from 4.2.x branch).

    2. Don't use setValueList for big arrays! Define Model and use $field->setModel($model) instead.

    3. It is slow in PHP basically because you parse all records in array and then set this array as data source of autocomplete field. If there are many records, that will consume big amount of processing time and RAM.

    4. It is slow in SQL because you search not in unmodified table fields, but in concatenation. That means, MySQL can't use any optimization and it should concatenate theses values for all records of your table and only then start to evaluate WHERE. That said WHERE nroitem LIKE '%abc%' OR detalle LIKE '%abc%' should work much faster than WHERE concat(nroitem,'-',detalle) LIKE '%abc%'

    5. In theory there's no limit of records for autocomplete field. All depends on your database structure, server performance, indexing and mostly on your own code :) I would say 100`000 records should still work fine.