Query with embedded parameters 10x faster than parameterized query

SQL++
1

I’m trying to figure out why on CB server 5.5.1 this parameterized query:

{                               
    "statement": "SELECT COUNT(type) AS count, type, MAX(content.updatedAt) AS lastUpdatedAt FROM master WHERE type IS NOT MISSING AND ANY c IN channels SATISFIES c IN $channels END GROUP BY type ORDER BY type",
    "$channels": ["denim_all_stores", "denim_location_161", "denim_tax_retail_location_group_33741"]
}

Takes 10x longer to execute than this query with the same parameters embedded:

{               
    "statement": "SELECT COUNT(type) AS count, type, MAX(content.updatedAt) AS lastUpdatedAt FROM master WHERE type IS NOT MISSING AND ANY c IN channels SATISFIES c IN ['denim_all_stores', 'denim_location_161', 'denim_tax_retail_location_group_33741'] END GROUP BY type ORDER BY type"
}

The index is:

CREATE INDEX `idx_document_metrics` ON `master`((all (`channels`)),`type`,(`content`.`updatedAt`))

I also tried creating a prepared statement and executing it, which had about the same performance as the parameterized query.

The queries return exactly the same results. I’ve attached the execution plan from EXPLAIN for both.

explain.zip (2.5 KB)

I’d appreciate any pointers on this. I need to be able to use parameterized queries, but the performance is inadequate.

1 Like
2

  1. Query parameters are not know at the time prepare time, right side of the IN clause parameter array. So prepare time must account all possible values. This result in extra work. More details can be found in https://blog.couchbase.com/in-list-handling-improvements-in-couchbase-server-6-5/

  2. Query using ARRAY index and part of the ANY clause using the parameters. In that situation it can’t use implicit covering index MB-33009

As you described embedded the parameters (In SDks form query string after you know parameters and execute it) and execute query better performance.

OR

Try the following query with adhoc=true same query parameters.

SELECT COUNT(DISTINCT META(m).id) AS count, m.type, MAX(m.content.updatedAt) AS lastUpdatedAt
FROM master AS m
UNNEST m.channels AS c
WHERE m.type IS NOT MISSING AND c IN $channels
GROUP BY m.type
ORDER BY m.type;
3

Hi @Vsr,

Even i am facing same issue when running parametrized query and embedded one.Embedded one is faster than Parametrized in query workbench.What is extra work done in parameterized query.I can see in plan access path is same.

Is there any option in Couchbase workbench to set adhoc= true.
Thanks
Ritu

4

adhoc=true is only for SDKs. Couchbase workbench should work.
After execution post the info from Plan Text Tab.

5

I am seeing difference in couchbase workbench itself embedded is performing better than parameterized.

embeeded is taking 8.8sec and parameterized is taking 18 sec. After comparing plan of both queries i found only difference in

Parameterized:
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 50000,
“#itemsOut”: 50000,
“#phaseSwitches”: 200003,
“execTime”: “18.090735803s”,
“kernTime”: "400.524499ms

Embedded parameters
“#operator”: “Filter”,
“#stats”: {
“#itemsIn”: 50000,
“#itemsOut”: 50000,
“#phaseSwitches”: 200003,
“execTime”: “791.968282ms”,
“kernTime”: “8.072226092s”

query is like this:
SELECT COUNT(*) FROM buket bc
WHERE column1 = $1 AND
type = “xyz”
AND column2 in $2 AND (ANY v IN bc.column3 SATISFIES v.status_code LIKE $3 END)
Any suggestion will be aprreciated

6

Unless you post whole thing it is difficult to tell. OR post both EXPLAINs full with index definition