I am having some serious performance issues on my initial data query. When the user loads the data for the grid there is no filters applied if he didn’t save any previously which causes the data to load slowly. Even so i use a limit of 100 records to return it takes almost 3 sec for the first 100 to show up. Hope there is a way to get this sub 1 Sec…
This is what my query looks like
WITH tracknames AS (
SELECT DISTINCT track_id,
t.name
FROM Contacts AS t
UNNEST t.tract_id AS track_id
WHERE t._type ="tract_info")
SELECT DISTINCT
meta(f).id as DocId,
SUBSTR(f.Record.apn,0,3) || "-" || SUBSTR(f.Record.apn,3,3) || "-" || SUBSTR(f.Record.apn,6,2) as apn,
f.Record.Owners.owner1FName || IFMISSINGORNULL((CASE WHEN (LENGTH(f.Record.Owners.owner1SpouseFName) > 0) THEN " & " || f.Record.Owners.owner1SpouseFName || " " ELSE " " END), " ") || f.Owners.owner1LName as owner_1,
TRIM(f.Record.Owners.owner2FName || IFMISSINGORNULL((CASE WHEN (LENGTH(f.Record.Owners.owner2SpouseFName) > 0) THEN " & " || f.Record.Owners.owner1SpouseFName || " "
ELSE " " END), " ") || f.Record.Owners.owner2LName) as owner2,
f.Record.Marketing.privacy,
ARRAY { CASE WHEN v.type IS NOT MISSING THEN lower(v.type)
ELSE "default"
END:
CASE WHEN LENGTH(v. \`number\`) = 10 THEN "(" || SUBSTR(v.\`number\` , 0,3) || ") " || SUBSTR(v.\`number\` , 3,3) || "-" || SUBSTR(v.\`number\` , 6,4)
else
v.\`number\` END } FOR v IN f.Record.phones END AS phones,
ARRAY {CASE WHEN v.type IS NOT MISSING THEN lower(v.type) ELSE "default" END :v.\`address\`} FOR v IN f.Record.emails END AS emails,
f.Marketing.farm_id,
f.Record.PropertyAddress.houseNumber,
f.Record.PropertyAddress.streetName || IFMISSINGORNULL(" " || f.Record.PropertyAddress.streetType, "") || IFMISSINGORNULL(" " || f.Record.PropertyAddress.streetSuffix, "") AS streetName,
f.Record.PropertyAddress.city,
f.Record.PropertyAddress.state,
f.Record.beds,
f.Record.baths,
f.Record.partialBaths,
f.Record.\`pool\`,
f.Record.fireplace,
f.Record.ownerOccupied,
f.Record.tract,
f.Record.propertyType,
f.Record.History.updated_flag,
FIRST v.name FOR v IN tracknames WHEN v.track_id = f.Record.tract END AS tract_name,
f.Record.mailingAddress.address || " " || f.Record.mailingAddress.city || " " ||
f.Record.mailingAddress.state || " " || f.Record.mailingAddress.zip ||"-" || f.Record.mailingAddress.zip4 AS mailing_address
FROM Contacts AS f
WHERE f._type="farm"
AND LOWER(f.Record.PropertyAddress.streetName) IS NOT MISSING
AND TONUMBER(f.Record.PropertyAddress.houseNumber) IS NOT MISSING
Limit 100 Offset 0
Below is my Plan Text
{
> "#operator": "With",
> "bindings": [
> {
> "expr": "(select distinct `track_id`, (`t`.`name`) from `Contacts` as `t` unnest (`t`.`tract_id`) as `track_id` where ((`t`.`_type`) = \"tract_info\"))",
> "static": true,
> "var": "tracknames"
> }
> ],
> "~child": {
> "#operator": "Sequence",
> "~children": [
> {
> "#operator": "IndexScan3",
> "as": "f",
> "index": "adv_to_number_Record_PropertyAddress_houseNumber_lower_Record_PropertyAddress_streetName_type",
> "index_id": "5f3b91c470e5ebbd",
> "index_projection": {
> "primary_key": true
> },
> "keyspace": "Contacts",
> "namespace": "default",
> "spans": [
> {
> "exact": true,
> "range": [
> {
> "inclusion": 1,
> "low": "null"
> },
> {
> "inclusion": 1,
> "low": "null"
> }
> ]
> }
> ],
> "using": "gsi"
> },
> {
> "#operator": "Fetch",
> "as": "f",
> "keyspace": "Contacts",
> "namespace": "default"
> },
> {
> "#operator": "Parallel",
> "~child": {
> "#operator": "Sequence",
> "~children": [
> {
> "#operator": "Filter",
> "condition": "((((`f`.`_type`) = \"farm\") and (lower((((`f`.`Record`).`PropertyAddress`).`streetName`)) is not missing)) and (to_number((((`f`.`Record`).`PropertyAddress`).`houseNumber`)) is not missing))"
> },
> {
> "#operator": "InitialProject",
> "distinct": true,
> "result_terms": [
> {
> "as": "DocId",
> "expr": "(meta(`f`).`id`)"
> },
> {
> "as": "apn",
> "expr": "(substr0(((`f`.`Record`).`apn`), 0, 3) || \"-\" || substr0(((`f`.`Record`).`apn`), 3, 3) || \"-\" || substr0(((`f`.`Record`).`apn`), 6, 2))"
> },
> {
> "as": "owner_1",
> "expr": "((((`f`.`Record`).`Owners`).`owner1FName`) || ifmissingornull(case when (0 < length((((`f`.`Record`).`Owners`).`owner1SpouseFName`))) then (\" & \" || (((`f`.`Record`).`Owners`).`owner1SpouseFName`) || \" \") else \" \" end, \" \") || ((`f`.`Owners`).`owner1LName`))"
> },
> {
> "as": "owner2",
> "expr": "trim(((((`f`.`Record`).`Owners`).`owner2FName`) || ifmissingornull(case when (0 < length((((`f`.`Record`).`Owners`).`owner2SpouseFName`))) then (\" & \" || (((`f`.`Record`).`Owners`).`owner1SpouseFName`) || \" \") else \" \" end, \" \") || (((`f`.`Record`).`Owners`).`owner2LName`)))"
> },
> {
> "expr": "(((`f`.`Record`).`Marketing`).`privacy`)"
> },
> {
> "as": "phones",
> "expr": "array {case when ((`v`.`type`) is not missing) then lower((`v`.`type`)) else \"default\" end: case when (length((`v`.`NUMBER`)) = 10) then (\"(\" || substr0((`v`.`NUMBER`), 0, 3) || \") \" || substr0((`v`.`NUMBER`), 3, 3) || \"-\" || substr0((`v`.`NUMBER`), 6, 4)) else (`v`.`NUMBER`) end} for `v` in ((`f`.`Record`).`phones`) end"
> },
> {
> "as": "emails",
> "expr": "array {case when ((`v`.`type`) is not missing) then lower((`v`.`type`)) else \"default\" end: (`v`.`address`)} for `v` in ((`f`.`Record`).`emails`) end"
> },
> {
> "expr": "((`f`.`Marketing`).`farm_id`)"
> },
> {
> "expr": "(((`f`.`Record`).`PropertyAddress`).`houseNumber`)"
> },
> {
> "as": "streetName",
> "expr": "((((`f`.`Record`).`PropertyAddress`).`streetName`) || ifmissingornull((\" \" || (((`f`.`Record`).`PropertyAddress`).`streetType`)), \"\") || ifmissingornull((\" \" || (((`f`.`Record`).`PropertyAddress`).`streetSuffix`)), \"\"))"
> },
> {
> "expr": "(((`f`.`Record`).`PropertyAddress`).`city`)"
> },
> {
> "expr": "(((`f`.`Record`).`PropertyAddress`).`state`)"
> },
> {
> "expr": "((`f`.`Record`).`beds`)"
> },
> {
> "expr": "((`f`.`Record`).`baths`)"
> },
> {
> "expr": "((`f`.`Record`).`partialBaths`)"
> },
> {
> "expr": "((`f`.`Record`).`fireplace`)"
> },
> {
> "expr": "((`f`.`Record`).`ownerOccupied`)"
> },
> {
> "expr": "((`f`.`Record`).`tract`)"
> },
> {
> "expr": "((`f`.`Record`).`propertyType`)"
> },
> {
> "expr": "(((`f`.`Record`).`History`).`updated_flag`)"
> },
> {
> "as": "tract_name",
> "expr": "first (`v`.`name`) for `v` in `tracknames` when ((`v`.`track_id`) = ((`f`.`Record`).`tract`)) end"
> },
> {
> "as": "mailing_address",
> "expr": "((((`f`.`Record`).`mailingAddress`).`address`) || \" \" || (((`f`.`Record`).`mailingAddress`).`city`) || \" \" || (((`f`.`Record`).`mailingAddress`).`state`) || \" \" || (((`f`.`Record`).`mailingAddress`).`zip`) || \"-\" || (((`f`.`Record`).`mailingAddress`).`zip4`))"
> }
> ]
> },
> {
> "#operator": "Distinct"
> },
> {
> "#operator": "FinalProject"
> }
> ]
> }
> },
> {
> "#operator": "Distinct"
> }
> ]
> }
> }