Query is slow for 135k documents, how to speed up

SQL++
1

I am looking into getting the query below perform better.

UPDATE metadata AS d 
SET d.parentDocumentId = (SELECT id FROM metadata USE KEYS meta(d).id WHERE meta().id like 'documentId_%' AND hasGeneratedChildren = true AND d.parentId = parentId AND d.locale = locale AND d.language = language)
 WHERE type = "testMetadataType" AND format IS NOT MISSING AND meta().id like 'documentId_%' AND parentDocumentId IS MISSING and hasGeneratedChildren IS MISSING AND language = "en" AND locale = "US";

This is the index that I use for the query

CREATE INDEX query_index ON `metadata`(`type`,`locale`,`language`,`parentDocumentId`,`hasGeneratedChildren`,`format`) 
WHERE type = "testMetadataType" AND format IS NOT MISSING AND meta().id like 'documentId_%' AND parentDocumentId IS MISSING and hasGeneratedChildren IS MISSING AND language = "en" AND locale = "US";

With about 135k documents, the query above took 2 minutes to finish. When I looked at the request profile information, I noticed that the SET phase has execTime - 1m59.269882889s. This looks like a long time. Is there something wrong in the query or index that I should fix? Or it is just because of the Couchbase Server itself?

This is a screenshot of the execution phases

image
image1258×255 19.5 KB

I am running version 6.0.

2

The query updating 100K documents. CE version has limited cores. Update requires to Fetch whole document and update it.

Not sure about what you are trying to do. SELECT has USE KEYS i.e means you are updating same document with id value of the document? If that is the case why do you need SELECT? result of subquery is also ARRAY. Do u want it as array.

3

@vsr1
What I am trying to achieve to assign a parentDocumentId of some of the metadata documents to an ID of another metadata document matching criteria. This is to create a parent-child relationship. An example is this data set

Document ID = doc_1

{
  "locale": "US",
  "language": "en",
  "type" = "testMetadataType",
  "hasGeneratedChildren" = true,
  "parentId" = "parent_1"
}

Document ID = doc_2

{
  "locale": "US",
  "language": "en",
  "type" = "testMetadataType",
  "parentId" = "parent_1"
}

Document ID = doc_3

{
  "locale": "fr",
  "language": "fr",
  "type" = "testMetadataType",
  "parentId" = "parent_1"
}

I would like to see the query changes the document with Document ID doc_2 to have parentDocumentId = doc_1 so something like this

Document ID = doc_2

{
  "locale": "US",
  "language": "en",
  "type" = "testMetadataType",
  "parentId" = "parent_1",
  "parentDocumentId" = "doc_1"
}

Whereas the document with Document ID doc_1 and doc_2 remain unchanged. I use USE KEYS because the query failed to run without it. I just Google and applied the fix without understanding what it does. It turned out that I am wrong. I don’t know how to make the query work. Is it possible that you can show me how.

Thanks

4

You should use MERGE described Indexing for performance

CREATE INDEX ix1 ON metadata(parentId, locale, language, hasGeneratedChildren)
WHERE type = "testMetadataType" ;

PRE 6.50

    MERGE metadata AS m USING (SELECT s1.parentId, META(s2).id, META(s1).id AS pid
                               FROM metadata s1
                               JOIN FROM metadata s2
                               ON s1.locale = s2.locale AND s1.language = s2.language AND s1.parentId = s2.parentId
                               WHERE s1.type = "testMetadataType"
                                     AND s2.type = "testMetadataType"
                                     AND s1.hasGeneratedChildren = true ) AS s
    ON KEY s.id
    WHEN MATCHED THEN UPDATE m.parentDocumentId = s.pid;

6.50 https://blog.couchbase.com/ansi-join-enhancements-and-ansi-merge/

MERGE metadata AS m USING (SELECT s1.parentId, s1.locale, s1.language, META(s1).id
                           FROM metadata s1
                           WHERE s1.type = "testMetadataType"
                                 AND s1.hasGeneratedChildren = true ) AS s
ON m.locale = s.locale AND m.language = s.language AND m.parentId = s.parentId AND m.type = "testMetadataType"
WHEN MATCHED THEN UPDATE m.parentDocumentId = s.id;
1 Like
5

I fixed up the query a bit using your suggestions. It worked now. Thanks