Improve query performance

SQL++
,
1

Hi,

I am writing a query to join cust doc and order doc to fetch cust docs if cust has orders. the searching filter is cust name. sample docs and indexes are:
cust doc:
[
“_cTime”: 1608279352,
“_mTime”: null,
“_tID”: “11111”,
“_type”: “custRec”,
“names”: [
{
“family”: “ftest”,
“given”: “gest”,
“prefix”: null,
“suffix”: null
}
]
]
create idex idx_cust_Fname ON t (distinct array lower(pName.family) for pName in names end) WHERE _type = ‘custRec’ ;

order doc:
[
“_cTime”: 1589873387,
“_dateTime”: 163365407,
“_inactiveTime”: 0,
“_mTime”: 1589873387,
“_tID”: “11111”,
“_type”: “Order”,
“custDocIDRef”: “Cust@@23456@@11111”
]
create idex idx_order_cust ON t (custDocIDRef) WHERE _type = ‘Order’;

query:
with cust AS (
SELECT meta().id as custID, * FROM t WHERE _type=‘custRec’ AND _tID=‘11111’
AND (ANY pName in names SATISFIES LOWER(pName.family) LIKE ‘fte%’ END))
select distinct cust.* from cust
inner join t t1
on t1.custRecDocIDRef = cust.custID and t1._type=‘Order’ AND t1._tID=‘11111’
ORDER BY cust.names[0].family, cust.names[0].given LIMIT 500;

The query is about 6 seconds. How to improve this query?

Thanks,

Matthew

2 
CREATE INDEX ix1 ON t(_tID, custRecDocIDRef ) WHERE _type="Order";
CREATE INDEX ix2 ON t(_tID, custID, DISTINCT ARRAY LOWER(pName.family) FOR pName IN names END) WHERE _type="custRec";
WITH custRecDocIDs AS (SELECT DISTINCT RAW o.custRecDocIDRef
                       FROM t AS o
                       WHERE o._type="Order" AND o._tID="11111")
SELECT c.*, META(c).id
FROM t AS c
WHERE c._type="custRec" AND c._tID="11111" AND c.custID IN custRecDocIDs
ORDER BY c.names[0].family, c.names[0].given
LIMIT 500;

OR

Also try other variations (JOIN t AS o USE HASH(BUILD), JOIN t AS o USE HASH (PROBE))

CREATE INDEX ix2 ON t(_tID, DISTINCT ARRAY LOWER(pName.family) FOR pName IN names END) WHERE _type="custRec";

SELECT DISTINCT c.*, META(c).id
FROM t AS c
JOIN t AS o ON o.custRecDocIDRef = c.custID AND o._type="Order" AND o._tID="11111"
WHERE c._type="custRec" AND c._tID="11111"
ORDER BY c.names[0].family, c.names[0].given
LIMIT 500;
3

Thanks for your quick response.

another query question:
sample doc:
{
“_cTime”: 1600160909,
“_tID”: “11111”,
“_type”: “PatRec”,
“identifiers”: [
{
“system”: “123abc”,
“type”: “MRN”,
“use”: 3001,
“value”: “9763mrn1”
}
]
}

I have 2 indexes:

Create INDEX idx_pat1 ON bucket1 (distinct array [lower(pIdentifier.value), pIdentifier.system] for pIdentifier in identifiers end,_tID) WHERE _type = “PatRec”;

Create INDEX idx_Pat2 ON bucket1 (distinct array lower(pIdentifier.value) for pIdentifier in identifiers end,_tID) WHERE _type = “PatRec”;

Query1: this query return 0 documents using index idx_pat1.
SELECT t.* FROM bucket1 t
WHERE t._type=“PatRec”
AND t._tID=“11111”
AND ANY pIdentifier IN identifiers SATISFIES [lower(pIdentifier.value), pIdentifier.system] in [“9763mrn1”, ‘123abc’] END;

Query2: this query return the sample doc above using idx_pat2
SELECT t.* FROM bucket1 t
WHERE t._type=“PatRec”
AND t._tID=“11111”
AND ANY pIdentifier IN identifiers SATISFIES lower(pIdentifier.value) = “9763mrn1” END;

How does query1 return 0 doc? How can I update query1 to return the sample doc above?

Thanks,
Matthew

4

IN takes an array of values; you’re matching a value that is itself an array, so you need:

in [["9763mrn1", "123abc"]]

HTH.

1 Like