Sorting data using order by taking higher time - CB Version 7.0.5

CB Team,
Sorting the data by adding ORDER BY clause DESC taking much higher time than without, any suggestion.

with order by

SELECT t.orderNumber, t.accountInfo.accAttNeeded attn
FROM D981_ACCOUNT AS t
WHERE t.accountNumber=‘TXE85X286’
ORDER BY t.accountInfo.accAttNeeded DESC - execution time 567 ms

without order by

SELECT t.orderNumber, t.accountInfo.accAttNeeded attn
FROM D981_ACCOUNT AS t
WHERE t.accountNumber=‘TXE85X286’ - execution time 300 ms

INDEX IDX_ACCOUNT_11302023 ON D981_ACCOUNT
(accountNumber,accountCreatedtDate,(accountInfo.lstOrderDt),(accountInfo.accAttNeeded)
PARTITION BY hash(accountNumber)

Sorting does indeed take time. I’m not sure what else to say.

does any query tuning or change sequence of indexing field would help ?
or else order by before indexing the document …? any thoughts …

If the order-by property is not indexed, then the data has to be fetched for it to be sorted.

If the order-by property is indexed, then the data does not have to be fetched for it to be sorted. The data won’t need to be fetched to project accAttNeeded either.

so, what you suggesting is , we need to add "order by on accAttneeded towards the index creation …?
the general idea behind to add the order by is to fetched accAttneeded order number should be first and then rest of the order number.
ex - if account 5 orders and 3 orders are in pending and 2 order in completed then, 3 order number should be shown first in the response.

something like this: where creationDate DESC

CREATE INDEX idx_user ON default( someId, status, active, creationDate DESC) WHERE type=“User”;

I’m suggesting to create an index on accAttneeded.

CREATE INDEX some_index on default(accAttneeded)

Since accAttneeded is a flag (right?), you just want the orders with it first, followed by all the other orders (right?) you can just take the UNION of the documents with accAttneeded and without accAttneeded. There’s no sorting. Just two queries.

SELECT t.orderNumber, t.accountInfo.accAttNeeded attn
FROM D981_ACCOUNT AS t
WHERE t.accountNumber=‘TXE85X286’ and EXISTS accAttneeded
UNION
SELECT t.orderNumber, t.accountInfo.accAttNeeded attn
FROM D981_ACCOUNT AS t
WHERE t.accountNumber=‘TXE85X286’ and NOT EXISTS accAttneeded

accAttneeded is value “01” / “02”/ “03”.
yes , i want them to show first in my response.

I have tried with EXIST AND NOT EXISTS and unfortunately it’s not returning any data set , where “IS MISSING” and " IS NOT MISSING" fetching the value .

doc example

{
“accountNumber”: “TXE85X286”
“accountInfo”: {
“lstOrderDt”: “2023-10-26”,
“lstOrderDELDt”: {
“strtTm”: “01.01.01”,
},
“accAttNeeded”: “01”,
}
}

DOC 2

{
“accountNumber”: “TXE85X286”
“accountInfo”: {
“lstOrderDt”: “2023-10-26”,
“lstOrderDELDt”: {
“strtTm”: “01.01.01”
},
}
}

My mistake - it’s NOT ISVALUED(…) instead of EXISTS.

And another mistake - without specifying ORDER-BY the results are not order (not even by the order of the SELECTs). So you’ll need to go back to your original query, and hope that adding an index helsp.

i have tried with union and order by clause and query execution time is around same as simply adding an order by clause in my previous query ( execution time 300 ms) , so now i am thinking if can indexed document by adding DESC

Example :

INDEX IDX_ACCOUNT_11302023 ON D981_ACCOUNT
(accountNumber ,accountCreatedtDate ,(accountInfo .lstOrderDt ),(accountInfo .accAttNeeded ) DESC
PARTITION BY hash(accountNumber )

just for your FYI … updating the index with DESC solved the problem and execution time is also good now.

SELECT t.orderNumber,
       t.accountInfo.accAttNeeded AS attn
FROM D981_ACCOUNT AS t
WHERE t.accountNumber = "TXE85X286"
ORDER BY t.accountInfo.accAttNeeded DESC;

CREATE INDEX ix1 ON D981_ACCOUNT( accountNumber, accountInfo.accAttNeeded DESC, orderNumber);

Query has ORDER BY it needs to sort the results and takes more time than WITHOUT ORDER BY except when query order matches the index order like below ( accountInfo.accAttNeeded DESC and index has second key , first key accountNumber not in ORDER BY, but it is single equality predicate so it can use index order) .

check out Appendix