Very bad performance with "Retrieve from server"?

Created on 18 June 2024, 10 days ago
Updated 22 June 2024, 6 days ago

Problem/Motivation

Hi, I'm trying search_api_autocomplete with a site with a decently big dataset - 30000+ nodes - and a search_api_db server.

I've tested on a local machine that "Display live results" suggester has good performence, about ~200 ms with display "only label".
On the other hand, "Retrieve from server" has a terrible performance, about 8/9 seconds to respond.

Looking the code, I would have expected the opposite results, as "Retrieve from the server" should do a direct sql query, while "Display live results load the Drupal entities via the API.

Is that a normal behaviour or maybe is something wrong on my setup?

šŸ› Bug report
Status

Active

Version

1.9

Component

General code

Created by

šŸ‡®šŸ‡¹Italy Giuseppe87

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

  • Issue created by @Giuseppe87
  • šŸ‡¦šŸ‡¹Austria drunken monkey Vienna, Austria

    Itā€™s definitely not normal, the SQL query/queries (there may be two if youā€™ve enabled both options under ā€œAutocomplete settingsā€ in the search server settings) seem(s) to be especially slow on your site. Since no-one else has complained yet (as far as I remember), this seems to be specific to your setup, though, so not sure how much I can help you with that. Try debugging first to see whether itā€™s really the SQL queries that are the problem (e.g., by activating your SQL serverā€™s slow query log) and, if so, debugging them to see how you might be able to improve their performance.

    If you are not a developer, there are even less options. If you have both of the mentioned options enabled, you could try whether using just one of them helps with the performance.

    However, it might also be that 30,000 nodes is just a bit much for this functionality in the DB backend. Normally, Iā€™d say this is well into the territory where using Solr makes more sense.

Production build 0.69.0 2024