Couchbase 6.0 community edition taking too much time for N1QL

SQL++
,
1

My Query

WHEN I RUN THIS QUERY ON MY DOC WHEN CONTAINS ARRAY OF PRICELIST LENGTH 255 IT TAKES 400ms to fetch but i need quicker. Thanks

SELECT schedule_pricelist.id,schedule_pricelist.price,schedule_pricelist.status,items.item_name as pricelist_item_name,service.name as service_name,department.sector as department_name FROM stitchit_initialization_hq schedule USE KEYS ‘schedule_3221190011’ UNNEST schedule.schedule_pricelist LEFT JOIN stitchit_initialization_hq items ON items.id = schedule_pricelist.item_id AND items.type = ‘items’ LEFT JOIN stitchit_initialization_hq service ON service.id = schedule_pricelist.service_id AND service.type = ‘service’ LEFT JOIN stitchit_initialization_hq department ON department.id = schedule_pricelist.department_id AND department.type = ‘department’ WHERE schedule.id = ‘3221190011’ AND schedule.type = ‘schedule’ AND service.id = ‘3221190019’ AND department.id = ‘3221190019’

MY document sample
{
“id”: “3221190011”,
“schedule_name”: “Alteration Price”,
“status”: “Active”,
“created_date”: “2019-11-15”,
“created_branch_id”: “3221”,
“updated_date”: “2019-11-16 02:33:01”,
“schedule_pricelist”: [
{
“id”: “322119000000”,
“schedule_id”: “3221190011”,
“service_id”: “3221190019”,
“department_id”: “3221190019”,
“item_id”: “3221190599”,
“price”: 11,
“branch_id”: “3221”,
“status”: “Active”,
“created_date”: “2019-10-09”,
“updated_date”: “2019-10-09 01:35:09”
},
],
“type”: “schedule”
}

MY INDEXS

CREATE INDEX isched ON stitchit_initialization_hq((distinct (array {“department_id”: (schedule_pricelist.department_id), “item_id”: (schedule_pricelist.item_id), “service_id”: (schedule_pricelist.service_id)} for schedule_pricelist in schedule_pricelist end))) WHERE (type = “schedule”)

CREATE INDEX def_service_details ON stitchit_initialization_hq(id,name) WHERE (type = “service”)

CREATE INDEX def_department_details ON stitchit_initialization_hq(id,sector) WHERE (type = “department”)

CREATE INDEX def_item ON stitchit_initialization_hq(id,service_id,department_id) WHERE (type = “items”)

2

I am not sure anything can be improved. Also CE version of query services uses limited (4) cores

SELECT sp.id,
       sp.price,
       sp.status,
       items.item_name AS pricelist_item_name,
       service.name AS service_name,
       department.sector AS department_name
FROM ( SELECT sp.id, sp.price, sp.status, sp.item_id, sp.service_id, sp.department_id
       FROM stitchit_initialization_hq AS s USE KEYS "schedule_3221190011"
       UNNEST s.schedule_pricelist AS sp
       WHERE s.id = "3221190011" AND s.type = "schedule"
     ) AS sp
LEFT JOIN stitchit_initialization_hq AS items ON items.id = sp.item_id AND items.type = "items"
LEFT JOIN stitchit_initialization_hq AS service ON service.id = sp.service_id AND service.type = "service"
LEFT JOIN stitchit_initialization_hq AS department ON department.id = sp.department_id AND department.type = "department"
WHERE service.id = "3221190019" AND department.id = "3221190019";


CREATE INDEX def_service_details ON stitchit_initialization_hq(id, name) WHERE (type = "service");
CREATE INDEX def_department_details ON stitchit_initialization_hq(id, sector) WHERE (type = "department");
CREATE INDEX def_item ON stitchit_initialization_hq(id, item_name) WHERE (type = "items");
3

Hi Thanks but its returning empty array . i created all the indexes you mentioned the query executes fast but returns empty array

4

try now. there is typo it needs UNNEST s.schedule_pricelist AS sp

5

Thanks it works previously it was giving result at around 350ms but now its gives me around in 250ms. Can we improve it little more like in 100ms??

6

You have WHERE service.id = “3221190019” AND department.id = “3221190019”; do really need LEFT JOIN.

7

yes i need because i stored service name and department name of item there

8

Please see if this gives expected results and better SLA.

SELECT sp.*, items.item_name AS pricelist_item_name
FROM (SELECT ARRAY {sp.id, sp.price, sp.status, sp.item_id, department_name, service_name}
                    FOR sp IN s.schedule_pricelist
                    WHEN sp.department_id = "3221190019" AND sp.service_id = "3221190019" END AS sps
        FROM stitchit_initialization_hq AS s USE KEYS "schedule_3221190011"
        LET department_name = (SELECT RAW d.sector
                               FROM stitchit_initialization_hq AS d WHERE d.id = "3221190019" AND d.type = "department")[0],
                  service_name = (SELECT RAW sn.name
                            FROM stitchit_initialization_hq AS sn WHERE sn.id = "3221190019" AND sn.type = "service")[0]
        WHERE s.id = "3221190011" AND s.type = "schedule") AS s
UNNEST s.sps AS sp
LEFT JOIN stitchit_initialization_hq AS items ON items.id = sp.item_id AND items.type = "items";
1 Like
9

Hi Thanks but its says error at LET

“code”: 3000,
“msg”: “syntax error - at LET”,

10

fixed the syntax error try again

11

My God awesome from 365 ms to 30ms thankyou so much coucl you pls explain me the query thanks