SQL Vs. Query DSL

[avnere (Avner)]

Hi,

I see huge difference in the performance when running the same query in the different approach.

SQL:

GET _sql?format=json
{
  "query": """
  SELECT * FROM "baseelements" 
  WHERE "sessionId" = '621786a4186dc944ccb0729b' AND "streamName" = 'rwr' AND  "source" = 'FW'
  ORDER BY "_instance"
  """
}

performed in 4560 ms.

DSL:

GET baseelements/_search
{
  "query": {
    "bool": {
        "must": [
            { "match": { "sessionId" :  "621786a4186dc944ccb0729b" }},
            {"match": { "streamName.keyword": "rwr"}},
            {"match": { "source": "FW"}
    }
        ]
    }
  }
, "sort": [
    {
      "_instance": {
        "order": "asc"
      }
    }
  ]
}

Performed in 250 ms.

Why do I see this huge difference?

Thanks,
Avner

[casterQ (caster)]

1. you can use SQL Translate API to translate your SQL to DSL and find their difference。
2. And you can use Profile API to get detailed timing information about the execution of individual components in a search request。
3. And when doing the comparison test，make sure that the second query request does not use the first query cache，you can change query criteria or clear the cache。