Query using java SDK taking more than 6 mins , but same query is taking 12s in Query workbench

SQL++
, , , ,
1

Hi Team

We have a query as below:

SELECT IFMISSINGORNULL(SUM(ARRAY_COUNT(ARRAY 1 FOR m IN migrations WHEN m.status = “SUCCESS” END)),0)
FROM promotions AS commandStatus WHERE commandStatus._class = “XXX.YYYY.ZZZZ”
AND commandStatus.eventType = “CL” AND commandStatus.requestDuplicated = false
AND commandStatus.createdDateTime BETWEEN 1587137280000 AND 1587160200000

The index that is being picked up is as below:

CREATE INDEX legacy_createdDateTime_eventType_extId_array_migrations_success_class_requestDuplicated_false ON
promotions(createdDateTime,eventType, extId, array_count(array 1 for m in (migrations) when ((m.status) = ‘SUCCESS’) end))
PARTITION BY HASH(extId)
WHERE _class = ‘XXX.YYYY.ZZZZ’
AND requestDuplicated = false
using GSI WITH {“num_replica”: 1,“num_partition”:6} ;

Can you guys help me understand why using java SDK the query is timing out after running for 6 mins but in workbench it is executing fine with in 12s?

Couchbase Server = 6.0.2
Couchbase java Client = 2.7.9

Regards,
Venkat

2

It is hard to tell why JAVA SDK is taking long.
It might be using different index on prepared plan.
Can you provide USE INDEX hint inside your java code see if that helps.

The following index will be better (small correction)

CREATE INDEX ix1 ON promotions(eventType, createdDateTime,
                    extId, array_count(array 1 for m in (migrations) when ((m.status) = "SUCCESS") end))
PARTITION BY HASH(extId)
WHERE _class = "XXX.YYYY.ZZZZ" AND requestDuplicated = false
using GSI WITH {"num_replica": 1,"num_partition":6} ;
3

Thanks for the swift response.

Change of indexed has resulted in better performance and reduce the execution time to 8s from 12s.

However, usage of USE INDEX in the code has not improved the result and still taking more than 12 mins.

Regards,
Venkat

4

Unless you get EXPLAIN plan from SDK it is hard to tell. try adhoc=true